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
This is unique property has singular direct beach access to Polo Beach.
Walk out of your oceanfront yard over a short lava rock path to put toes in the powdery sand, snorkel, boogie board and sand castle. Come back to jump in the pool, ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kihei
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
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
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
Welcome to your own private paradise! This stunning home is situated on 24 acres of lush land, surrounded by the natural beauty of Hawaii. Immerse yourself in the best that the island has to offer, with refreshing waterfalls, mulitle on property...
- 6 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Hakalau
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...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Hawi
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
