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 (`propertyName` like '%Pili Mai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Relax and enjoy modern living at Kauai’s newest townhome community, Pili Mai! This condo, also known as Nani Ka Hale, is located on the second and third floor of the building. It overlooks the lush greens of Kiahuna Golf Course and offers ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Gorgeous Pili Mai 6-L Distant Ocean and Mountain Views Surrounded by a Beautiful Golf Course - Sleeps 8 !
This Brand New Townhome in Poipu is a 3 Bedroom/3 Bathroom at Pili Mai at Poipu - the Newest Complex on Kauai. Pili Mai’s ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Brand new spacious 3-bedroom, 3-bathroom condo features two master bedrooms with En Suite bathrooms, upgraded amenities such as A/C, bamboo flooring, granite countertops, a Bosch dishwasher, washer, and dryer and much more!
Pili Mai 14A ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
This single-story, second-floor condo opens to the scenic Kiahuna Golf Course, with ocean views beyond. Its newly constructed floor plan features central air conditioning and three spacious bedrooms, each with a television and its own full bath....
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
This is a 3 story building with our 2 story unit located on second and third floors with entry to condo up a flight of stairs. We have four bedrooms, 2 on the second story (the condo's main floor) which are Master suites. While upstairs on the ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Spacious 4-bedroom, 3.5-bathroom condo features two master bedrooms with En Suite bathrooms, upgraded amenities such as A/C, Marbau flooring, granite countertops and Bosch appliances, a washer, dryer and much more!
The sparkling new Pili ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
A stay at Hale Konani in the Pili Mai resort community has everything you need for the perfect Hawaiian vacation. This exceptional well-appointed three-bedroom, three-bath 1,500 sq. ft. condominium with central air conditioning is located on ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Pack your golf clubs and get ready to enjoy modern Hawaiian living in this exclusive Poipu community. Located on the renowned Kiahuna Golf Course, Pili Mai 4C is an exceptional, newly-constructed three-bedroom, two and a half bath townhome ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Pack your golf clubs and get ready to enjoy modern Hawaiian living in this exclusive Poipu community. Located on the renowned Kiahuna Golf Course, Pili Mai 4B is an exceptional, newly-constructed three-bedroom, two and a half bath townhome ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Experience a vibrant, brand new condo in the upscale Pili Mai complex. This gorgeous condo, built in 2020, features an abundance of light through the expansive windows and modern clean-line design. Enjoy the spacious balconies (lanai) with the ...
- 3 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
Nestled among a championship golf course, Pili Mai is set in an ideal location on the island of Kauai’s south shore in Poipu Beach. This is Kauai's newest resort development and has a choice of two, three, and four bedroom luxury ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Elegantly beautiful, Pili Mai 12F engulfs you in pure luxury. As you come upon the entry, you immediately notice the comfortable seating that allows you to take in the warm sunshine and tropical flora. Warm earthy tones of the flooring, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
