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 '%Kumulani%') AND sleeps>=1 order by rating desc LIMIT 0, 20
-----------------------------------------
Surround yourself with the luxury, elegance, and all the amenities of two of the world's finest resorts - The Mauna Kea and The Westin Hapuna Beach Resorts.
This 2 BR 2 bath second floor luxury...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Your tropical dream vacation awaits you at Kumulani I-1 by Gather/Elite, a deluxe, ground-level two-bedroom, two-bathroom condominium featuring ocean, mountain, and lush tropical views. Located inside the historic Mauna Kea, the resort built by ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Experience the magic of the Big Island in this luxurious 2 bedroom, 2 bath Mauna Kea Resort property, nestled above the Pacific in the gated Kumulani (“base of the sky”) community. A bright upper corner unit, Kumulani i4 has an open floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
This second-level, ocean view residence offers a great room with sliding glass doors for indoor/outdoor island living and smart TV, a chef’s kitchen with premium stainless steel appliances, and a dining table for six. Additional features ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This vacation rental is professionally managed by ResorticaHawaii
Kumulani D2. Newly Remodeled in 2023! Ocean Views!
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Den: Fold Out Queen Murphy ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Mauna Kea Resort
Primely located ground-floor condo with expansive ocean views at the exclusive Mauna Kea Resort, Kumulani A1 has received over 70 five star reviews. This luxurious two bedroom, two bath residence with den was originally selected and furnished as...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This ground-level, oceanview residence, finished in classic Hawaiian decor, offers a great room with sliding glass doors for indoor/outdoor island living, a full kitchen with premium stainless steel appliances (including a wine refrigerator) and...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
Kumulani G2 is a 2 bedroom, 2 bathroom ground floor condo with beautiful ocean views. The spacious living room has glass sliders that open to a large lanai with a BBQ that overlooks the Mauna Kea Resort golf course and Pacific Ocean. The condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This contemporary, upper-level, oceanview residence offers a great room with sliding glass doors for indoor/outdoor island living, a full kitchen, den with dining table, a king bed in the master suite and 2 twin beds in the guest bedroom. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
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
Effective October 23, 2022 we will not accept rental for less than 91 days because we have to comply with the new law of the city and county of Honolulu.
This property is about 30 minutes from the airport. And about 12 minutes from Disney ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kapolei
Located at the exclusive Mauna Kea Resort, Kumulani I2 is a spacious two bedroom ground-floor condo with beautiful ocean views. Located in the Hapuna Uplands, I2 is an excellent choice for visitors who want close access to the best beaches and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kamuela
This ground-level, oceanview residence offers a great room with several sliding glass doors for indoor/outdoor island living and sleeper sofa, a full kitchen with gas range, a king bed in the master suite and 2 twin beds in the guest bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kamuela
Located at the exclusive Mauna Kea Resort, Kumulani I3 is a beautifully designed two bedroom top-floor condo with panoramic ocean views. Located in the Uplands, I3 is a perfect choice for visitors who want enjoy the world-class Mauna Kea Resort,...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This ground-level, oceanview residence offers a great room with sliding glass doors for indoor/outdoor island living, a full kitchen, a king bed in the master suite and 2 twin beds in the guest bedroom. Additional features include air ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
This upper-level, oceanview residence, lovingly coined “You had me at Aloha”, offers a great room with a sleeper sofa and sliding glass doors for indoor/outdoor island living; a chef’s kitchen with premium stainless steel appliances and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
