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%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Aloha nui Loa! Minutes from Mauna Kea and Hapuna beaches, the Red 'Ohana 2 bedroom is located in Waimea is a quaint town with breezes and milder temperatures that provide an idyllic Hawaiian environment.
The ultimate in comfort and ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kamuela
Aloha! The Red 'Ohana 3 bedroom is located on the beautifully scenic Kohala Mountain Ranch Road on the 'sunny side' of Waimea/Kamuela, above Mauna Kea and Hapuna Beach. Waimea is a quaint town with breezes and milder temperatures that provide an...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kamuela
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
Aloha & Welcome to this slice slice of paradise at Wai'ula'ula, nestled within the prestigious Mauna Kea Resort. This rare 4-bedroom private home offers sweeping ocean views, a private infinity pool, and a separate Ohana cottage with it's own ...
- 4 bedroom
- 5 bathroom
- Sleeps 9
Hawaii> Kamuela
Aloha, picture yourself in paradise! Less than 30 minutes from the Kona airport, this beautiful three bedroom Villa provides the perfect location to enjoy all that the Big Island has to offer. Located in prestigious Wai'ula'ula, just minutes ...
- 3 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Waimea
Welcome to Wai‘ula‘ula at Mauna Kea---a private, gated community on the Hapuna Golf Course.
Relax in our 3-bedroom, 3-bathroom, 2,100 sqft. condominium and enjoy the many upgrades we’ve added to our home, including hardwood ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waimea
This stunning home offers the best of both worlds: the comfort of a spacious, private home and access to all of the facilities of the Mauna Kea and Hapuna resorts. All four bedrooms have en-suite bathrooms.
We have not furnished and ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kamuela
Aloha! Welcome to the ʻOhana Hale, your Puako retreat with stunning forest and unobstructed view of majestic Mauna Kea from the lanai, living room and dining area. This quiet and tranquil location is literally 100 steps away from the beach. ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This vacation rental is professionally managed by ResorticaHawaii
Mauna Kea High Bluffs #9. Stunning Private Home with swimming pool and ocean views. Mauna Kea Resort Amenity Program add on option
Please scroll down for detailed ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Mauna Kea Beach Resort
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
The Hawaiian word for family is ohana and we affectionately call our home, O2 or Ohana 2, as it is our family vacation home and we would love to share it with the you!
Marvel at the shimmering Pacific Ocean and tropical Kohala Coast ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Waimea
Alohale | Aloha (Hello) + Hale (Home). Kulalani is a special place you will want to return to again and again. This artistically presented home offers true serenity and privacy with world class services and amenities all around.
3 ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
Aloha! Welcome to Hapuna Beach Villa, our home overlooking the Hapuna Golf Course in the beautiful Waiulaula development with panoramic 180 degree views of the ocean and Kohala mountains. We also have partial views of the 14th green.
Our...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Waimea
This 7,768 square foot home was designed by Hawaiian architect Thomas Bingham (a protege of Frank Lloyd Wright) for indoor/outdoor living and entertainment. It has 5 bedrooms, each with a private bath, the master suite enjoying complete privacy ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kamuela
Enjoy two of the world's finest resorts, the Mauna Kea and the Hapuna Prince, at this 2 bedroom 2 bath luxury 2nd floor condo at the "Kumulani at the Uplands" of the Mauna Kea resort on the Big Island of Hawaii. This peaceful gated community is ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Villas at Mauna Kea #37 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort For many years, the Mauna Kea Resort has served the world's most sophisticated travelers. It has been their special place to visit, ...
- 3 bedroom
- 2 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
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
Hawaii> Kamuela
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
Hawaii> Waimea
Villas at Mauna Kea #22 This single-level, sophisticated, oceanfront estate offers a sunken living room with sliding glass pocket doors for indoor/outdoor island living, a chef’s kitchen with Wolf and Sub-Zero appliances, a dining area with an...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Waimea
