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 '%kuhio shores%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Beautiful oceanfront Poipu condo in the closest complex to the water. This ground floor corner unit in Kuhio Shores offers stunning ocean views from every room, with the seawall and water just steps from your lanai.
This condo features one ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
Kuhio Shores 112 is a quiet, private oceanfront air conditioned condo that is 10 steps from the water’s edge. No need to deal with stairs or elevators; simply walk out the sliding glass door, and enjoy the ocean’s majesty! The first-floor...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
Kuhio Shores oceanfront resort is Poipu's hidden gem! It is only steps from the waters edge where you can enjoy spectacular sunset views in the summer, watch turtles and surfers from your lanai all while taking in the refreshing trade winds that...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
This beautiful 2 bedroom and 2 bathroom condo has been fully updated and sits directly on the ocean. You will not find a better view and location in Kauai.
All beach amenities such as towels, chairs, and boogie boards are provided.
3 AC Units...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Enjoy spectacular ocean views in one of Koloa’s most sought-after oceanfront condominiums. This south and west facing 2-bedroom, 2-bath corner condo has a unique view of Kauai's unsurpassed sunsets. Floor to ceiling windows, an elevated ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
Ready to relax to the sounds of the sea? Here is your amazing beachfront condo just steps to the water, located on the beautiful island of Kauai.This beautiful oceanfront Kuhio Shores condo is located in Poipu on the south shore of Kauai. It is ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
New mini split Air Conditioning system just installed to make your Kuhio Shores 217 cooling experience perfect. Open windows, trade winds, fans to hear the soothing sounds of the ocean. And now if none of the above works you have use of a quiet,...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
Kuhio Shores is on the sunny South Shore of Poipu Beach and is small and quiet. It extends out into the ocean so that there is water on each side. Our condo has an expansive ocean view from wall-to-wall, floor-to-ceiling windows in the living ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
Hawaii Tax ID# TA-034-303-5904-01 and GE-034-303-5904-01
New! Air Conditioning!
Thirty feet from the waters edge, our spectacular oceanfront, ground floor, one bedroom, Poipu condo, has fantastic snorkeling, famous surf spots, and incredible ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
This one bedroom one bath oceanfront unit is on the fourth floor of Kuhio Shores condominium located in sunny Poipu, Kauai. Fronting a sandy bay this four story complex offers some of the best ocean views, swimming and snorkeling available in ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
1 bedroom oceanfront luxury condo w AC, Netflix, ocean views from every room, watch sea turtles and surfers from the balcony, full kitchen
Your oceanfront home in paradise is tastefully furnished in breezy island style, luxuriously appointed ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
The owners searched the world for this incredible oceanfront condo, and spent a fortune creating an absolute masterpiece of design, and world class quality. The hard surfaces of this condo consists of the finest Italian marbles and granites, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Poipu
ON THE OCEAN condominium with spectacular views. SURF - SNORKEL - SUNBATH - SIGHT SEE - or SIMPLY relax in a peaceful corner of Kauai Island.
Dazzling views of the sparkling tropical Pacific Ocean welcome you the moment you step in the door. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa
***COVID 19 NOTE: http://travel.hawaii.gov provides current info on travel requirements pertaining to Covid. Hawaii/Kauai policies keep changing depending on current data; however, it seems Hawaii will be most consistent with least restrictions ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Poipu
The ULTIMATE beachfront Kauai condo vacation rental! PRIVACY combined with absolutely indescribable ocean, surf, and beach views. This FOURTH FLOOR, west corner unit of Kuhio Shores, is sixty feet above the waves, steps from the beach, in a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Koloa/Poipu, Kauai
