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, 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
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 ...
- 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
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
Please note that each bedroom is equipped with Portable AC units.
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 ...
- 3 bedroom
- 2 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
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
Our Moku Hale, or “Island Home,” is peacefully nestled on the Kiahuna Golf Course with stunning views from each private lanai; the front lanai has a picturesque view of the lush mountains and golf course, while the large back lanai has an ...
- 4 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
Perfectly located in sunny Poipu, Pili Mai is home to Kauai's newest resort development. With both, unobstructed mountain and fairway views from the kitchen, great room, courtyard, and lanais, this villa provides everything you could dream of ...
- 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. It is a rare find indeed with 4 bedrooms, 3.5 baths, accommodating up to 8 people! This well-appointed home offers a classic and ...
- 4 bedroom
- 4 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.
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
4 Bedrooms, 3.5 Baths, Sleeps 8
Enjoy the Garden Island of Kauai while working remotely. Contact us for discounts on extended and monthly stays.
The beautiful golf course views will pull you into this gorgeous new condo built in 2019. This...
- 4 bedroom
- 4 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
Welcome to Pili Mai 12L, fondly named The Gordon Isle! Designed with your ohana (family) in mind, this 4-bedroom, 3.5-bath luxury villa provides all the amenities your heart desires. Allow your cares to drift away as you prepare for a ...
- 4 bedroom
- 3 bathroom
- Sleeps 14
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 ...
- 4 bedroom
- 3 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
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
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
Escape to Pili Mai 5G, a stunning two-bedroom retreat in the heart of Poipu. Just minutes from Poipu Beach, Kiahuna Golf Course, and historic Koloa town, this elegant condo offers the perfect blend of modern comfort and island charm. Enjoy ...
- 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
