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, 20
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
COMPLEX IS BEING REROOFED FROM AUGUST 19, 2024 TO APRIL 28 FROM 730 AM TO 6 PM (MONDAY THROUGH SATURDAY). THERE IS A 25% APPLIED TO COMPENSATE FOR THE NOISE. NO ADDITIONAL DISCOUNTS WILL BE ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
** OCTOBER CONSTRUCTION DISCOUNT ALREADY APPLIED ** PLEASE CONTACT US FOR DETAILS.
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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
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
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
Poipu Sands #427 is a luxury 2 bedroom condo with 2 large master suites, each with king beds, ensuite bathrooms and air conditioning for your enjoyment. This is a very lovely property, with beautiful ocean views from the lanai and the interior ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
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
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
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
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
Stunning 2BR Condo with Ocean Views
Immerse yourself in the beauty of Kauai's south shore at this stunning Poipu Sands condominium. Wake up to breathtaking ocean views from the expansive lanais spanning the second and third floors, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
Experience your ultimate tropical paradise with all the comforts of home and more. From your perch on the top floor, you will have an amazing view of the turquoise blue ocean, and if you bring your binoculars, you will be up close to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Awake to a new day as the sunrise comes to this spacious 1353-square-foot vacation home. The stunning panorama will captivate; watchful mountains, white water at Shipwreck Beach, a beckoning pool (heated!), and a lush landscape. With a warm and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Life is a Beach at this beautiful south shore condo, located on the ground floor just a short walk from the Shipwreck beach. With newly upgraded appliances, bed and mattress, master bath vanity and cabinets, paint, Wi-Fi, cable, and Flat Screen ...
- 1 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Koloa
Experience the ultimate getaway at Poipu Sands 233, a captivating 2-bedroom, 2-bathroom vacation rental nestled in the heart of Poipu. Gazing out from the living room, dining room, kitchen, and master bedroom, a breathtaking ocean view awaits, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
