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 '%Polo Beach Club%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Location, location, location…. Polo Beach is the premiere condominium that sits at the south end of the Wailea Beach walk, next to the Fairmont Kei Lani resort. Our unit is on the sunny south side of the building, overlooking the pool area and...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Wailea
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
Polo Beach Club 407 is a premier oceanfront vacation rental located in the upscale resort area of Wailea. This two-bedroom, two-bathroom condo offers guests a luxurious and comfortable stay with sweeping ocean views and direct beach access out ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Polo Beach Club 409 is a luxurious oceanfront vacation rental located in the world-renowned beach vacation destination of Wailea. This two-bedroom, two-bathroom condo offers a premium beachfront experience with direct access to Polo Beach out ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Welcome to our little piece of paradise! We were so excited to be able to purchase our condo a few years back and are now ready to share it with guests. Polo Beach Club is truly a magical place, a hidden gem that sits at the south end of the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Indulge in the luxurious Polo Beach Club 707, a "Maui Modern Masterpiece" situated on the pristine Ke One o Polo Beach in Wailea. This exquisite condo offers breathtaking ocean and sunset views from its prime location. Step into Suite 707's ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Makena, Maui
Indulge in the luxurious allure of Polo Beach Club 606, an stunning condominium property boasting unparalleled oceanfront and multi-island vistas. Set amidst the captivating Makena coastline and the world-renowned Wailea, this enchanting ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Makena, Maui
Welcome to Polo Beach #602 at Polo Beach Club, a luxury condominium property that offers breathtaking prime-oceanfront views and captivating vistas of the surrounding islands. Situated between the stunning Makena coastline and the world-renowned...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Makena, Maui
Polo Beach Club 608 is an exquisite two-bedroom, two-bath residence designed to accommodate up to six guests. With its island-inspired decor, this condo strikes a perfect balance between comfort and contemporary style, making it an ideal getaway...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Welcome to Polo Beach Club 301, a stunning third-floor corner residence that offers breathtaking views of Polo Beach. This spacious unit exemplifies the indoor-outdoor lifestyle that Hawaii is famous for, making it perfect for both entertaining ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Polo #406 is a beautiful, comfortable unit overlooking the ocean. It offers breathtaking views of Molokini, West Maui, Kahoolawe, and Lanai. The unobstructed sunsets are absolutely stunning!
While staying at the Polo Beach Club, guests ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailea-Makena
Magnificent ocean views from Polo Beach Club 705, with direct beach access! Enjoy the serenity and privacy of one of the best locations in Wailea.
As you enter Polo Beach Club 705 you are greeted by the lovely furnishings and decor. You ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Book 5 nights and enjoy the 6th night free! Plus, get complimentary late check-out. Valid for new reservations from 8/19/24 - 11/21/2024.
Polo Beach Club 205! Direct ocean front views! Located in the highly sought-after beachfront stack....
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Polo Beach Club 408 is a stunningly renovated vacation rental that offers breathtaking ocean views, making it an ideal getaway. Guests can relax on the lanai, enjoying the sight and sound of turquoise waters gently lapping the shores of the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Polo Beach Club 102 is a luxurious oceanfront vacation rental located in the prestigious Wailea area of Maui. This ground-floor condo offers an exceptional beachfront experience, situated just steps away from the pristine Polo Beach.
This ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
Step into a light and bright modern oasis and enjoy breathtaking ocean front views from the spacious lanai. Large windows and sliding glass doors bring in lots of light and the modern coastal decor offers a tranquil setting inside this sprawling...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Polo Beach Club 706 is an ideal home base for your tropical getaway. Make your Maui experience one to be remembered. Whether you’re celebrating with family, vacationing with friends, or simply seeking a peaceful and private alternative to a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailea-Makena
