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 '%Prince Kuhio%') AND sleeps>=1 order by IF (ISNULL(`bedrooms`), 1,0), `bedrooms` desc LIMIT 0, 15
Welcome to our Penthouse Condo with an ocean view from every room. You will stay in our comfy spot 'above it all' in this spacious, fourth floor, non-Smoking complex with two bedrooms (each with a queen bed), huge living room, and an over-sized ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
FOR OUR INTER ISLAND GUESTS; ASK ABOUT OUR STAYCATION COVID-19 SPECIALS UNTIL SEPTEMBER 01ST
The Prince Kuhio studio #235 is just steps from the beach and perfect for couples. It's located on the second floor with a king size bed. This ocean...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Poipu
Beauty, comfort, quality, and value are what best describes this spacious one bedroom condominium in sunny Poipu! This is a real gem with stunning ocean and mountain views from the lanai that backs up onto Prince Kuhio Park, a lush, quiet open ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Poipu
An oasis in the heart of sunny Poipu, this quaint condominium complex is impressively landscaped with mature, lush jungle foliage. Right next to historic Prince Kuhio Park and just 30 yards from the ocean, you have discovered a gem within ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
An oasis in the heart of sunny Poipu, this quaint condominium complex is impressively landscaped with mature, lush jungle foliage. Right next to historic Prince Kuhio Park and just 30 yards from the ocean, you have discovered a gem within ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> Koloa
Large 1-bedroom, 720 sq ft, Sleeps 2
King size bed (Serta Plush) in bedroom
Tile floor throughout, bar height lanai furniture for optimal viewing, and more
Third floor location is ideal for cooling trade winds that blow from bedroom through the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
An oasis in the heart of sunny Poipu, this quaint condominium complex is impressively landscaped with mature, lush jungle foliage. Right next to historic Prince Kuhio Park and just 30 yards from the ocean, you have discovered a gem within ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
3rd floor unit, done with a soft tropical touch. Adjacent to historic Prince Kuhio Park, with good ocean views, it's a walk across the street to ocean for snorkeling and surfing. Common sights include Sea Turtles, endangered Monk Seals, and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Poipu
On the sunny south shore of Kauai, Prince Kuhio Condo Unit 110 is conveniently located on the ground floor. The newly remodeled one-bedroom condo features high-end bedroom and living room furniture, and as of 2020, includes air conditioning.
...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Koloa
Enjoy the best island oasis getaway on Kauai! Located across the street from the pacific blue ocean, Beach House Restaurant, snorkeling & surfing. Easy proximity to all the majestic south shore has to offer! This private upgraded condo is just ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
This one bedroom is the perfect place to stay while in Kauai. Newly renovated and fully equipped with everything you need to enjoy and relax. This gem with a spectacular ocean view is only a 100 yards to beautiful sandy Lawai Beach or a short ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Koloa
An oasis in the heart of sunny Poipu, this quaint condominium complex is impressively landscaped with mature, lush jungle foliage. Right next to historic Prince Kuhio Park and just 30 yards from the ocean, you have discovered a gem within ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> Koloa
Welcome to the stunning Prince Kuhio island oasis overlooking the endless reaches of the deep blue Pacific Ocean! Situated in the exclusive Koloa community near all the south shore has to offer! Enjoy the pool or take a stroll along a sandy ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Koloa
Welcome to your Kauai home away from home! Prince Kuhio is the perfect location to enjoy the sunny south side of Kauai! We are next to the Prince Kuhio Memorial Park with open views across the park to the ocean with the sound of waves soothing...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Koloa
Prince Kuhio 302 is a fabulous condo with great ocean views just a few steps from a great snorkeling beach and an oceanfront restaurant that's perfect for a romantic evening including a colorful sunset.
The Prince Kuhio complex features ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> POIPU
