select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%poipu shores%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Welcome to this amazing 3rd floor condominium with two bedrooms and two bathrooms. You’ll love the modern look of this unit, featuring granite kitchen and bathroom countertops, large ceramic floors, large wicker furniture, and stone showers. ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
This two-bedroom two-bathroom condo is ultra spacious with modern flare. A large kitchen boasts black countertops, a full-sized refrigerator, and a stainless steel sink. The kitchen opens up into a bright dining and living room with comfortable ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
Poipu Shores is an intimate and private oceanfront resort with only 39 townhomes located in sunny Poipu Beach. This resort is tucked away on an oceanfront cliff side and all accommodations have unobstructed oceanfront views. It is also just a ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
Enjoy the unobstructed ocean views of Southern Kauai from the lawn-access lanai of Poipu Shores 102A! Listen to the soothing sounds of the waves breaking on the rocks just feet below while you enjoy your morning coffee in the morning or ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
Poipu Shores 101A
*UNDER NEW MANAGEMENT*
(Please excuse the lack of reviews - we are currently helping our guests become acquainted with the property and the new reviews will follow shortly. Mahalo!)
Any closer to the ocean and your feet...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
Enjoy the most amazing views of Southern Kauai from the majestic oceanfront lanai of Poipu Shores 207A! Listen to the soothing sounds of the waves breaking on the rocks just feet below while you enjoy your morning coffee in the morning or ...
2 bedroom
2 bathroom
Sleeps 5
HI> Koloa
3 Bedrooms, 3 Bathrooms, Living Room, Separate Dining Room, Full Kitchen, Laundry
The Penthouse at Poipu Shores is the corner unit on the top floor, and is only a few feet from the ocean. Originally built for the developer, it is the largest ...
3 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Fully air conditioned, Poipu Shores 103A is a spectacularly located, professionally decorated oceanfront 2-BR 2-BA unit
with many recent upgrades such as engineered-wood flooring, new appliances, a split-king reclinable massage bed with
...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa/Poipu
Kauai's best kept secret, stunning Poipu Shores 105C, is our Sea Cove Hideaway! This gorgeous one bedroom, two and a half bath unit is located on the waters edge in a gated and private boutique condominium property. Beautifully remodeled and...
1 bedroom
3 bathroom
Sleeps 2
HI> Poipu
Beautiful and modern, renovated top floor unit with full kitchen, lanai, washer / dryer and WIFI! This two-bedroom two-bathroom oceanfront condo is gorgeously renovated and features a full, brightly lit kitchen with mosaic tiled back-splash, ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
This ultra modern two-bedroom two-bathroom unit features a large kitchen with black marble countertops, stainless steel appliances, and a Keurig Coffee Maker. The spacious living area includes a 42-inch LCD TV, Blu-ray player, and stereo system ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
This modern two-bedroom two-bathroom oceanfront unit has a large kitchen with stainless steel appliances and a convenient downstairs bathroom. The living area has tan woven floors and an LCD TV and DVD player. Meals can be enjoyed on an oval ...
2 bedroom
2 bathroom
Sleeps 6
HI> Koloa
Please note that repairs will take place on the grounds of this property from March 1, 2022, to April 8, 2022. Scaffolding may minorly affect the view for some units, and noise levels will be slightly higher than normal during the brief ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
This climate-controlled 2 bedroom, 2.5 bath oceanfront deluxe condo is the perfect retreat for your Poipu vacation. With a large kitchen complete with all the cooking amenities you may need, open living space with cool, tile floors, and a large ...
2 bedroom
3 bathroom
Sleeps 6
HI> Koloa
Poipu Shores is a gem property perched overlooking the Pacific Ocean on a rocky cliff where you can view sealife year round from your private covered lanai. Enjoy endless views from our oceanfront pool. The front landscaped area also has two ...
2 bedroom
2 bathroom
Sleeps 4
HI> Koloa
