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 (city = 'Kamuela' OR postalCode = 'Kamuela' OR region like '%|Kamuela|%') 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
The LoveRentals portfolio is comprised of curated, professionally managed vacation rental homes around the world. We only select professional property managers that meet our high standards. Book with peace of mind knowing that you will have the ...
2 bedroom
2 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
This stunning 10,000 square foot, slate and mahogany floored, 7-bedroom open air Villa is your own private paradise. Enter the expansive foyer through the beautiful etched glass double doors to step down into the 2 1/2 story living room capped ...
7 bedroom
7 bathroom
Sleeps 14
HI> Kamuela
This immaculate condo villa is conveniently located along the ocean-facing side of Mauna Lani's South championship golf course. Beautifully appointed 2 bedrooms with a huge loft, the location is in a quiet, gated community with lush greenery and...
2 bedroom
2 bathroom
Sleeps 6
HI> Kamuela
Aloha and welcome to Halenani, the premiere estate on Hawaii's sunlit Kohala Coast. Halenani is unique in its ability to create a paradise oasis within paradise itself.
The superb landscape design, ponds and gardens surrounding the home sing ...
7 bedroom
7 bathroom
Sleeps 16
HI> Kamuela
Property is currently for sale. The property manager will contact you to make arrangements to schedule showings at a convenient time.
This oceanfront Kohala Coast home with breathtaking coastline views offers the ultimate setting for a ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kamuela
Enjoy effortless beachfront living at B32, the premier oceanfront 1-bedroom at the newly constructed Hapuna Beach Residences. B32 is located on the third floor and is the largest 1-bedroom available (882 sqft) with bonus square footage and an ...
1 bedroom
1 bathroom
Sleeps 4
HI> Kamuela
Enjoy the world renowned Mauna Kea Resort from Wai'ula'ula Estate 421, a spacious four bedroom private residence with a heated pool, spa and unobstructed ocean views. Built as the developers model residence, the home is perched over the 13th ...
4 bedroom
5 bathroom
Sleeps 12
HI> Kamuela
This newly renovated 2-bedroom condo at the Mauna Lani Resort is now available for your next vacation. This condominium/townhome is bright and airy and spans two floors with large windows to allow in the natural light. This popular floor plan ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kamuela
Take advantage of Hawaii's quarantine exemption with a negative Covid test within 72 hours of travel!
COVID-19 Notice - Safety is our number one priority.
We look forward to welcoming you to our home on the beautiful island of Hawai’i and we...
3 bedroom
4 bathroom
Sleeps 8
HI> Kamuela
* Modern 3 Bedroom townhome at the exclusive Mauna Lani Resort - close to the best beaches, shops and restaurants on the island!
* Covered lanai with a BBQ and outdoor seating.
* Located a short stroll to the Fairways amenity center, where a ...
3 bedroom
3 bathroom
Sleeps 6
HI> Kamuela
Hawaii Tax I.D. NO. W67125384-01
Hale Alaula, or House of the Sunset Glow, is a renovated cottage located right on a beautiful sandy beach in sunny Puako. As you can see from the pictures, the house is full of modern and newly furnished ...
1 bedroom
2 bathroom
Sleeps 4
HI> Kamuela
Brand new to the vacation rental market, this beautifully upgraded condo at the famed Mauna Kea Resort offers the privacy and resort access that Hawaii visitors are seeking. Located in Wai'ula'ula in the Hapuna Resort, Wai'ula'ula B101 is close ...
3 bedroom
3 bathroom
Sleeps 5
HI> Kamuela
New listing Hale Mele house, your luxurious Hawaiian retreat in the prestigious gated community of Champion Ridge. Perfectly located on the sunny “Gold Coast” of South Kohala in one of the most desired resorts on the Big Island with ...
5 bedroom
11 bathroom
Sleeps 10
HI> Kamuela
