select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kumulani%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
HI> Kamuela
NOTE: Because of the Corona Virus pandemic, the government of Hawaii has implemented certain restrictions so potential renters are encouraged to review Hawaii's Safer-at-Home orders that may be in place for the dates they plan to reserve. As the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kamuela
Kumulani Unit H-3 This beautiful 2 bedroom, 2 bath condominium overlooks the sweeping ocean views. The unit has central air-conditioning, custom interiors, a spacious great room, a fully equipped kitchen, and a covered furnished lanai. This unit...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waimea
Your tropical dream vacation awaits you at Kumulani I-1, a deluxe, ground-level two-bedroom condominium featuring ocean, mountain, and lush tropical views. Located inside the historic Mauna Kea, the resort built by billionaire businessman ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kamuela
Effective August 1st, 2019 we will not accept rental for less than 30 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
HI> Kapolei
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
HI> Kamuela
Kumulani Unit E-2 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort Kumulani Heights is a private gated community and is located and accessed through The Westin Hapuna Beach Resort entrance. This beautiful ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kamuela
This ground-level, oceanview residence offers a great room with sliding glass doors for indoor/outdoor island living and sleeper sofa, a full kitchen, a king bed in the master suite and 2 twin beds in the guest bedroom (can be converted into a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Waimea
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
HI> Kamuela
Located at the exclusive Mauna Kea Resort, Kumulani I2 is an ideal two bedroom corner condo with beautiful ocean views. Located in the Uplands, I2 is a perfect choice for visitors who want to be close to the best beaches and resorts on the Big ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kamuela
