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, 50
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
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 ...
- 3 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 ...
- 2 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 ...
- 3 bedroom
- 2 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 ...
- 3 bedroom
- 3 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 ...
- 2 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 ...
- 3 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
This sophisticated, oceanfront, island masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a splendid kitchen with Wolf and SubZero appliances, wine storage and handcrafted cabinetry, plus a dining ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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 13
Hawaii> Kamuela
This superb, oceanfront, newlyconstructed, island masterpiece showcases floortoceiling sliding window walls that create indooroutdoor living space, a magnificent chefs kitchen outfitted with professionalgrade Wolf, SubZero and Miele appliances, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Enjoy unobstructed beachfront views in this modern, luxurious two-bedroom unit decorated with Restoration Hardware furniture throughout. Overlooking the white sandy beach, the living and master bedrooms feature floor-to-ceiling sliding glass ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
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
Escape to paradise at Hapuna Beach Residences Unit C34, a luxurious 2-bedroom, 2-bathroom oceanfront condo at Hapuna Beach Resort on Hawaii's Big Island. This high-end, premium beachfront vacation rental features stunning ocean views, designer ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Brought to you by Paradise in Hawaii Luxury Homes.
Welcome to Mauna Kea Palms, a part of our Hawaiian Luxury Collection. Mauna Kea Palms at the Mauna Kea Resort Kumulani complex has majestic views of golf, mountains and sea. You will be ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
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...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waimea
Brought to you by Paradise in Hawaii Luxury Homes.
Welcome to Ocean Palms Villa, part of our Hawaiian Luxury Collection. Ocean Palms Villa at the Mauna Kea Resort Wai'ula'ula complex has majestic views of mountains and ocean. You will be ...
- 4 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kamuela
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, Trent Jones Sr. was challenged to build a course...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
This vacation rental is professionally managed by ResorticaHawaii
Kumulani J3
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Guest Bedroom: Two Twin Beds
Mauna Kea Amenity Program-see...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Mauna Kea Resort
Brought to you by Paradise in Hawaii Luxury Homes
Welcome to "A Sea-nic Escape", part of our Hawaiian Luxury Collection. "A Sea-nic Escape" at the Mauna Kea Resort Wai'ula'ula complex has majestic views of mountains and ocean. You will be...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kohala Coast
Beautiful pod-style home at the exclusive Mauna Kea Beach Resort with panoramic ocean views. This 2 bedroom, 2.5 bathroom home is in a prime location on a bluff above the beach and features a large pool and sunning deck with spectacular sunset ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kamuela
This cozy Mauna Kea Resort vacation home offers 3 bedroom and 3 bathrooms, comprised over 2,400 square feet overlooking the Hapuna golf course. The kitchen and bathrooms have all been recently renovated (2017) with modern European fixtures, ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
This island contemporary, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a gourmet kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
Experience the elegance of a classic Mauna Kea residence at Fairways South 29, boasting breathtaking ocean views from nearly every room. The custom design seamlessly flows from kitchen to indoor dining, leading to a modern living area with ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kamuela
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
Brought to you by Paradise in Hawaii Luxury Homes.
Welcome to 'Ikena Nani at Mauna Kea Beach Resort, part of our Hawaiian Luxury Collection.
'Ikena Nani at the Mauna Kea Resort in the Wai'ula'ula complex has private pool and ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kamuela
Located only a short drive away from the Big Island's best sandy beaches, Wai'ula'ula Ridge N102 is an upgraded 3 bedroom, 3 bathroom ground-floor condo that offers privacy and easy access to the best sights on the Kohala Coast. This modern ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Where extraordinary meets island luxury, this newly-constructed, top floor, beachfront residence offers floor-to-ceiling sliding pocket doors that transform the great room into a lanai; an epicurean kitchen with professional-grade Wolf, Sub-Zero...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kamuela
This magnificent, oceanfront, newly-constructed island masterpiece offers floor-to-ceiling window walls that create indoor/outdoor living space, a chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele appliances, wine ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This discerning, oceanfront, newly-constructed, ground level masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
This exceptional, oceanfront, newly-constructed, fourth 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 refined, 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAMUELA
This impeccable, oceanfront, newly-constructed, ground level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a true chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This delightful, oceanfront, newly-constructed, second level studio offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero and Miele ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This island luxe, oceanfront, newly-constructed, ground level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a fantastic chef’s kitchen outfitted with professional-grade Wolf, Sub-Zero ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAMUELA
This incomparable, oceanfront, newly-constructed island masterpiece seamlessly fuses the blissful convenience of ground-floor beach access with exceptional privacy and size. This residence showcases floor-to-ceiling sliding window walls that ...
- 5 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
This contemporary, oceanfront, newly-constructed, fourth 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...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> KAMUELA
This oceanfront, newlyconstructed, groundlevel masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a magnificent chefs kitchen with Wolf, SubZero and Miele appliances, including double ovens and a wine ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> KAMUELA
This exclusive, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, a sleek, luxe chef’s kitchen outfitted with professional-grade Wolf and Sub-Zero ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This delightful, oceanfront, newly-constructed, top floor studio offers a floor-to-ceiling sliding window wall that creates indoor/outdoor living space, a chef’s kitchen outfitted with premium Wolf and Sub-Zero appliances, wine storage and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> KAMUELA
This brilliant, oceanfront, newlyconstructed, ground level island masterpiece offers floortoceiling sliding window walls that create indooroutdoor living space, a stateoftheart chefs kitchen outfitted with professionalgrade Wolf, SubZero and ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> KAMUELA
This ultra-refined, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls for indoor/outdoor living, a sleek, luxe chef’s kitchen outfitted with Wolf and Sub-Zero appliances, double ovens, wine storage,...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> KAMUELA
This opulent, oceanfront, third level island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an epicurean kitchen outfitted with professional-series Wolf and Sub-Zero appliances, including double...
- 4 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> KAMUELA
This ultraluxe, oceanfront, newlyconstructed, island masterpiece seamlessly fuses the blissful convenience of groundfloor beach access with exceptional privacy and space. This residence showcases floortoceiling sliding window walls, a ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> Kamuela
This magnificent, oceanfront, newly-constructed, island masterpiece offers floor-to-ceiling sliding window walls that create indoor/outdoor living space, an exquisite kitchen outfitted with professional-series Wolf and Sub-Zero appliances, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> KAMUELA
