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 (`headline` like '%Kohala%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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 you:...
4 bedroom
2 bathroom
Sleeps 8
HI> Kamuela
Hale Kea Cottage: Hosted With Big Heart & Aloha "Just For Two." . Please note following.
Owners live in separate house. Their DOG and a CAT never go inside the rental.
1) Must be vaccinated against COVID-19 (vax card).
2) There is a 7 ...
1 bedroom
1 bathroom
Sleeps 2
HI> Waimea
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. The consistently sunny skies, magnificent ocean views, and expansive ...
1 bedroom
1 bathroom
Sleeps 2
HI> Kamuela
❤️PiH❤️ 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 the most sought after homes on the Kohala Coast....
3 bedroom
3 bathroom
Sleeps 7
HI> Kamuela
2BD Hali'i Kai 12C at Waikoloa Resort now accepting longer-term stays at reduced rates. Please inquire for details. If more space is desired, we also manage the 3-bedroom condo directly above, 12G. As availability allows, larger groups are ...
2 bedroom
2 bathroom
Sleeps 4
HI> Waikoloa
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
KOHALA LUXURY RENTALS
KULALANI 501 - Two Master Suites, Two Levels, New Furnishings, Steps from the Pool!
Welcome to Kulalani 501 ~ A beautiful 2 bedroom 2.5 bath townhome retreat with vaulted 20 foot ceilings, three lanais including one with a...
2 bedroom
3 bathroom
Sleeps 4
HI> Waimea
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 northeast.
Marvel in the panoramic ocean views from ...
5 bedroom
7 bathroom
Sleeps 16
HI> Waimea
Thank you for showing interest in Kohala Lodge. This Beautiful 2400 square ft craftsman-style Paniolo (cowboy) retreat is your's for you and your guest only. The retreat sets on 3.5 acres located at the Northwest coast of the Big Island above ...
1 bedroom
2 bathroom
Sleeps 2
HI> Hawi
Discover the bliss of Big Island living at this Kamuela vacation rental townhome! Situated on the Kohala Coast in the Fairways at Mauna Lani luxury resort community, this 2-bedroom, 2-bath property offers 1,300 square feet of living space. Enjoy...
2 bedroom
2 bathroom
Sleeps 4
HI> Kamuela
If you are looking for a beautifully maintained and tastefully furnished, very private condo with amazing views and right on the golf course, this is for you. Perfect for couples, this one bedroom unit is breath taking and peaceful, a perfect ...
1 bedroom
1 bathroom
Sleeps 2
HI> Waikoloa Village
Aloha and welcome to paradise!
Located in the prestigious Wai'ula'ula development, our villa is 30 minutes North of Kona and minutes from one of the most beautiful beaches in all of Hawaii. Your island home features sunset views, views of the ...
3 bedroom
4 bathroom
Sleeps 8
HI> Waimea
With an ocean view of Anaeho'omalu Bay, and a mountain view of Hualalai, this highly coveted penthouse is now welcoming guests after years of exclusive and private use.
Kolea 7F is a 3 bedroom, 3.5 bath just a stone's throw from the Kolea ...
3 bedroom
4 bathroom
Sleeps 6
HI> Waikoloa Village
Please ask about rates as they are currently flexible.
A tropical oasis with natural lava rock, salt water, infinity-edge pool, massaging bubble bed and waterfalls give the guest a feeling of vacation paradise! Located in the desirable ...
3 bedroom
3 bathroom
Sleeps 6
HI> Waimea
Nice studio with lanai, with ocean and coastline for miles. Clean and quiet. Microwave. Toaster oven, small fridge. Coffee maker. WiFi. Internet . portable A/C.
Queen size bed. Private Parking. Private entry way. Near all the best ...
1 bedroom
1 bathroom
Sleeps 2
HI> kamuela
