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, rewritten_description,
(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 '%private estate%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Maui's Premier Vacation Home! Rainbow Hale Estate Kaanapali – a Family Retreat or Private Honeymoon Get -away. Surrounded by 5 acres of Maui tropical plantation, lies a spectacular estate like you've always dreamed of, with the lifestyle
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
"Out of all our experiences with hosts on AirBnb, Kaili's was by far our most memorable. Not only is this house an amazing home for large families or groups to enjoy, but it's so centrally located to most top beaches, grocery and shopping on the
- 5 bedroom
- 5 bathroom
- Sleeps 16
Hawaii> Kailua-Kona
A PRIVATE ESTATE where you will experience Comfort, Ease, Quality, Carefree, Private, Relaxing, Heaven on Earth. Our permit number is BBKM 2014/0004.
We believe in the Malama Hawaii program where visitors are respected as Ohana "family" and
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Come stay and relax at this quartzite stone-sided custom built home on a 10 acre private estate located in historic Hawi on the Big Island of Hawaii. The house is built with loving care and attention to details by the owners. Our home is perfect
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Hawi
This custom-built, well appointed ocean view 1000 square foot cottage with outdoor spa is on a lush 10 acre private estate, botanical sanctuary and organic farm. Located in historic Hawi on the Big Island of Hawaii, this lovely space can sleep
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hawi
Celebrate your Hawaiian holiday with friends & family in your own Bed & Breakfast dedicated to you and your group!!
Away from the hustle and bustle of tourists and traffic, Ohi'a Kona Estates is located in the hills above Kona with beautiful
- 5 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Kailua-Kona
Welcome to Kauai's magical North Shore!!! This 3 acre property with house and guest house offers pristine privacy and ocean views with a private path to a secluded beach. Kauai Vacation Hideaway also nurtures dozens of fruit trees that guests
- 3 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kilauea
This exquisite, singlelevel, gated, oceanview private estate offers a sunken great room with sliding glass pocket doors for indooroutdoor island living, a magnificent kitchen with topoftheline appliances, custom cabinetry, and woodwork, as well
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kamuela
