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
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
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
Luxury Condo. Main floor, one level condo. 3 bedroom, 3 Bath, Sleeps 8. Central A/C. Granite countertops, bamboo and travertine flooring, stainless steel appliances. Surrounded by the Kiahuna Golf Course, with views of the mountains. Central ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
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
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
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
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
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
Pili Mai 7H
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Ready to experience a new Hawaii? Surrounded by the beautiful Kiahuna Golf Course ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
E komo mai, welcome to The Laulea Pili Mai 13H!
Experience Poipu's newest luxury condos on Kauai's south shore. The Laulea #13H at Pili Mai is set in an ideal location on the ninth fairway of the renowned Kiahuna Golf Course in Poipu, fringed...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Two Bedrooms, 1 King bed, 1 queen bed, 1 queen murphy bed, and 1 queen sofa sleeper, 2 bathrooms, nicely upgraded, washer and dryer in unit, lanai, golf course view, great complex amenities.
If Sunny Poipu is your vacation choice, 6G at ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Make yourself at home in this luxury three bedroom, three bathroom condo located on Kauai’s sunny south side! A generously open-floor plan allows for abundant natural lighting and air flow throughout the entire home.
The spacious living...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Brand new, two-bedroom townhome located at the Pili Mai resort in Poipu, Kaua'i with AC, Pool, and Fitness Center. Enjoy Ha'upu Mountain views and be entranced in beautiful sunrise views surrounded in serene Kaua'i harmony!
The sparkling ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Poipu
Welcome to Pili Mai, the modern and stylish abode that is sure to fill your next Hawaiian vacation with comfort and relaxation. Situated on the scenic Kiahuna Golf Course on the south side of Kauai with picturesque panoramic mountain views and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
PILI MAI 15F AT KOLOA KAI VACATION RENTALS
Get ready to embark on a new and exciting Hawaiian experience at Pili Mai! Nestled amidst the stunning Kiahuna Golf Course, this modern condo offers breathtaking views of the mountains and ocean ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Elegant design meets luxurious comfort in this beautifully bright, airy 3BR/2.5 BA townhome in the acclaimed Pili Mai at Poipu vacation-resort complex. Inside and out, the artistically color-coordinated furnishings are a rhapsody in oceanic ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Stunning!! This elegant fully-furnished Villa 4-I in Poipu's newest condo development Pili Mai, is situated in an ideal location within walking distance to shops, restaurants, beaches, and Kiahuna Golf Course. This villa is located on the 2nd ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Pili Mai located, along a championship golf course, is an ideal location on the island of Kauai's south shore in Poipu Beach. This luxurious 4 bedroom 3.5 bathroom corner end unit is the perfect spot for touring the island and relaxing after a ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Pili Mai 13B is your perfect Island home… With views of rolling greens and distant ocean views from the lanai and bedroom windows. While the entrancing Ha’upu Mountains greet you with Aloha. Pili Mai is an exclusive resort that provides a ...
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Koloa
Welcome to Pili Mai 12A by Gather, the perfect three-bedroom, three-bathroom condo in Koloa. This ground-floor condo, alongside the picturesque Kiahuna Golf Course, offers garden views and a newly designed layout with central air conditioning. ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
