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 '%Kona Hawaiian Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Please note, that some holiday weeks require a seven-night stay, please confirm with an agent. Prime weeks and holiday weeks have higher rates, your agent will send you an initial quote based on the dates you have chosen.
Thank you ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
**We mainly do weekly stays but we can sometimes accommodate shorter or longer stays. Please email for availability before booking. Friday to Friday or Saturday to Saturday.**
2 Bedroom, 2 Full Bath Villa located just a short drive or ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Concierge Realty is proud to present luxury villas at this premier location on the Big Island!
These are 7-night rentals, with check-in on Saturdays only.
Situated on more than thirty sloping acres overlooking Kahului Bay in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Club Wyndham Kona Hawaiian Resort 2br 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 booking process ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Enjoy this charming tropical home away from home at Club Wyndham Kona Hawaiian Resort on Kailua-Kona. Modeled in Hawaii of years past this resort has everything you need. The 2 bedroom, 2 bath unit is complete with a full kitchen and living ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Wyndham Kona Hawaiian Resort will become your home away from home. Enjoy these family friendly large (1120 square feet) Villas, which are located close to beaches and downtown Kailua-Kona.
Situated on more than thirty sloping acres ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Enjoy the perfect location for the Ironman World Championships. The beautiful Kona Hawaiian Resort was built on the site of a former native fishing village. The Two bedroom unit is spacious and Will comfortably sleep 4/6 people . The unit comes ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
When you stay at Club Wyndham Kona Hawaiian Resort in Kailua-Kona on the Big Island’s west coast, you'll feel transported to a bygone era of simple, unspoiled charm. The resort is thoughtfully designed to evoke the traditional Hawaiian ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
