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, 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
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
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
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
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
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
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
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
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
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
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
Enjoy your vacation under beautiful azure skies near the spectacular multi-colored waters of the Pacific. Just steps from well-known south shore surf spots, this 1353 square-foot condo is located just west of Shipwreck Beach. A modern, upgraded ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Come enjoy the relaxed tropical setting of this ocean-view two-bedroom, two-bath condo. Located in sunny Poipu, you will be minutes away from some of the best beaches Kauai has to offer. The 1700-square-foot condo features a remodeled kitchen ...
- 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
Luxuriously remodeled and fronting the captivating Keoneloa Bay, Poipu Sands 522 is the embodiment of a luxuriously relaxing holiday. Every part of this beautiful island home reflects the warm personal touches of its owners. The redesigned ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Holiday in the captivating Poipu Sands 124, a 2 bedroom, 2 bath home with an enthralling view of the majestic Haupu mountains and sparkling waters of Keoneloa Bay also known as Shipwreck Beach. Relish watching whales frolic in the bay from ...
- 2 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
You & your guests will love this location! Watch the sunrise from the Lanai & listen to the ocean waves. Walking distance to Shipwreck Beach, and Grand Hyatt Resort. Access the green belt walking paths that will take you all the way to Poipu and...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Poipu Kai Poipu Sands 216 - Two-bedroom Garden View condo on the island of Kaua'i.
Primary bedroom: King Bed
Primary bathroom: full bath ensuite with shower/tub combo
Bedroom 2: queen bed
Second bathroom: full bath with single ...
- 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
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
The main living area is an open concept with the living room, dining room, and kitchen (newly remodeled). This area looks out onto the main lanai and beautiful garden area.
Our condo has 2 spacious bedroom suites. The master suite is upstairs...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
