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 '%Prince Kuhio%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Kaua'i Prince Kuhio 104 is a gorgeous, ground-level, one bedroom condo with ocean views. The owners spared no details in the remodel of this home! A stunning kitchen, new flooring and an inviting living room! From your private porch, enjoy the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Comfortable studio steps away from the beach!
This condo is ideal for your stay on the South Side of Kauai. You can't beat the unobstructed ocean and garden views. The condo features a king bed, sleeper sofa, small dining table and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to Kauaʻi's sunny Poʻipū. Enjoy a cozy comfortable stay in our superbly situated ground floor quiet, private end unit. This studio opens onto an east facing lanai with gentle trade winds overlooking the historical Prince Kuhio Park....
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> Koloa
Aloha and welcome to our condo in sunny Poipu, Kauai. A one bedroom, second floor air conditioned condo nestled between Prince Kuhio Park and tropical gardens. The living room has great views of the gardens and the pool. You can hear the ocean ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
Location, Location, Location! This very well maintained, lovely one bedroom Prince -Kuhio condo #303, has a great view of the ocean less than 100 feet away. It is located on the third floor of the Prince Kuhio and there is no elevator in ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
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 from the lanai across the park to the ocean with the sound of...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Large, one-bedroom, first floor condo nestled between Prince Kuhio Park and tropical gardens. The living room has great views of the gardens and pool. You can see the ocean from your lanai and, at night, let the sound of the surf lull you to ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
Prince Kuhio Resort is the perfect location to enjoy the sunny South of Kauai. Two excellent snorkeling beaches are just steps away. The famous Beach House Restaurant is across the street. All the activities of the Poipu Beach area are within a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
One-bedroom, second-floor condo with great view of the ocean, Prince Kuhio Park, and the mountains. Enjoy the view while keeping in touch with the world with a 42' HDTV and high speed internet. You can hear the ocean waves from your bedroom at ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> POIPU
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
Hawaii> Poipu
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 and fine ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
FOR OUR INTER ISLAND AND KAUAI GUESTS; ASK ABOUT OUR STAYCATION COVID-19 SPECIALS UNTIL DECEMBER 15th
Prince Kuhio Condominiums - These ocean view studio and one bedroom condominiums are located directly across the street from a small ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Enjoy the best island oasis getaway on Kauai! Located across the street from the Pacific Ocean, Beach House restaurant, snorkeling, and surfing. Easy proximity to all the majestic south shore has to offer! This private condo is just minutes from...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Po'ipu
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 4
Hawaii> 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
Hawaii> koloa
We invite you to relax and play on the beautiful and sunny south shore of Kauai in this 3rd-floor studio. (no elevators) The studio looks over Prince Kuhio Park and has an ocean view. Each of our properties is privately owned and represents ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Poipu
Just a two minute walk from the ocean, this spacious air conditioned studio is a perfect retreat for a single traveler or couple. It's located just minutes away from shops, farmers' markets, and the famous Poipu beaches. Create your culinary ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> 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
Hawaii> Koloa
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), a huge living room, and an oversized...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
