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 '%Mauna Kea%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This destination home is in the seat of horse country in the beautiful town of Kamuela/Waimea. This A-frame challet home has spectacular views of Mauna Kea and the verdant green, rolling hills of paniolo (hawaiian cowboy) country. Cozy up to ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Waimea
Come live like a local one month at a time! One bedroom with Tempurpedic queen bed/One bath with additional four poster queen bed in living room for additional sleeping known as a Punee in Hawaii. Tropical landscaping with ocean views from front...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Waimea
Come and enjoy a luxurious laid-back island living in our new home located in the beautiful new neighborhood in Waikoloa Beach Resort – Ainamalu. This gated community Ainamalu "Protected, Peaceful Land" is the newest resort community in the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Waikoloa village
Rates vary based on time of year. Rates begin at $399. per night, plus taxes and fees. Please contact owner for details, by clicking on Send email to owner.
This luxurious 3 Bedroom 3 full Bath residence with high-end finishes and gorgeous ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waimea
**Effective January 1, 2022 the state of Hawaii has added a 3% county tax. The new tax rate is 17.962%** This goes into effect for guests arriving after the new year for both hotel and rental guests.
Professionally decorated former model home...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Waimea
Aloha and welcome to our home, villa #311. Our villa offers concierge service to all of our guests. We have ocean views, a private pool with spa, and gourmet kitchen with professional grade appliances. The villa is bright, airy and spacious with...
- 3 bedroom
- 4 bathroom
- Sleeps 8
HI> Kamuela
**Effective January 1, 2022 the state of Hawaii has added a 3% county tax. The new tax rate is 17.962%** This goes into effect for guests arriving after the new year for both hotel and rental guests.
MEMBER OF THE CLUB - This allows you to ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Kamuela
Mauna Kea Fairways North 16 is an architecturally inspired 4-bedroom private home located in the famed Mauna Kea Beach Resort. This grand family vacation home boasts spectacular ocean views as it was constructed on a bluff overlooking the ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Kamuela
Enjoy stunning ocean views as far as the eye can see. Watch the whales, gaze at the stars, grab a good book, relax in the spectacular pool and spa, or just enjoy time with family and friends in generous outdoor living areas. All in a comfortably...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Kamuela
Behold the most breathtaking view of the Kohala coastline! Hale Malie is a newly available, 3 bedroom, 3 bathroom Fairways South home at Mauna Kea Resort. This charming family-friendly home features a private heated lap pool, spacious rooms and ...
- 3 bedroom
- 3 bathroom
- Sleeps 10
HI> Kamuela
Upon opening the front door, you are met with stunning and breathtaking unobstructed180+ degree views of the ocean, Maui in the distance, golf course and mountains making this property truly special. The home is covered in beautiful travertine ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Waimea
This newly renovated four bedroom luxury property, walking distance to the Mauna Kea Beach Hotel, offers the ideal oasis for relaxing and luxuriating in paradise. Unwind on the generous private pool deck and covered lanai and revel in fabulous ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Kamuela
❤️PiH❤️ brought to you by Paradise in Hawaii Luxury Homes.
Welcome to "You Had me at Aloha" Villa, a part of our Hawaiian Luxury Collection. "You Had me at Aloha" Villa at the Mauna Kea Resort Kumulani complex has stunning views of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kamuela
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
This wonderful Golf Villas ground floor condo is the perfect spot for some quiet relaxation and to enjoy all that Big Island has to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kamuela
TRULY AN EXCLUSIVE EXPERIENCE!
Insiders know about the Mauna Kea Villas, and THIS STUNNING villa with a beautiful interior space was the original model at Mauna Kea because of its superb location. It is most private, has unparalleled views, ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Kamuela
