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, 50
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 ...
- 3 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> Wailea
Welcome to your tropical escape at Polo Beach Club, our cherished slice of paradise! Having fallen in love with this magical location years ago, we are thrilled to be able to share our beautiful condo with you. Nestled at the tranquil south end ...
- 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
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
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 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
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
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailea
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
Polo Beach Club 205! Direct ocean front views! Located in the highly sought-after beachfront stack. Enjoy incredible views of Polo Beach, Molokini Crater, Kaho'olawe, and the West Maui Mountains!
Polo Beach Club 205! Direct ocean front ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Makena, Maui
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
We are proud to present this luxurious 2BR/2BA oceanfront residence at Polo Beach Club–newly launched on 10/25/24 to start taking reservations for check-ins on 1/6/25!
Boasting a stunning Wailea oceanfront location and a lavish ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
