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, rewritten_description,
(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, 50
You're invited to experience the 'ALOHA' spirit in this completely renovated, nearly 1000 sq. ft., one bedroom, one and a half bath luxury Poipu Sands Condo. Situated in Building 4 of the complex, our unit is among the closest to Grand Hyatt
- 1 bedroom
- 2 bathroom
- Sleeps 2
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
POIPU SANDS 513 AT KOLOA KAI VACATION RENTALS
Oceanfront Tranquility on Kauai’s Sunny South Shore
Discover a slice of paradise at Poipu Sands 513, a ground-floor oceanfront retreat located in the heart of Poipu. With unobstructed
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
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 are two
- 3 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
POIPU SANDS 214 AT KOLOA KAI VACATION RENTALS
A Tranquil Island Retreat on Poipu Kai Greenbelt
Welcome to Poipu Sands 214, a beautifully appointed ground-floor condo just steps away from the lush Poipu Kai Greenbelt and the
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU SANDS 116 AT KOLOA KAI VACATION RENTALS
A Tropical Retreat with Stunning Ocean Views
Welcome to Poipu Sands 116, a spacious 1,353-square-foot ground-floor condo nestled on the sunny south shore of Kauai. Featuring
- 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
POIPU SANDS 312 AT KOLOA KAI VACATION RENTALS
Ocean-View Getaway in Sunny Poipu
Welcome to Poipu Sands 312, your serene island retreat located on Kauai’s picturesque south shore. With its recent renovations, vibrant décor, and
- 2 bedroom
- 2 bathroom
- Sleeps 4
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
- 2 bedroom
- 2 bathroom
- Sleeps 3
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
POIPU SANDS 222 AT KOLOA KAI VACATION RENTALS
Your Two-Story Ocean-View Retreat in Poipu
Welcome to Poipu Sands 222, a beautifully designed two-story condo situated on the lush Poipu Kai Greenbelt. With spacious interiors,
- 2 bedroom
- 2 bathroom
- Sleeps 4
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,
- 3 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
POIPU SANDS 221 AT KOLOA KAI VACATION RENTALS
Your Island Oasis with Stunning Ocean Views
Welcome to Poipu Sands 221, a second-floor condo offering over 1,371 square feet of tropical comfort and breathtaking ocean views. With its
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
