select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `address`,round(price) as price,minimum_nights, `rating`, `review_count`,`latitude`, `longitude`,case when propertyName='' then trim(address) else CONCAT(CONCAT(propertyName,'(',trim(address)),'',')') end as addre,propertyName,
(6371 *
acos(
cos( radians( 21.2759167 ) ) *
cos( radians( `latitude` ) ) *
cos(
radians( `longitude` ) - radians( -157.8233677 )
) +
sin(radians(21.2759167)) *
sin(radians(`latitude`))
)
) as distance from `vacationrentalindex` a inner join vacationrentalindexfeed b on a.uniturl=b.unitUrl and a.headline!='' and a.thumbnailUrl!='' and b.enabled=1) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%poipu kai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Sugar Mill Cottage is located in the heart of the Poipu Kai Resort community. It is the perfect location for beach access to either Shipwrecks beach or Poipu Beach park. A short stroll in either direction will take you to sun and sand!
Enjoy ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
The Villas at Poipu Kai are one of the newer luxury condominium resorts on the South side of Kauai in Poipu. These spacious and upscale condominiums are part of the Poipu Kai Resort that offers access to additional recreational amenities to all ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Our luxurious Kauai vacation rentals at Poipu Sands offer spacious layouts, large covered lanais, a private heated resort swimming pool, community gas BBQs, on-site tennis courts, and more.
Centrally located in Poipu, adjacent to the Grand ...
1 bedroom
2 bathroom
Sleeps 4
HI> Koloa
Situated in the premier Poipu Kai Resort on Kauai's south shore is Kahala at Poipu Kai. Steps from a scenic greenbelt walking path Kahala 621, surrounded by tropical landscaping within minutes from popular Brennecke's and Poipu Beach, as well as...
1 bedroom
1 bathroom
Sleeps 2
HI> Koloa
Brand new flooring, bathroom, and kitchen remodeled in 2018. The rattan furniture and tropical artwork in this luxurious condo showcase Kauai’s true laid-back island style. The private lanai just out of the living room is perfect for a ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
Featuring some of the newest and finest Kauai condos on island. Each fully air conditioned Regency villa has luxurious designer furniture and interiors as well as all the needed modern comforts.
PARRISH PROFESSIONAL CLEAN – Built Upon the ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
This condo is a spacious 1,400 square foot air conditioned condo with oversized windows and two lanais to help guests enjoy the surrounding beauty of the resort. Master and guest bedrooms with respective bathrooms provide privacy, while plenty ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
The Villas at Poipu Kai is the newest and most luxurious development within Poipu Kai Resort, located on the sunny south shore of Kauai! Unit C110 is a 3 bedroom 3 bathroom ground level luxury villa, boasting over 1,600 square feet of living ...
3 bedroom
3 bathroom
Sleeps 8
HI> Koloa
Tucked into the Lanai Villas section of the tropical Poipu Kai Resort, Maikalani is a Kauai vacation rental home exquisitely decorated and outfitted with all the necessities of home, including zoned air conditioning. Located on the lush green ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
Our luxurious Kauai vacation rentals at Poipu Sands offer spacious layouts, large covered lanais, a private heated resort swimming pool, community gas BBQs, on-site tennis courts, and more.
Centrally located in Poipu, adjacent to the Grand ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
This one-bedroom, one bath condo is located on the top floor of the Kahala complex, one of Poipu Kai’s most desirable rental condos. With a partial ocean view and close proximity to the complex pools, tennis courts, and pathway straight to ...
1 bedroom
1 bathroom
Sleeps 4
HI> Koloa
A spacious 1,400 square foot fully air conditioned condo with oversized windows and two lanais help guests enjoy the surrounding beauty of the resort. Master and guest bedrooms with respective bathrooms provide privacy, while plenty of closet ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
Our luxurious Kauai vacation rentals at Poipu Sands offer spacious layouts, large covered lanais, a private heated resort swimming pool, community gas BBQs, on-site tennis courts, and more.
Centrally located in Poipu, adjacent to the Grand ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
Hale Maluhia Maikai is a luxurious greenbelt property in Regency Villas at Poipu Kai with air conditioning on the sunny south shore of Kauai has designer furnishings and tasteful decor. Spacious and bright with two stories and an open loft with ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Aloha! Come bring your ohana (family) and friends to sit on our beautiful wrap around patio at our beautiful Pu'uhonua (place of refuge) with breathtaking tropical views. The great room and kitchen are the heart of Pu'uhonua located on the 2nd...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
