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 = 'Holualoa' OR postalCode = 'Holualoa' OR region like '%|Holualoa|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Our working coffee farm is located on 3 plus acres of land, it has 3 bedrooms, two full bathrooms, brand new kitchen ;it is peaceful, private, quiet and at a cooler 1600' elevation. It is surrounded by exotic flowers, coffee trees , macadamia ...
3 bedroom
2 bathroom
Sleeps 6
HI> Holualoa
Our private coffee estate is situated on the slopes of Mt. Hualalai on the Big Island of Hawaii. We are located in Holualoa, just 15 minutes from the Kailua-Kona Keahole Airport, 10 minutes from the quaint coastal town of Kailua-Kona, 10 ...
2 bedroom
1 bathroom
Sleeps 4
HI> Holualoa
Mauna Lea (Mountain Joy) Manor is a 5 acre private estate tucked away in the Kona Coffee country on the slopes of Hualalai volcano, overlooking the small coastal town of Kailua-Kona. On this property there are 4 cottages available rent. Each ...
1 bedroom
1 bathroom
Sleeps 5
HI> Holualoa
We are located on the west side of the Big Island of Hawaii, directly above Kailua-Kona, on the side of Mt Hualalai, in Holualoa, with an awesome ocean and coastline view! Please read our reviews because the view from the cottage lanai is better...
0 bedroom
1 bathroom
Sleeps 2
HI> Holualoa
This listing is for all 4 of our tiny house studio cottages & 3 level Manorhouse on the same property. Each cottage sleeps up to 5-6 people. When you want to stay with your family or friends but each have your own private house on the same ...
8 bedroom
6 bathroom
Sleeps 30
HI> Holualoa
Luxury Ocean View Condo Hainoa Villa 2905B at Four Seasons Resort Hualalai Make your dream vacation in Hawaii come true! This gorgeous upper level two bedroom/two bath villa is tastefully designed and appointed. The pristine condition reflects ...
2 bedroom
2 bathroom
Sleeps 4
HI> Holualoa
The Lookout Welcomes you to Relax & Enjoy Breathtaking Views from Your Private Patio Overlooking the Pacific Ocean and the City of Kailua Kona Hawaii.
*Private entrance with self checkin*
Your very own private Guest Ohana has a queen size bed
&...
1 bedroom
1 bathroom
Sleeps 2
HI> Holualoa
Come relax and explore Hawaii in our unique cottage nestled in the center of Kona coffee country with expansive views of the ocean, coffee, and mac nut farms and still only a 12 minute drive to beautiful Magic Sands Beach! We are the new owners ...
2 bedroom
2 bathroom
Sleeps 4
HI> Holualoa
Traveling with a large group? Book our entire property! Aolani House and Cottage is a perfect hidden gem for large families, corporate getaways or a couples private get-a-away. Tranquility at it's finest, our resort style home will have you ...
6 bedroom
6 bathroom
Sleeps 12
HI> Holualoa
Featured in Airbnb Magazine & Conde Nast Traveler, we welcome you to the gorgeous Hawaiian escapes of world renowned artist, Anna Sweet! Inside you’ll find tasteful modern designs featuring sophisticated, new restoration hardware furnishings ...
4 bedroom
4 bathroom
Sleeps 8
HI> Holualoa
Please watch the video (3rd in the photos section) for a walk through tour. It shows the setting and every room.
Located at 2500 feet elevation, we offer our downstairs, two-bedroom, one-bathroom ohana. Our house is located on a quiet Kona ...
2 bedroom
1 bathroom
Sleeps 6
HI> Holualoa
Aloha! We offer short stays of 14 nights or less on our Kona coffee farm. Your Hosts are vaccinated for Covid 19 and are requesting that guests staying with us be fully vaccinated as well. Mahalo for your cooperation!
Experience the charm ...
1 bedroom
1 bathroom
Sleeps 2
HI> Holualoa
The Holualoa Country Home is a one bedroom with a queen bed, small private patio area with some ocean view, cable TV, WiFi, full kitchen, bath with tub/shower. The sofa in living area lays flat to a bed. Minutes to Holualoa village. Close to ...
1 bedroom
1 bathroom
Sleeps 3
HI> Holualoa
All stays here will be EXCLUSIVE - there will be no other guests. If you book with us our larger 2 bed/2 bath unit will be vacant. The entire huge deck/pool area on the upper level will be for your exclusive use 24/7.
Our minimum stay is now 10...
1 bedroom
1 bathroom
Sleeps 3
HI> Holualoa
As of April 1, 2020, our guesthouse is now only available as a long term rental (6 month/180 days or more).
Our guesthouse is nestled along the slopes of Mount Hualalai on our 40 acre ranch and coffee estate. Located above the historic and ...
1 bedroom
1 bathroom
Sleeps 4
HI> Holualoa
