select * from (Select a.`postalCode`,a.`uniturl`, `region`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `source`, `property_type`, `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 and b.blocked=0 ) 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, 30
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
THE BEST DESCRIPTIONS OF OUR CONDO COME FROM OUR GUESTS - PLEASE READ THEIR REVIEWS.
With spectacular views, our condo is superbly located within the elegant, serene and spacious Poipu Sands, which enjoys the distinction as Poipu's ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
Aloha from paradise! Take in the breathtaking ocean views 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 many ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> 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! This is one of the most updated units in the complex.
ABOUT THE CONDO:
-2 floors of spacious family ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
POIPU SANDS 513 AT KOLOA KAI VACATION RENTALS
Discover the unique kind of peace and tranquility you can only get from oceanfront living. Backing to the lush Poipu Greenbelt, this well-appointed ground-floor condo offers unobstructed ocean...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
New Owners! Welcome to Poipu Sands 437! This top floor ocean view condo is spacious, updated and comfortable, with a 300+ sq ft lanai overlooking the cliffs of Shipwreck Beach, perfect for turtle, dolphin and whale watching (in season). There ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Experience Paradise Living: Your Dream Hawaiian Getaway Awaits!
🌴 Welcome to your own slice of Hawaiian paradise at Poipu Sands! 🌴
Nestled on the stunning shores of Kauai's Poipu Beach, this exquisite three-bedroom, ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Enjoy beautiful ocean views from your 2nd and 3rd floor lanais totalling over 354 sq ft. This spacious and well appointed 1371 sq ft. condo has it all! Recently updated with carpet, paint, granite counter tops, smart TVs, and 2 updated ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
Savor the crystal blue and turquoise ocean accented by black lava rocks, white sands, and glorious sunrises; all from your private lanai. Enjoy the tropical ambiance and relax in the island comforts offered by this amazing two-bedroom ocean-view...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Amazingly beautiful is the term used by many to describe this wonderful Poipu Sands condo. Warmly toned furnishings and stately canopy beds, paired with brilliant tropical touches, bring this condo to a level of quality rarely seen. Featuring ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
Immerse in the lavish comforts of Poipu Sands 321, a remodeled two-bedroom, two-bath townhouse-style home only steps from the white sands and azure waters of Shipwreck Beach. The warm earthy tones touching every surface from wood furnishings, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
You’ll know your vacation has started the moment you walk through the door of this upscale, renovated, 1000 square foot condo! This tastefully decorated condo is furnished with relaxing Earth tones accented with vibrant pops of color. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
This 1353 square foot, ground floor ocean view condo features a private lanai where you can sit back and enjoy ocean breezes while taking in the beautiful island views. Wonderfully decorated, yet comfortable and cozy, Poipu Sands 116 provides ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Beautiful Ground Floor Poipu Sands 1 Bedroom 1.5 Bath condo with AC in Master. Perfectly located near the heated tropical pool, BBQ area, and tennis court, and is just a short stroll to swimming beaches. Your neighbor is the Grand Hyatt and you ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Beautifully appointed 1 bedroom, 1.5 bath ocean view condo offers the Kauai experience you've been looking for! Highly sought after ground floor unit provides abundant open space and direct access to the barbecue grill and pool. Plenty of free ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
