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 '%Retreat%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Puako Pau Hana is a great place to escape from all your cares! Located in the heart of Puako, you are literally just a few steps to the most beautiful little beach in Puako!
Puako is known for it's magical old fashioned neighborhood feel and...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Come join us and stay in our newly remodeled, contemporary one bedroom/ one bath unit in a quiet cul-de-sac neighborhood across from Three Tables Beach. The new kitchen has all new appliances and a quartz island for cooking and dining. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Haleiwa
UALANI RETREAT
Fully Licensed & Permitted
Locally Owned & Operated B&B
Permit # BBPH 2012/0002
...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Haiku
Welcome to The Palms at Wailea – Your Island Paradise Awaits!
Located in the prestigious Wailea resort community, The Palms at Wailea condo complex stands out as one of Maui's most elegant destinations. Our 1-bedroom, 2-bathroom condo...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kihei
Kuuipo Cottage is a clean, secluded and relaxing slice of paradise. The cottage is surrounded by Hawaii native Ohia trees and Hapu'u ferns and includes your own private orchid garden which you can enjoy from the newly expanded patio. You won't ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Mountain View
Summary:
Tranquil Beachfront Estate w/ 5 Luxurious Private Full Suites & Exotic Fruit Grove. Full amenities. Our luxury Beachfront estate has 4 beachfront one-bedroom suites and a garden view two-bedroom Ohana. Each suite has a private ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Spectacular private 92 foot long (x 3-1/2 to 5 ft deep) infinity edge pool overlooking the 15th hole of the Mauna Lani North Golf Course, along with a 12 person hot tub that waterfalls into the pool below. This is truly one of the nicest pools ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> KAMUELA
Discover beautiful Kauai from this spacious 1 bedroom vacation home (built in 2018) in the heart of sunny Poipu. Located in Poipu Beach Estates, the newest upscale neighborhood bordering the Kiahuna Golf Course, that is just minutes from the...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
Our Tropical Retreat Guest House is a luxurious 1 bedroom, 1 full bath resort-style guest house located in one of Hilo’s premier subdivisions. Our guest house is truly a tropical paradise with a very spacious living area. The guest house opens...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Hilo
Picture Shangri La with rolling mists and sunshine over the canopy of Ohia Lehua with the tip of Mauna Kea poking through the valleys off in the near distance...
Ahu Lani Retreat Center is an educational and retreat facility located at ...
- 11 bedroom
- 5 bathroom
- Sleeps 20
Hawaii> Honokaa
IF YOU'RE PINING FOR A LUSH, LAZY, LANGUID ESCAPE...A BAREFOOT TRANQUILITY...A BALMY, PALMY, SEA-SCENTED ADVENTURE...LOOK NO FURTHER!!
Rated #1 Because...
1. AS SEEN ON TV - The Discovery Channel recently filmed our home, Hale Mana, ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kilauea
This beautifully decorated deluxe three bedroom condo is situated in the heart of Waikoloa Beach Resort. With no units above or below, the condo offers the most in privacy. It is newly professionally decorated in Ocean colors with granite and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
Hawaii State Tax ID: 148-388-6592-01. Hawai'i County IDs: STVR 19-345557, NUC 19-293
Aloha! E komo mai. Welcome to Fern Cottage. Located across from Volcano Village, and just minutes from Hawaii Volcanoes National Park, we invite you to ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Volcano
RiverSong is nestled in two tropical acres alongside Kilauea River. The river sings below and waterfalls splash beneath your private, secluded lanai. Our gardens abound with color from red ginger, gardenia, orange and yellow heliconia, purple ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kilauea
Summer Special! Perfect Couple Retreat! Enjoy Sweeping Ocean Views High End Top Floor Remodel
TOP FLR OCEAN VIEW BEAUTIFUL REMODEL, ENHANCED CLEANING
https://www.youtube.com/watch?v=Or2Innjn2LE
Enjoy Sweeping Ocean ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Wailea
Surround yourself with the lush scenery of Maui Breeze Retreat at Ho'olei, a tastefully decorated two-story villa with spectacular views of the Pacific Ocean. Your exquisite island retreat in Wailea offers spacious, well-appointed living ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kihei
One of only seven Hualalai Penthouse Villas. Upper-level Villa featuring single level interior with over 4,200 sq feet of living and lanai space. Hear the sounds of the Pacific Ocean while enjoying the beautiful close unobstructed ocean and ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Local Owners! Renovated and upgraded! Steps from Kamaole Beaches! The perfect spot to relax and enjoy all that Maui has to offer.
Everything you need to make your stay comfortable and convenient is here.
•Split Air Conditioning ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Nestled on the slopes of Hualalai, the Keauhou Hideaway is a private retreat on 2.7 acres of lush tropical vegetation. The 800' elevation affords a spectacular view of the Kona coastline and ensures warm, sunny days and cool nights. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Brought to you by Paradise in Hawaii Luxury Homes.
Welcome to Pacific Vista Retreat, a part of our Hawaiian Luxury Collection. Pacific Vista Retreat in Kahakai Estates is a wonderful 5-bedroom home, minutes away from downtown Kailua-Kona....
- 5 bedroom
- 4 bathroom
- Sleeps 11
Hawaii> Kailua-Kona
There’s no better way to experience Oahu’s south shore than from this charming Honolulu vacation rental home. Ideal for a romantic retreat, this 1-bedroom, 1-bathroom apartment is just a short stroll from the scenic Koko Crater trail system,...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Honolulu
Length of stay: MINIMUM 30 NIGHTS. Monthly rental. Property damage insurance required.
Kai Lani is a quiet gated community located in Ko Olina, a 640 acre oceanfront resort on West Oahu, the dry leeward side of the island. Ko Olina ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
Discover Mountain Thunder Retreat, a tranquil and serene get-away nestled on the cool slopes of dormant volcano, Hualalai.
This vintage-style home is located on a 20-acre organic coffee farm. We are a quick 20-minute drive from the ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
The Royal Palms Estate is perfect for multi-generational families, friends, and groups of fewer than 12 people. Unfortunately, we can not have large weddings or parties.
The Royal Palms has 13 Acres Of Exotic Fruit Trees, Tropical ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Ninole
Sunset Beach Retreat is only a one block walk to the famous Sunset Beach and Backyards on the North Shore of Oahu.
This spacious upstairs 3 bedroom, 2 bath home has a fully equipped kitchen, stocked linens, two lanais, large fenced in ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Haleiwa
PLEASE NOTE: YOU ARE RENTING FROM A PRIVATE OWNER. ALL ACCESS INFORMATION WILL BE SENT TO YOU DIRECTLY FROM THE OWNERS. OUR ONLY ASSOCIATION WITH THE MAUI BANYAN RESERVATION DESK IS FOR THEM TO ISSUE PARKING PASSES AND POOL CODES, THEY DO NOT ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Please read listing description for full details on this lovely property! We also have another listing at the Keauhou Surf and Racquet Club if this property isn't available: Vrbo 1620967.
We are an approved Short Term Vacation Rental ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Immerse yourself in the tranquil sounds of the waves, our oceanfront condo offers the ultimate beach experience! Just steps away, you can enjoy wonderful swimming and snorkeling, and even spot turtles on the beach. Take in the beautiful ocean ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Ekahi Ocean Studio is a studio perfectly located in the lower village of Wailea Ekahi (#8C), a lush-landscaped paradise, just steps from the oceanfront pool and to world-renowned Keawakapu beach. This studio is perfect for couples seeking a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
We have remodeled our spacious one bedroom condo with amenities to be our home away from home. We invite you to make it yours while you enjoy the finest beaches and sunny weather of Kihei. Keep in touch with free wireless ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Hale Kainalu is a sweet getaway at the end of Lanikai with exclusive secluded beach used by only 5 houses within a gated drive. This beautiful cottage is literally right on the beach & overlooks the crystal clear, blue and green hued water. Wake...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua
Modern luxury right on the Waikoloa Village Golf Course! Ocean and sunset views from the lanai and almost every room!! Our Hawaiian home was completely renovated in 2017, with a brand new kitchen featuring custom maple cabinets, quartz ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Waikoloa Village
Luxurious Tropical Retreat - Unit 31D is truly "one-of-kind"!
Ideally located overlooking the lower Ekahi Village, Unit 31D provides our guests with the best of everything Maui has to offer: spectacular views, privacy and a short stroll ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Wailea
E komo mai meke aloha kakou! Welcome to Maui Vista 1225.
For the sixteen years we have been associated with vrbo, we have used this space to tell you about the features and benefits of our condo. We have wanted you to know what others ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Special Permit: (SUP2) SUP220170018
Permit: BBMP20170006
Relax in the luxury and privacy of this quiet neighborhood that is still convenient to modern amenities and restaurants. This location is a little slice of heaven, a different ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kula
Ohana Retreat is the perfect spot for a large family to enjoy the beauty of Hawaii. We offer a spacious and functional island-style home with 8 spacious bedroom suites. Each with private bath, wet bar, refrigerator and private deck. Enjoy ...
- 8 bedroom
- 9 bathroom
- Sleeps 18
Hawaii> Kailua-Kona
This luxury two-story townhome features four bedrooms (including two primary suites) and 4.5 baths, with its own private pool and spa, in an exclusive gated community. Only about 500 yards from two exceptional resorts and two of the most ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kamuela
Hualalai Hale is your personal resort home located in the exclusive Hualalai Resort, Hawaii on the Big Island. Offering four beautifully decorated bedroom suites, each with their own bath, this single-story grand residence on 1.5 acres is an ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Kailua Kona
About Grand Champions Villas 128 (Hawaii Tax ID: 104-909-0048-01):
This condo is not listed on Craigslist. Reservations are not accepted from Craigslist.
What is in my great Grand Champions Condo?
CENTRAL AIR CONDITIONING - 3 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Waialua Retreat is your home away from home! Located on a quiet residential cul-de-sac, you will truly relax as you dine on the lanai of this spectacular, architecturally designed beachfront home, while taking in amazing sunrises and sunsets. ...
- 4 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Waialua
Highly cleaned and sanitized for your vacation. Ask about our AlohaSafeStay program.
If you have a stay that qualifies for housekeeping services, traditional housekeeping service will be performed every 3rd day. This includes refreshing...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Makena, Maui
Thoughtfully designed with your comfort in mind, Hale Nui will exceed your expectations! After a long, arduous flight, each guest will feel embraced upon entering this exquisite, spacious home and everyone will immediately marvel at the ...
- 5 bedroom
- 6 bathroom
- Sleeps 10
Hawaii> Lahaina
This newly constructed split-level home features an open floor plan, only the finest finishes, and its own pool and jacuzzi, along with access to the KaMilo pools, fitness center, and other recreational facilities. Luxuriously appointed, it ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kamuela
*Please note: Maximum stay is 12 nights. Mahalo.
*Please note: Bamboo Retreat is not suitable for children under age 10. Mahalo.
Located at 1400' elevation in the lush foothills above Hilo, just 15 min from downtown, this cloud ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hilo
Escape to a lush and secluded mountain retreat Kona coffee farm, just minutes away from sun-kissed beaches, Kailua and Captain Cook. Enjoy picturesque sunsets and stargaze at night. Relax in peaceful moments surrounded by full ocean views, ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Holualoa
Kolea 14 is a spectacular five-bedroom oceanfront estate that offers true luxury in the most exclusive section of the world-renowned Waikoloa Resort. The home offers spacious luxury with a solar heated pool, a spa with a waterfall, and a large ...
- 5 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Waikoloa
Beautifully remodeled ground floor corner unit in Building 20, with Pacific Ocean views! From the front Lanai, there is a broad green lawn, lined with palm trees that take you to one of the most beautiful swimming and surfing beaches in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Koloa
This exceptionally large, bright, spacious floor plan has three beautifully decorated bedrooms and three full bathrooms. Located in the beautiful KoOlina gated community with a location that overlooks a lush greenbelt and has quick access to the...
- 4 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Kapolei
Aloha and Welcome to Kihei Beach Ho’okipa, located directly on the beautiful six mile white sands of Sugar Beach in Kihei town on the island of Maui.We are centrally situated on Maui’s south shore. Special rates are available let us know ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Kiahuna Plantation #2115: Tropical décor delights in this 690sf rare deluxe ground-floor suite. You and your friends or family can easily access the grounds from your ground-floor balcony and walk to world-famous Poipu Beach which according to ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> KOLOA
