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, rewritten_description,
(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, 50
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
- 3 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
- 4 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
Pili Mai is the south shore's newest development and adjacent to Poipu's ocean view Kiahuna Champion Golf Course.
Your dream vacation is waiting at this second story, 3 bedroom, 3 bath spacious condo. The lanai gives plenty of outdoor
- 4 bedroom
- 3 bathroom
- Sleeps 8
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
- 3 bedroom
- 3 bathroom
- Sleeps 6
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
- 4 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
The Resort:
Pili Mai was built in 2019 and features plantation-style design, cobblestone drives, tropical gardens, a large swimming pool, a children's pool, spa, and an air-conditioned fitness room. It is nestled between Kiahuna Golf Club and
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
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
Modern Luxury in the Heart of Poipu
Discover the perfect blend of island charm and contemporary comfort at Pili Mai 15F, a spacious two-bedroom, two-and-a-half-bathroom condo overlooking
- 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
Located on the beautiful Kiahuna Golf Course, this exceptional, newly constructed two-bedroom, two-bath condo accommodates up to six people. A two-story, second-floor unit, Pili Mai 15G provides the ideal location for a honeymoon, anniversary
- 3 bedroom
- 2 bathroom
- Sleeps 6
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
Summary:
Experience the enchanting allure of Kauai in our 2-story, 3-bedroom, 2.5-bath condominium nestled along the scenic Kiahuna Golf Course in Poipu. Enjoy the breath-taking mountain views that surround this meticulously designed space.
- 4 bedroom
- 3 bathroom
- Sleeps 6
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
Welcome to Pili Mai by Gather
Discover your perfect island retreat at Pili Mai by Gather, a modern and stylish 3-bedroom, 3-bathroom condo located on the scenic Kiahuna Golf Course in sunny Poipu on Kauai’s south shore. With picturesque
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Enjoy modern Hawaii living at Pili Mai 11I. Pack your golf clubs and get ready to enjoy modern Hawaii living in this exclusive Poipu community. Located on the 9th hole of the beautiful Kiahuna Golf Course, this exceptionally beautiful,
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
This single-story, ground-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
Able to comfortably accommodate up to 10 guests, this rare four-bedroom brand new Pili Mai condo with 3.5 baths is adjacent to the breathtaking Kiahuna Golf Course and minutes to beaches. It features mountain views, central air conditioning,
- 4 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Koloa
Located in the Poipu Resort area, Pili Mai 7F is situated on 35 acres of landscaped lawns and gardens filled with tropical flowers. Enjoy an open air gathering place with barbecues, air conditioned work out facility, and a large swimming pool,
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Poipu
Pili Mai is the south shore's newest development and adjacent to Poipu's ocean view Kiahuna Champion Golf Course.
Spacious living is found in this beautiful 4 bedroom, 3.5 bath retreat with plenty of room to spread out and call home. Perfect
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Three bedrooms, 1 King bed, 1 Queen bed, 2 Twin beds, 3 full bathrooms, upgrades, washer and dryer in unit, ground floor lanai, single car garage, near BBQ's, wonderful views, great complex amenities.
Relax and enjoy the expansive fairway
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Pili Mai is the south shore's newest development and adjacent to Poipu's ocean view Kiahuna Champion Golf Course. The perfect place to get away for family and friends. This spacious condo provides the backdrop for creating amazing memories you
- 4 bedroom
- 3 bathroom
- Sleeps 8
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
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Poipu
FAMILY FUN at Pili Mai, 2br + Game room (bumper pool, poker table, classic video game console, board games, piano, masks and snorkels, beach chairs, croquet, and small yard for the kids to play) 2.5 bath, sleeps 6, no stairs, back yard lawn.
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Our "Ha'upu Hale" is a beautiful, 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
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> koloa
Pili Mai is one of Poipu's newest complexes with a great location and spacious living! You will not be disappointed as you step into this 3 bedroom condo and discover the great features such as travertine, granite, wood flooring, plantation
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Pili Mai is the south shore's newest development and adjacent to Poipu's ocean view Kiahuna Champion Golf Course. Close to beaches, shopping and excursions.
If sunny Poipu is your vacation destination, this spacious unit will welcome you
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Two bedrooms plus loft, 1 king bed, 1 queen, plus 2 twins in the loft, 2 full bathrooms, nice upgrades, washer and dryer in unit, lanai space, incredible views, great complex amenities.
This brand new 2 Bedroom plus loft, 2 Baths condo is
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
PILI MAI 15H AT KOLOA KAI VACATION RENTALS
Modern Luxury Meets Island Charm
Discover the perfect blend of contemporary comfort and tropical tranquility at Pili Mai 15H, a stunning two-bedroom, two-and-a-half-bathroom condo set
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Enjoy this brand-new, two-level suite with plantation-style architecture. Pili Mai 11B is a spacious 4-bedroom, 3.5-bathroom condo featuring two master bedrooms with ensuite bathrooms, upgraded amenities such as A/C, Marbau flooring, granite
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Koloa
Enjoy this brand-new, two-level suite with plantation-style architecture. Pili Mai 5L is a spacious 4-bedroom, 3.5-bathroom condo featuring two master bedrooms with ensuite bathrooms, upgraded amenities such as A/C, Marbau flooring, granite
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> POIPU
Pili Mai 10F
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Ideal for a family beach vacation, this sunny and inviting, golf course-adjacent
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Poipu
Welcome to Pili Mai 7J by Gather, a ground-floor, single-story condo that presents an ideal blend of luxury and convenience in the heart of Koloa, HI. With its serene setting on the Kiahuna Golf Course and views stretching to the ocean, this
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Koloa
Pili Mai 7E is a three-bedroom, two-level suite with plantation-style architecture. Newly furnished and decked with bamboo floors, Koa cabinets, a gourmet kitchen and central A/C, this villa is a fresh find. Revel in all the bells and whistles
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Grand town home living in airy 3 bedroom home with high-end furnishing and finishes throughout. This is the largest, and most distinct 3br floor plan in the complex - the Kalewa. This plan boasts 1600sf living space, 278 feet of Lenai.
- 3 bedroom
- 3 bathroom
- Sleeps 7
Hawaii> Koloa
