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 '%Kulalani%') AND sleeps>=1 order by rating desc LIMIT 0, 15
**Beautiful 3 Bedroom, 2.5 Bath, Sleeps 6**
Kulalani is a luxurious gated community, across the street from the Fairmont Orchid, in the renowned Mauna Lani Resort on the Kohala Coast of Hawaii.
Kulalani #2305 is a beautiful 2000 square foot ...
- 0 bedroom
- 0 bathroom
- Sleeps 6
HI> Kamuela
Welcome and aloha to Hale Papa Kea at Kulalani, a beautiful, fully furnished and professionally decorated 2 bedroom, 2 ½ bathroom townhouse with 1,631 sq/ft of living space and 398 sq/ft of outdoor lanai space to enjoy during your Hawaii ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Kamuela
Kulalani 205. Includes Mauna Lani Beach Club Pass
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or Community/BBQ Areas ***
...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Waimea
❤️PiH❤️ brought to you by Paradise in Hawaii Luxury Homes
Welcome to 'The Oasis at Kulalani' on the Big Island of Hawaii.
Welcome to the newest home in our Hawaiian Luxury Collection. 'The Oasis at Kulalani' is a wonderful almost new ...
- 3 bedroom
- 3 bathroom
- Sleeps 7
HI> Kamuela
🌺 Morning Glory - Best Poolside Location - Great Reviews - Always Immaculate - Always Fresh - Enjoy Coffee with Mauna Kea Views and Access to the Mauna Lani Private Beach Club 🌺
Welcome to Morning Glory our Sunrise Villa (Kulalani 2006) ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kamuela
KOHALA LUXURY RENTALS
KULALANI 501 - Two Master Suites, Two Levels, New Furnishings, Steps from the Pool!
Welcome to Kulalani 501 ~ A beautiful 2 bedroom 2.5 bath townhome retreat with vaulted 20 foot ceilings, three lanais including one with a...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Waimea
Kulalani 2405. Includes Mauna Lani Beach Club Pass
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or Community/BBQ Areas ***
...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kamuela
❤️PiH❤️ brought to you by Paradise in Hawaii Luxury Homes
Welcome to Starfish House, a part of our Hawaiian Luxury Collection. The owners are the same as that of Sea Glass House at KaMilo which is one of our most elegant homes. Like Sea...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Kamuela
UNBELIEVABLE SPACIOUS TOWNHOME ILLUMINATED WITH A WALL OF NATURAL LIGHT!
Kulalani 904 is one of the newest homes developed within the resort. At a whopping 2673 square feet, this two story paradise will easily accommodate families as well as ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Waimea
Kulalani 2403. Includes Mauna Lani Beach Club Pass
Please scroll down for detailed information
***PLEASE NOTE: Due to current Covid 19 protocols there may be modified hours for Pool Services, Fitness Rooms or Community/BBQ Areas ***
...
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Waimea
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
This showcase 3-bedroom, 2.5-bathroom townhouse on the prestigious Mauna Lani resort will make you feel like you are in paradise. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kamuela