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 '%pono kai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
The Pono Kai Resort in Kauai 2 Bedroom Ocean View Unit Sleeps 6
***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” FEATURE***
Book our Amazing Luxury Villa & let The Timeshare Brokers, LLC make the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapa'a
Our condo has a fully equipped kitchen, 1 bedroom and 1 full bathroom. The bedroom has a Queen size bed. There is also a pull out sleeper sofa in the living room. They are very spacious units. The units are fully stocked with linens, towels, and...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa`a
This 2 bedroom, 2 bathroom condo comes with a fully equipped kitchen and spacious living area with a sleeper sofa. The master bedroom has a king size bed the second bedroom has a queen size bed.. The condo is fully stocked with linens, towels, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
You'll find golfing, water sports equipment, and more on site at this smoke-free condo. Kapa'a Beach Park is within an easy 7-minute walk and Kapaa Shopping Center is also a quick 8-minute walk away, so leave your car at the property, which ...
- 0 bedroom
- 0 bathroom
- Sleeps 4
Hawaii> Kapaa
This is a completely renovated 2 bedroom/2 bath 1256 sq ft. condo within a stone's throw from the Pacific Ocean! When you enter the unit you will be taken back by the breathtaking view of the Pacific Ocean. Open the patio door and immediately ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
GARDEN VIEW CONDO JUST STEPS FROM THE OCEAN!!!
This beautiful Pono Kai Condo Building H is steps from the ocean and the beach!! With a full kitchen, dining area, living room, private patio, pool, hot tub, tennis, exercise room, ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Without a doubt, the Pono Kai Zen Retreat is the finest unit on this 15 acre property. This 2 bedroom 2 bath condo is literally only steps to the warm Pacific Ocean! Golden sunrises, warm trade winds, and shimmering waters next to a mile-long ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapa'a
The resort is located in the historic town of Kapaa, Kauai. You can catch the sunrise in the morning over the Pacific Ocean or even see whales during migrating season. The resort is within walking distance of restaurants, boutiques, shopping ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
J305 is a 3rd floor condo in Pono Kai Resort. It has been a fully-equipped kitchen. New air conditioners have just been installed in May 2024. The living room has new vertical bamboo shades with sliding glass doors leading onto the lanai, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Pono Kai Resort B101, Oceanfront, 2 bedroom, 2 bath end unit in the heart of Old Kapa'a Town.
Air conditioning in both bedrooms and living/dining area!
Swim, bike or stroll along the Ke Ala Hele Makalae Bike Path right in front of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
Beautifully remodeled, 1 bedroom condo with a stunning ocean view. The only thing between you and the beach is the Kauai Coastal Path!
• Cal King bed in the bedroom (new mattress, Dec 2023)
• Queen size wallbed in the living room ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
"Lush Garden Island Condo" is a 1 bedroom, 1 bathroom condo with a fully equipped kitchen and spacious living area with a sleeper sofa. There is a Queen size bed in the bedroom. The condo is fully stocked with linens, towels, soap, shampoo, ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
This is a 1 bedroom, 1 bathroom condo was just renovated in December 2022. The condo comes with a fully equipped kitchen and spacious living area with a pull out sofa bed. There is AC in BOTH the main living space and the master bedroom!! The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
Oceanfront one bedroom plus a loft, one bath, top/3rd floor Pono Kai Resort Unit B304 with elevator and beautiful ocean views is centrally located in historic Kapa'a Town with easy access to the North and South shores of Kaua'i. Situated on 13 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Welcome to East Kaua'i!
Located within Pono Kai Resort in Kapa'a on the Coconut Coast and 20 mins. from Lihu'e Airport.
This beautiful OCEANFRONT (Newly refurbished in 2019), 1-Bedroom Condo (Queen Bed) with sleeper sofa at east ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Pono Kai Resort is located on oceanfront/beachfront property. Our 1 Bedroom Ocean View condo has a fully equipped kitchen, 1 bedroom and 1 full bathroom. The bedroom has a Queen size bed. There is also a pull out sleeper sofa in the living ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Our 1 Bedroom Garden View condo has a fully equipped kitchen, 1 bedroom and 1 full bathroom. The bedroom has a Queen size bed. There is also a pull out sleeper sofa in the living room. They are very spacious units. The units are fully ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
NOTE: Starting April 5th Kauai is open again for travel with only one covid pre-test! Hurry dates are booking fast!
Imagine sitting on your lanai watching the sunrise, ocean, and waves hitting the beach 75 FEET away! This is what a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapa?a
Pono Kai Resort is located on oceanfront/beachfront property. Our garden view condo has a fully equipped kitchen, 1 bedroom and 1 full bathroom. The bedroom has a either a queen or king size bed. There is also a pull out queen sleeper sofa in...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
Pono Kai Resort is located on oceanfront/beachfront property. Our 1 Bedroom Ocean view condo has a fully equipped kitchen, 1 bedroom and 1 full bathroom. The bedroom has a Queen size bed. There is also a pull out sleeper sofa in the living ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a