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 (city = 'Kapaau' OR postalCode = 'Kapaau' OR region like '%|Kapaau|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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
Hawaii> Kapaau
Located just past the town of Hawi in North Kohola, this historic Plantation House dates back to the 1940's when the Union Mill company farmed the land for sugar cane. It once belonged to the plantation doctor. Our renovation has focused on ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapaau
Situated on a 20-acre farm, Hale Makana Kamalei is an expansive 4-bedroom house with an open floor plan. Explore the island as locals know it.
Discover the back orchard and enjoy fruits from more than 50 fruit trees, vegetables from our ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kapaau
Welcome to our country farm, located at the northern tip of the Big Island of Hawaii. A secluded estate farmland with lots of room - 23 acres - to create Fun & happy memories gathering fresh fruit and vegetables, interacting with our horses ...
- 7 bedroom
- 4 bathroom
- Sleeps 14
Hawaii> Kapaau
Located on a private, gated road, experience fresh air that's travelled thousands of miles over the ocean. Explore 15 acres of the most beautiful property in the universe. Be prepared for 50,000 shades of verdant green to welcome you to a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kapaau
Aloha and welcome to our private banana tree house. Experience a peaceful, unique cottage with spectacular ocean & lush tropical valley views. The cottage floats above and overlooks an operating organic banana orchard. Gated in a 20 acre estate,...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaau
Your Big Island Secluded Oasis Awaits!
Available November 20 - 30, 2024 - Book Now!
A one-of-a-kind modern architectural gem, this home features high ceilings, floor-to-ceiling windows, and sweeping views of the ocean and the island ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaau
