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 '%kuhio shores%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 amazing oceanfront Kuhio Shores villa is located in Poipu on the south shore of Kauai. Our ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Closest complex to the water in Poipu, this corner unit in Kuhio Shores is situated on the third floor. Repeat guests come year after year to enjoy our air conditioned oceanfront condo. Whether it's to be lulled to sleep by the sound of the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
New mini split Air Conditioning system just installed to make your Kuhio Shores 415 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
Hawaii> 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
Hawaii> 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
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Located on the sunny South Shore at Poipu Beach, just 10 steps from the water's edge, this 1 bedroom condo offers the best oceanfront vacation rental that Kauai has to offer.
At Kuhio Shores #113 nothing can beat the location and views. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Koloa
This spacious top floor, one-bedroom condominium in Kuhio Shores offers an open living, dining and kitchen area, well appointed bedroom with new pillow top mattress, bathroom and impressive lanai (covered deck for you non-islanders!). Enjoy ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
ON THE OCEAN condominium "Turtle Haven" steps from the sea near Poipu with spectacular views. Swim, surf, snorkel, sunbath or just relax in a peaceful corner of Kauai Island. Dazzling views of the tropical Pacific Ocean welcome you as you step ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
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...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
One of the best ocean views in Poipu at an unbeatable price. Across the street from Prince Kuhio Park, this unit overlooks one of Kauai's many great surf breaks (PK's). This beautiful third floor 900 sq ft. 1 bedroom 1 bath condo sits just 70 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
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
Hawaii> Poipu
The ultimate beachfront Kauai condo vacation rental! Privacy combined with absolutely indescribable ocean, surf, and beach views. This four-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
Hawaii> Koloa\/Poipu, Kauai
Enjoy paradise from this oceanfront condo only steps away from world-class snorkeling and surfing. The beautiful, exceptionally clean and well appointed condo is located in sunny Poipu, the dryer side Kauai. From your own private lanai, you ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
This air conditioned two bedroom two bath oceanfront unit is on the second floor of Kuhio Shores condominium located in sunny Poipu, Kauai. Fronting a sandy bay this complex has as an elevator and offers some of the best surfing, body-boarding, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Best location in Poipu! Oceanfront - Thirty (30) feet from the ocean - rare and unique corner unit. Two bedroom, 2 bath. Ceiling to floor windows across the entire front of the condominium. The condo faces South at the OCEAN and then ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Koloa
From the oversized lanai, which overlooks Turtle Cove, watch amazing sunrises and turtles feeding near the rocks below, and in the winter, watch breaching whales and blow holes. Hear and watch the constant ebb and flow of the waves rolling ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
