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 '%Kohala%') AND sleeps>=1 order by rating desc LIMIT 0, 50
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
The perfect Hawaii vacation rental on the Big Island awaits when you book this lovely Kamuela townhome in a luxury resort community! The 2-bedroom, 2-bathroom getaway offers all the essentials for a hassle-free stay including a stocked kitchen, ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kamuela
This lovely private Big Island bed and breakfast, just four miles from Hawaii's most beautiful white-sand beaches, continues to be one of the most sought-after locations. Free parking on property, includes daily breakfast and AC. The ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kamuela
From our covered lanai you have expansive views of the ocean, the sprawling Kohala ranchland and Haleakala on neighboring Maui. There really is no better way to start your day! And what you do with the rest of your day is completely up to...
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Kamuela
2BD Hali'i Kai (12C) at Waikoloa Resort now accepting longer-term stays at reduced rates. This oceanfront condo rental on the Big Island is ground-level and offers 2 bedroom options in one of the most sought-after locations. Unlike other...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Waikoloa
www.hulahularanch.com/virtual-tour.html Enjoy absolute privacy and unobstructed views of the Hawaiian sunset and migrating humpback whales to the west, Mauna Loa and Mauna Kea volcanoes to the south, and the Kohala mountains to the ...
5 bedroom
7 bathroom
Sleeps 16
Hawaii> Waimea
Aloha! The Ohana Hale at Kahua Ranch is located in the Kohala Mountains on the stunning, privately owned, 4000 acre Kahua Ranch. We are a working ranch with sheep, cattle and horses and one adorable pig. Breathe in some of the world's freshest,...
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Kamuela
Nice studio with two lap around lanai, with ocean and North Kohala coastline view for miles. 5 mins from Kawaihae harbor and Mauna Kea beach & Hotel. Clean and quiet. Microwave. Toaster oven, small fridge. Coffee maker. ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> kamuela
We invite you to experience the warmth and aloha that truly embodies the spirit of the Hawaiian Islands! Our tropical villa is located within the gated, upscale community of KaMilo. This private, quiet community is situated in the world-famous ...
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Kamuela
Brought to you by Paradise in Hawaii Luxury Homes. Welcome to Kohala Haven, a part of our Hawaiian Luxury Collection. Kohala Haven at KaMilo is a wonderful new single-level 3 bedroom home. We pride ourselves on providing some of ...
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kamuela
Puako is the only non-resort community on the coast between the Waikoloa and Mauna Kea resorts on the Big Island. Puako Beach Drive runs along the coastal reef for two miles with many beach accesses. It is one of the best diving areas in all of ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimea
Hilton Grand Vacations Club Kohala Suites Waikoloa 2 bedroom Sleeps 6! **all photos are stock images and do not guarantee the view shown** ***PLEASE DO NOT PUT IN A RESERVATION REQUEST USING THE “REQUEST TO BOOK” ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
Right in the beginning of North Kohala with an ocean view. This Tiny Home is a great place for a couple or a single family with a small kid. Close to beautiful beaches like Hapuna, Mauna Kea, 69, and Spencer Beach all less than 15 minutes drive....
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Waimea
*** Sanitation/Cleaning Update *** We understand that our guests’ expectations for hotel cleaning and disinfecting have changed. That’s why we’ve taken additional measures beyond our industry-leading cleaning standards by partnering...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village
*** Sanitation/Cleaning Update *** We understand that our guests’ expectations for hotel cleaning and disinfecting have changed. That’s why we’ve taken additional measures beyond our industry-leading cleaning standards by ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Waikoloa Village