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 '%estate%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Hale Aloha… On The Sand TVNC-4185
Anaik Martin, is the property manager, lives nearby and is available 24 hours a day, 7 days a week, (808) 652-3410. Professionally cleaned and disinfected by a licensed ...
- 6 bedroom
- 7 bathroom
- Sleeps 12
Hawaii> Anahola
Hale Aliomanu- a Private Beachfront Estate Home TVNC-4245
Anaik Matin, property manager, lives nearby and is available 24 hours a day, 7 days a week
Professionally cleaned and disinfected by a licensed company. There are at least 24 hours...
- 6 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Anahola
Treat yourself to ultimate island luxury in this architecturally designed $5.5M home on 15,000 sq ft lot in one of the most desired locations on Oahu's south shore. Nestled on the slopes of Diamond Head just outside of Waikiki, this exquisite ...
- 4 bedroom
- 6 bathroom
- Sleeps 9
Hawaii> Honolulu
Make unforgettable memories in this secluded, polynesian-styled beachfront estate situated on the eastern side of Oahu next door to the famous Magnum PI Robin's nest. Flanked by the majestic Ko’olau mountains on one side and a reef-protected ...
- 3 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waimanalo
Welcome to one of Hawaii’s most secluded luxurious ocean front estates! Our family vacation home is situated on a 16 acre heart of palm plantation and is located on the Scenic Route, just minutes from downtown Hilo.
Upon entering ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Papaikou
Located in central Kihei, nestled on the northern end of beautiful Halama Street. Family-owned and operated legal short-term rental. There are two dwellings on this beach front property - an apartment building with four units and a detached 2 ...
- 6 bedroom
- 4 bathroom
- Sleeps 15
Hawaii> Kihei
Welcome to Pacific Sunset Villa, home of expansive ocean views and ultimate luxury. This award winning home with 4 bedroom with ensuite bathrooms and 3 half bathrooms sits on an oceanfront setting and offers easy access to all the amenities at ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waikoloa
Rates shown are for 8 guests; additional guests up to maximum of 11 are $20/night each.
This magnificent, island-style estate truly exceeds all expectations. Located on secluded Pakala's Beach on Kauai's west side, this totally serene ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waimea
This serene estate is named Hululani, or "Heavenly," for its pristine beauty. This truly fabulous home has awesome, panoramic views of the mountains, Bali Hai and Hanalei Bay from virtually every room as well as from the private, saline swimming...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Princeville
Kipuka Lani offers a fresh approach to luxury resort living. The modern tropical design seamlessly links interior and exterior spaces in the lush Mauna Lani landscape. Embracing the spirit of Malama Aina - respect for the land, this luxury ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kamuela
Spacious luxury estate in a highly desired beachside location that comfortably sleeps 10 guests. Enjoy luxurious island living only a few steps away from the sands of the acclaimed Beach Club in a sprawling private home in the exclusive Cape at ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kohala Coast
Gorgeous, Contemporary, Newer Home Exemplifies Luxury and ease of Living in our Island Paradise. This 5-bedroom, 5 1/2 bath pod-style estate home is exceptionally appointed and designed to take full advantage of the temperate climate and island ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
This beautiful upstairs 1 bedroom Ohana is located in Poipu Beach Estates just minutes from all sunny Poipu has to offer. A perfect retreat for a couple looking to find a comfortable place to stay in a charming setting. The bedroom has a Queen...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
ALOHA & WE WELCOME YOU TO OUR COMFORTABLE HOME AWAY FROM HOME! LOCATED IN THE SUNNY SOUTH SIDE OF THE GARDEN ISLAND OF KAUAI IN POIPU BEACH, THESE 2 HOMES HAVE BEEN RECENTLY CONSTRUCTED WITH TOP OF THE LINE FEATURES, PERFECT FOR A PARADISE ...
- 5 bedroom
- 5 bathroom
- Sleeps 14
Hawaii> Koloa
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
Kona recalls the Beach Boys playing their pop hits by the pool. The band left but the good vibrations linger. Situated on 9.5 gated acres, gated for with an amazing Hawaiian plantation-style home with separate sleeping quarters and a guest ...
- 5 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Captain Cook
Kona Coffee Estate Guest Apartment with Ocean and Garden Views
Our estate is situated on the slopes of Mt. Hualalai on the Big Island of Hawaii. We are located just 15 minutes from the Kona Airport, 10 minutes from the quaint coastal town...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Holualoa
Aloha,
And welcome to Maka Nui! The name means Big view which you will see when you enter the gate to our paradise. The home sits on a cliff overlooking an undeveloped and historic coastline including Kealakekua Bay and Pu'uhonua o' ...
- 6 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Captain Cook
As of April 1, 2020, our guesthouse is now only available as a long term rental (6 month/180 days or more).
Our guesthouse is nestled along the slopes of Mount Hualalai on our 40 acre ranch and coffee estate. Located above the historic ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Holualoa
Dropped again! 30% LAST MINUTE DISCOUNT for the dates between April 2nd to MAY 5th 2025. Don't miss this opportunity to stay in this amazing luxury home with million $$ VIEWS!!! Just select your dates for an instant booking and the discount ...
- 4 bedroom
- 3 bathroom
- Sleeps 9
Hawaii> Kamuela
Enjoy the best Hawaii has to offer in this luxury 10,000 sq ft Kona Coast tropical estate on The Big Island in the Champion Ridge enclave of Mauna Lani. Enjoy the private heated lagoon pool and spa and access to many of the Mauna Lani resort ...
- 7 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Waimea
Enjoy the luxury and comfort of our newest Elite home centrally located in Poipu Beach Estates. This custom home takes full advantage of many views and is also positioned to enjoy the fabulous trade wind breezes. The spacious and open great room...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Poipu
30 Day Minimum Stay Required
Share the romance and beauty of the North Shore with your entire family!
Swing open the gate to our Sandy Beach, sink your toes into the white sand and say “Aloha” to the turtles who come to our ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waialua
30 Day Minimum Stay Required
Share the romance and beauty of the North Shore with your entire family: 2 separate fully appointed living spaces with high end amenities great your every moment of your vacation.
Swing open the gate ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Waialua
Ho'ohu Nani has Four Bedrooms (2 Master Suites), Den with Fold-Out Couch, Four Full Bathrooms, and Two Half Baths. Ocean Views from Upstairs Living Area and Covered Lanai. The Kitchen includes top-of-the line appliances with a new Wolf ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Koloa
Stately and serene, THE HOME OF THE HULA MOON is one of the most beautiful contemporary oceanfront properties in PUAKO. Each bedroom has a perfect ocean view. Located near the end of the road, you will enjoy year-round sunsets and privacy. ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Puako
Pacific View is nestled in the majestic West Maui Mountains, a vast area of lush valleys and steep cliffs. This lovely 5-acre estate is uniquely situated to capture all of the beautiful west Maui offers.
Pacific View is a gated villa ...
- 6 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Lahaina
Welcome to Hale Mana, one of Kauai's most beautiful island treasures. This stunning three-acre private estate will bathe you in every luxury with its beautifully appointed amenities, secluded beach, and tremendous ocean views.
If you are ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kilauea
We invite you to sit back, relax and experience the dazzling sunsets, tropical breezes and never ending ocean views from "Mango Sunsets", our private 3-acre Kohala Ranch estate where modern elegance meets traditional quality craftsmanship. This ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
***Please note there is a 7-night minimum during the holidays. Christmas reservations must check out by 10 AM on Dec. 27 and New Year's reservations can check in on Dec. 27 or after for a week. There is a 14-night minimum to book both Christmas ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Kailua Kona
Welcome to the exquisite Waiulu Estate Villa at Four Seasons Hualalai Resort. This estate offers a private and serene setting with close wide-open ocean views. The estate has been recently updated with new contemporary décor by the renowned ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
STVR 19-363225 - NUC 19-1616
Picture yourself vacationing in this magnificent and upscale 6,000 square foot oceanfront home. The stunning house is comprised of 4 bedrooms and 4½ baths with a variety of features including an atrium ...
- 5 bedroom
- 5 bathroom
- Sleeps 11
Hawaii> kailua Kona
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
Hawaii> Kamuela
Greetings! Welcome to the - HaleAlohaEstate - on Oahu's North Shore. Come experience a truly authentic Hawaiian experience in our beautiful Estate home with 6+ bedrooms, and 4.5 bathrooms on the quiet – best part of Oahu’s famous North ...
- 6 bedroom
- 5 bathroom
- Sleeps 20
Hawaii> Waialua
One of the most stunning homes and locations in Maui. The beachfront home is 'U' shaped, with a spacious lanai area which includes a pool and spa and which overlooks the beach, ocean, and the island of Lanai. Located on a private palm tree ...
- 5 bedroom
- 7 bathroom
- Sleeps 10
Hawaii> Lahaina
“Notice: In compliance with the Revised Ordinances of the City and County of Honolulu, the Property will be made available for your use and occupancy for a period of thirty (30) days.”
$79.00 Admin Fee is for our Damage Protection ...
- 9 bedroom
- 8 bathroom
- Sleeps 20
Hawaii> Waimanalo
Welcome to Kuilima Estates West. Location! Location! Location!
Only steps to the grounds of the famous and newly renovated Turtle Bay Hotel on the North Shore. We are a Legal Vacation Rental.
The condos are Surrounded by Two ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Live the dream at this oceanfront gem, with wide beach frontage and an inviting courtyard pool that beckons you to experience the ultimate Hawaiian retreat in style. Kick off your shoes and step barefoot off your expansive covered lanai onto a ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Waimea
Gracious comforts of yesteryears and featured in the coffee table book, The Hawaiian House Now, the home is an historic 1895 structure, modified in the 1940s. Retaining the charm of old Hawaii with architecture and furnishings of the day, while ...
- 4 bedroom
- 3 bathroom
- Sleeps 11
Hawaii> Holualoa
GE/TA 125-285-3760-01
TA#W90869281-01
Permits: TVNC 4272 SP2012-10
Welcome to Jungle Bee!
Note: Pricing includes both homes on the property and exclusive use of the estate.
Jungle Bee Farms is a stunning 23 acre ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kilauea
This “Over-the-Top” estate is a resort within a resort! Guests often choose to Opt Out of paying the resort fees and enjoy this quiet retreat without the hustle and bustle of the resort activity. Beautiful infinity pool with sandy ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Kailua-Kona
Surf's up on the North Shore and our condo is the perfect place to Hang Ten! You'll appreciate the attention to detail, modern amenities and the chic, decor when you escape to our Surfer's Paradise. Our location provides easy access to the ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
This amazing and completely refurbished home is situated on a stunningly beautiful one-acre, gated OCEANFRONT estate property, right in the middle of magical Hanalei Bay. It is, in fact, one of the ONLY vacation rental cottages on beachfront ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Hanalei
Maui County Rental permit - STPH 2014/0004
TA-097-868-8000-01 / GE-097-868-8000-01
TMK-270031100000
Welcome to Seaview! This is a unique family style vacation home, offering space for up to ten family members or friends to vacation ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Haiku-Pauwela
5 STAR REVIEWS! STUNNING VILLA! TASTE OF OLD HAWAII ON THE BEST BEACH IN PUAKO!
This lofty and spacious Hawaiian villa lies on the Gold Coast of Hawaii, the sunniest spot in the state. Aloha Sands is also on the best sandy beach in ...
- 4 bedroom
- 5 bathroom
- Sleeps 11
Hawaii> Kamuela
Villas at Mauna Kea #37 FREE resort access to both the Mauna Kea Beach Hotel and The Westin Hapuna Beach Resort For many years, the Mauna Kea Resort has served the world's most sophisticated travelers. It has been their special place to visit, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waimea
This spectacular French Tudor exudes a casual elegance and the comfort of the islands. Inside you will find gleaming wood floors, an elegant fireplace, crown molding and spacious bright rooms. The kitchen is fully equipped with a new ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Honolulu
Welcome to your tranquil oasis at the exclusive Lalani Lau'eki Estate at the Four Seasons Hualalai Resort. This private estate is nestled between exquisite and expansive ocean and Hualalai mountain views—perfect for beautiful Hawaiian ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Prices start at $150,000/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.
Banyan House is Oahu’s most luxurious vacation rental near the foot of Diamond Head. The exquisite home features...
- 8 bedroom
- 10 bathroom
- Sleeps 16
Hawaii> Honolulu
Our home was updated during the last half of 2021 and in early 2022 with new cabinets, countertops, appliances, sinks, faucets and dimmable recessed lighting in kitchen, baths, wet-bar and laundry; all new showers and a free-standing tub in the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Lahaina
