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 '%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
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage era, in this two-story, luxury home. *400mb...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
"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
Welcome to the Green Pineapple, a gorgeous 3 bedroom estate on Oahu's North Shore with an attached studio apartment. Take a walk down memory lane in style, as this gorgeous North Shore Oahu estate has all the charm of Hawaii's vintage in this ...
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Haleiwa
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
Tropical Paradise Awaits: Big Island Hawai'i Estate
Nestled on a sprawling 5-acre property, this breathtaking Big Island Hawai'i estate offers the ultimate in luxury and seclusion. Surrounded by lush tropical gardens, swaying palm trees, ...
- 5 bedroom
- 4 bathroom
- Sleeps 16
Hawaii> Pahoa
30 DAY RENTAL*****A beautiful, peaceful, private north shore estate, 300 ft from sunset beach. The main house has 4 bedrooms and 4 bathrooms and sleeps 8 in beds. You may rent the cottage for an additional 2 people-check availability-it's called...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Haleiwa
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
This lush private estate offers sweeping ocean and mountain views from a hilltop above the beach. Custom throughout and fully equipped, the main house has 3 bedrooms/2 baths; the included private suite has a queen bed, bath, kitchenette, and ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Haleiwa
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
