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 sands%') AND sleeps>=1 order by rating desc LIMIT 0, 15
POIPU SANDS 412
Experience the true tropical getaway with Poipu Sands #412. A short walk to two great beaches - Shipwrecks Beach and Poipu Beach. Watch the sunrise from the knoll just outside the lanai and start your day with a view of ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Koloa
We are the new owners of Poipu Sands #225 and have completed a full renovation in June 2021 to make this space even more fabulous!
ABOUT THE CONDO:
-2 floors of spacious family accommodations (over 1300 sq ft).
-Entry on the 2nd floor with ...
- 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
This beautiful ground floor, garden view unit features a private lanai just steps to Shipwreck's beach and hiking. Enjoy king beds in each bedroom along with a/c to keep you cool! The master suite includes a sliding glass door so you can walk ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Aloha from paradise! Take in the breathtaking sunrise over the ocean while having breakfast on the large lanai (balcony), or listen to waves crash as you drift off to sleep! You'll enjoy a spacious 1353 sq. ft, full kitchen, A/C in bedrooms, and...
- 2 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 3
HI> Koloa
Poipu Sands 411 is a beautifully furnished, and elegant condo on the sunny South Shore, where you can experience the magic of soft tropical breezes, peaceful sounds of the surf and views of the ocean while relaxing on your own lanai. The only 1 ...
- 1 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 stunning three-bedroom, three-bath island-style split level condo with ocean and garden views, is only 100 yards from spectacular Shipwreck Beach (Keoneloa Bay). This 1700 square feet condo features two master suites along with a large open...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Koloa
Wake to the sounds of birds and take a walk along some of Kauai’s finest beaches. This two-bedroom, two-bath vacation condo is perfectly located near one of the best surfing and boogie-boarding beaches on the south shore, Shipwreck Beach. Rest...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Poipu Sands #427 is a luxury 2 bedroom condo with 2 large master suites, each with king beds. Both have attached full bathrooms adjoining and the entire unit was remodeled in the summer of 2009 with new carpet and furniture installed in January ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
POIPU SANDS 513
*UNDER NEW MANAGEMENT*
(Please excuse the lack of reviews - we are currently helping our guests become acquainted with the property and the new reviews will follow shortly. Mahalo!)
Discover the unique kind of peace and ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Poipu Sands 222
Enjoy a phenomenal vacation in this two-story ocean-view retreat, just a quick walk to Shipwreck Beach on the lush Poipu Kai Greenbelt.
Comfortable furnishings and tasteful tropical décor fill the spacious, breeze-cooled ...
- 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
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 6
HI> Koloa
