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
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
Paradise as you imagined Penthouse in the heart of wonderful Kapa'a Town! Pono Kai Condominiums are perfectly located RIGHT ON THE OCEAN a short drive from the airport and within walking distance of downtown Kapa'a with its restaurants and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Welcome to Pono Kai J201 in Kapaa, Kauai! Escape to your own tropical retreat at Pono Kai J201, nestled on the second floor of the sought-after Pono Kai Resort. This cozy 1-bedroom oasis is perfect for your Hawaiian getaway, offering all the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
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, ...
- 2 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
J305 is a 3rd floor condo in Pono Kai Resort. It has a fully-equipped kitchen. Just installed as of May 2024, Fujitsu Multi-Zone 24,000 BTU total with 9,000 BTU master bedroom and 18,000 BTU in living room. New bamboo shades leading to the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
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, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
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!
2025 tentative pool closure scheduled for September, October, and November 2025 ...
- 3 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
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
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
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...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapa?a
This is a 1 bedroom, 1 bathroom condo with a fully equipped kitchen and spacious living area with a murphy bed. The bedroom has a Queen size bed. The condo is fully stocked with linens, towels, soap, shampoo, conditioner and all kitchen ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapaa
Pono Kai Resort B109, Kapa'a Oceanfront two bedroom, Kauai Vacation Rental.
2025 tentative pool closure scheduled for September, October, and November 2025 (subject to change).
Bike or stroll along the Ke Ala Hele Makalae Bike ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
Pono Kai Resort C101, Oceanfront, Kapaa, Kauai Vacation Rental. Just steps to the beach and Coconut Coast bike path. Walk to shops and restaurants.
2025 tentative pool closure scheduled for September, October, and November 2025 (subject ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
WAKE UP TO BREATH TAKING KAUAI SUNRISES AND OCEANFRONT VIEWS from the MASTER BEDROOM, LIVING/DINING ROOMS and KITCHEN!!
This Beautiful 1200 sq/ft. Pono Kai Condo directly in front of the ocean and the beach...and furnished in traditional ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
Enjoy beautiful sunrises and all the luxuries that accompany beach side living. This two-bedroom ground floor condominium in Pono Kai is steps from a mile-long, sandy beach. This beautiful condominium has been privately owned, cared for, and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaa
