select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) 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, 15
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
HI> 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 condominiums....
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Koloa
New 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
HI> 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 condominiums....
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> 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
HI> 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 condominiums....
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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
HI> 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
HI> Koloa
Enjoy stunning ocean and golf course views from our new and luxurious condo fabulously located in the heart of Poipu. The quaint lanai offers peace and serenity while sipping your morning coffee or relaxing with cocktails after a long day of ...
- 2 bedroom
- 2 bathroom
- Sleeps 8
HI> 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 condominiums....
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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 condominiums....
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> 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 with views ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Koloa
Two bedrooms, 1 King bed, 2 Twin beds and a sleeper sofa, 2 full bathrooms plus 1 half bath, newly built ground floor property, washer and dryer in unit, large lanai space, single car garage, garden views, great complex amenities.
PARKING: ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> 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 condominiums....
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> 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 condominiums....
- 2 bedroom
- 3 bathroom
- Sleeps 4
HI> Koloa
