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 = 'Kapaau' OR postalCode = 'Kapaau' OR region like '%|Kapaau|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This studio cottage sits on 5 acres of orchard, gulch and gardens with the Kohala coast as a backdrop. The owners farm the back 20 acres with cattle, sheep, goats and chickens. We are located midway between the towns of Hawi and Kapa'au--both...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kapaau
*20% OF DISCOUNT FOR THE MONTH OF NOVEMBER *
The Big Island of Hawaii's, North Kohala is a travelers' delight, off the beaten path, it makes the area one of Hawaii's best-kept secrets. Experience ''Old Hawaii'' in the charming historic towns of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kapaau
Hawaiian retreat on 5.44 acres with gorgeous ocean views. Watch the whales and enjoy brilliant sunsets daily, see the snow on Mauna Kea or enjoy fireworks from the resorts along the Kohala Coast on New Years'. About 10 minutes away from ...
- 3 bedroom
- 3 bathroom
- Sleeps 10
HI> Kapaau
Hawaii Island Retreat's eco-boutique hotel and wellness spa is a place of intentional peacefulness, set among towering evergreens and ocean breezes along Hawaii Island's North Kohala coast. When you visit Hawaii Island Retreat you quickly ...
- 10 bedroom
- 10 bathroom
- Sleeps 35
HI> Kapaau
Hawaii Island Retreat's eco-boutique hotel and wellness spa is a place of intentional peacefulness, set among towering evergreens and ocean breezes along Hawaii Island's North Kohala coast. When you visit Hawaii Island Retreat you quickly ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kapaau
Hawaii Island Retreat's eco-boutique hotel and wellness spa is a place of intentional peacefulness, set among towering evergreens and ocean breezes along Hawaii Island's North Kohala coast. Here, you can relax and unwind on 50 oceanfront acres ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kapaau
