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 (`propertyName` like '%puu poa%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Are you looking for the absolute perfect condo for your once in a lifetime vacation to Kauai? Do you want breath taking views from almost every room? Do you want to be within walking distance to multiple beaches? How about be close to shops and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Aloha
Because of the terrible heat a few summers ago, Gloria and I had an air conditioning unit installed that services the living room/atrium area and the master bedroom. This will allow guests to fully enjoy the greatest asset of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Our Oceanfront, Mountain and Sunset view is THE most famous on all of Kaua'i!!!
Newly renovated with A/C throughout unit. 4 split Mitsubishi units: one in each bedroom , one in kitchen/dining area and one in living room/atrium ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Discover the ultimate beachside oasis with this exquisite end unit boasting breathtaking ocean views from every angle. Step onto not one, but two expansive decks - perfect for soaking up the sun while grilling your favorite dishes or unwinding ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This beautiful third-floor, two-bedroom unit features stunning ocean views from almost every angle and would be perfect for a honeymoon or two traveling couples. The main living area features comfortable, modern furniture with an HD smart TV for...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
*Updated Magnificent Vacation Home
* Incredible Ocean Views, Sounds, & Breezes
* Directly on the Ocean, Bali Hai views
* Updated Kitchen, Hard Wood Floors
* High-speed Internet, 60 inch and 40 inch HDTV with extensive premium ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Step into paradise at your own Princeville oasis. This oceanfront two- bedroom, two-bathroom condo is in the coveted Pu'u Poa development. The thoughtful design and luxurious finishes truly rival the panoramic ocean views. From the custom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Comfortably furnished with great attention to detail, this first-floor condo with stairs to entry, is truly fabulous. Near the edge of a bluff overlooking the beautiful Pacific Ocean, this condo also commands spectacular views Bali Hai, the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Princeville
Puu Poa #414. TA#185-902-5408-01 TMK # 4-5-4-12-11-56 - At Puu Poa Point
The best unit in the entire complex. Recently renovated from top to bottom, this comfortable two bedroom, two bath condominium located on the edge of the Princeville ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Panoramic Ocean Views at Pu'u Poa 212
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Pu’u Poa Unit 212 in Princeville, Kauai, has unequaled...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This Air Conditioned Puu Poa condo sits on an ocean bluff next door to the renowned 1 Hanalei Hotel. This luxury two bed, two-bath condo boasts 1900 square feet, spacious rooms, tasteful décor, new granite kitchen countertops, newly painted ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This modern ocean bluff, air-conditioned condominium offers magnificent vistas of the Pacific Ocean and coastline. Enjoy the ever-changing views of the ocean that can be viewed throughout, including the living room, atrium, primary bedroom, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Puu Poa 211 Air Conditioned unit is perfect for a relaxing vacation. This Princeville condo has spectacular Bali Hai and ocean views from the lanai, living room and indoor outdoor style atrium. Nicely furnished.
The three couches in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Completely renovated in 2022 this rental is one of the best oceanfront condos on Kauai. This two bed two bath unit boasts sweeping ocean views from your kitchen, living room, dining room, lanai, and primary bedroom. Pu'o Poa is the perfect ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to your dream retreat at Puu Poa 309! This impeccably renovated (completed - 3/1/24) two-bed, two-bath condo has AC throughout and two oversized lanais that have one of the greatest views on Kauai's north shore.
The condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
