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, 50
-----------------------------------------
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, 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 ...
- 3 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 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
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 ...
- 3 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...
- 3 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 that overlooks the Mauna Kea Resort golf course and Pacific Ocean. The condo has a full ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
This vacation rental is professionally managed by ResorticaHawaii
Kumulani D2. Ocean Views!
Bedding Configuration:
Master Bedroom: King Bed
Guest Bedroom: Queen Bed
Den: Fold Out Queen Murphy Bed
Mauna Kea Amenity ...
- 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
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kamuela
