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 '%Mauna Kea Resort%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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
Hawaii> Kamuela
Rates vary based on time of year. Rates begin at $550. 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 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This luxury two-story townhome features four bedrooms (including two primary suites) and 4.5 baths, with its own private pool and spa, in an exclusive gated community. Only about 500 yards from two exceptional resorts and two of the most ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kamuela
Situated within the Mauna Kea Resort, this home is close to two championship golf courses: the newly renovated Mauna Kea Golf Course and Hapuna Golf Course. Furnished in a natural tropical motif with high beam ceilings and lots of windows, this ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
This extraordinary, oceanfront, newly-constructed, third level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a magnificent chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
Relax in the luxurious comfort and privacy of this oceanfront, deluxe studio penthouse, just steps from the white sands of world famous Hapuna Beach. Enjoy spectacular sunset ocean views from the unique, four floor-to-ceiling radius glass ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KAMUELA
This smart and chic, oceanfront, newly-constructed, second level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This beguiling, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an exquisite chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This divine, oceanfront, newly-constructed, fourth level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a professional-grade chefâs kitchen outfitted with Wolf, Sub-Zero and Miele appliances, and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This incomparable, oceanfront, newly-constructed, ground level penthouse offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a superb chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> KAMUELA
This stylish, oceanfront, newly-constructed, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This marvelous, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a luxe chefâs kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This immaculate, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek, epicurean kitchen with Wolf and Sub-Zero appliances, wine storage and hand-crafted ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This magnificent, oceanfront, newlyconstructed, island masterpiece offers floortoceiling window walls that create indooroutdoor living space, a sleek, gourmet kitchen with Wolf and SubZero appliances, double ovens, a cappuccino machine, wine ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
Tropical get-away in the Mauna Kea and Hapuna resort areas. The Kumulani area is a quiet and peaceful setting. Sit and relax on the lanai overlooking the magnificent golf course and watch the sunset over the ocean views. This condo has all you ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Presenting this exciting opportunity to vacation in a private estate in the Mauna Kea Resort's exclusive Fairways South community. Fairways South 31 has completed extensive renovations and offers guests expansive living spaces, fresh interiors, ...
- 4 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Kamuela
Stylish, island-modern residence housed within the renowned Mauna Kea Resort area overlooking the beautiful Hapuna Beach. Just steps to the ocean as well as amenities to allow for easy access to play, dine, unwind, and connect. Available access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Experience the ultimate beachfront getaway at Unit C34, a stunning 2-bedroom, 2-bathroom oceanfront condo at Hapuna Beach Resort on Hawaii's Big Island. This high-end vacation rental offers premium furnishings, curated artwork, & breathtaking ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Brought to you by Paradise in Hawaii Luxury Homes.
Welcome to Majestic Ocean Villa, a part of our Hawaiian Luxury Collection. Majestic Ocean Villa at the Mauna Kea Resort Kumulani complex has majestic views of golf, mountains and sea. You...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waimea
We are paying for your resort access!
When Laurance S.Rockefeller opened the Mauna Kea Resort in 1965, it was a new level of luxury for its hotel guests and now the guests of residential homeowners. Times have changed! At that time, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
