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
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...
- 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
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
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
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Koloa
POIPU SANDS 222 AT KOLOA KAI VACATION RENTALS
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 ...
- 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
POIPU SANDS 312 AT KOLOA KAI VACATION RENTALS
Poipu Sands 312 is a stunning vacation home that embodies the essence of a perfect getaway. This spacious and charming 1-bedroom, 1.5-bathroom home boasts a newly renovated fully equipped ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
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
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
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
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 214 AT KOLOA KAI VACATION RENTALS
Discover rest, rejuvenation, and relaxation at this beautifully designed ground-floor condo located on the scenic Poipu Kai Greenbelt, just 100 yards from the breathtaking Shipwreck ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
POIPU SANDS 116 AT KOLOA KAI VACATION RENTALS
Nestled on the ground floor with stunning ocean views, Poipu Sands 116 offers 1,353 square feet of comfort and tropical charm, making it the perfect retreat for your Kauai vacation. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
