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 (city = 'Kapaa' OR postalCode = 'Kapaa' OR region like '%|Kapaa|%') AND sleeps>=1 order by IF (ISNULL(`bedrooms`), 1,0), `bedrooms` desc LIMIT 0, 15
Elegant and spacious King Hale, set beachfront in a lush tropical jungle, is located in the serene community of Haena, just 4 miles north of Hanalei. Here you will discover beautiful stretches of white sand beaches and the most breathtaking of ...
4 bedroom
4 bathroom
Sleeps 10
HI> Kapaa
Disclosure: During December 2021 and January 2022 guests stay may be impacted by beach rehabilitate work. Dates and times are to be determined
Looking for the perfect Kauai beach house rental? Explore Niulani Lanikai, a Kapa’a vacation rental...
4 bedroom
4 bathroom
Sleeps 10
HI> Kapaa
Kauai Kailani 3br Condo. Full Ocean View, Sunrise from the Lanai!
Just a few steps until your feet are in the sand. This building and condo is oceanfront. Gentle tides of the saltwater lagoons greet you. Completely renovated 3br with, pool, ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
"Kapa'a by the Sea" Beachfront Home, Air Conditioned!
TAT ID #: TA-102-514-2784-01, TVNC: #1312.
On-island manager, Garden Island Properties LLC.
We have implemented enhanced cleaning and disinfecting guidelines as well as infection ...
3 bedroom
3 bathroom
Sleeps 8
HI> Kapaa
COVID-19 UPDATE: The state of Hawaii has very strict Arrival requirements. All travelers must register with Safe Travels Hawaii program. You are responsible for complying with the State of Hawaii and County of Kauai regulations. For more ...
3 bedroom
2 bathroom
Sleeps 7
HI> Kapaa
Kauai Kailani 3br Condo. Full Ocean View, Sunrise from the Lanai!
Just a few steps until your feet are in the sand. This building and condo is oceanfront. Gentle tides of the saltwater lagoons greet you. Completely renovated 3br with, pool, ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
$250 per night up to 4 guests. $35 per night for each additional guest.
Honu Hale Kai is a warm and casual home base for your family vacation adventure. This home sits upon an oceanfront property (one house back) with a white sand swimming ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
Sunrise on a quiet beach just in front of the condo complex. Just a few steps until your feet are in the sand. Gentle tides of the saltwater lagoons greet you. Completely renovated three bedroom with no stairs. Granite counters, 2.5 bath with ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
*CoronaVirus Update:* Visitors who have taken and received a valid negative Covid-19 FDA approved PCR test within 72 hours of traveling to Hawai’i can eliminated the 14-day quarantine beginning Aug 1. The test must be taken and confirmed ...
3 bedroom
4 bathroom
Sleeps 8
HI> Kapaa
Lanai, pool, open parking. Located on the Kauai path!
• Old Hawaii Charm
• Modern Style
• Perfect Coconut Coast
• Family Vacations
• Stylish New Tile
• Tropical Carpeting
• Plantation Style Ceiling Fans
• Pool (2 pools on ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
Why stay at Fern Grotto Inn? The reasons are as numerous as our glowing guest reviews. In fact, Fern Grotto Inn recently became the best-rated vacation rental and was featured in Lonely Planet—all thanks to our warm hospitality, prime ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
At Lae Nani #511, you will feel truly relaxed, surrounded by lush tropical landscaping and listening to the soothing sound of the ocean’s waves breaking on the shore. The location of this three-bedroom suite is ideal, providing views of the ...
3 bedroom
2 bathroom
Sleeps 8
HI> Kapaa
Come to Beautiful Kauai and stay in this immaculate, beautifully furnished Home in the beautiful Wailua Rise Estates. Enjoy the peaceful surroundings while also being only 5 minutes from Golf, Tennis, the Beach, Resturants, and Shopping.
Have ...
3 bedroom
2 bathroom
Sleeps 6
HI> Kapaa
Hale Niu...TVNCU#1217
Rates shown are for 8 guests; additional guests up to maximum of 10 are $200 per person/per week and Holiday rates are $250 per person/per week.
Tropical breezes, ocean view and sunny white sand beaches make this an ideal...
3 bedroom
3 bathroom
Sleeps 8
HI> Kapaa
Lanai, pool, open parking. Located on the Kauai path!
• Old Hawaii Charm
• Modern Style
• Perfect Coconut Coast
• Family Vacations
• Stylish New Tile
• Tropical Carpeting
• Plantation Style Ceiling Fans
• Pool (2 pools on ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kapaa
