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 ...
- 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 ...
- 2 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
