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 (`headline` like '%Puamana%') AND sleeps>=1 order by rating desc LIMIT 0, 20
*STAY UP TO DATE WITH THE LATEST GUIDELINES FOR TRAVEL TO MAUI HERE:
https://www.hawaiitourismauthority.org/
BEAUTIFULLY REMODELED ocean front home at Puamana, great ocean views that soothe the spirit. Experience a feeling of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Rest, relax and rejuvenate from your Kauai adventures and activities at our totally remodeled, all new Puamana townhome. Perfectly situated in Princeville, with easy access to Hanalei, Anini, Haena and local restaurants, farmer's markets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Welcome to Hale Blue Hawaii at Puamana 17D! Ideal for a family beach vacation, this spacious, two-story villa offers top-notch features throughout as well as plenty of on-site resort amenities located just steps from your door. Start your days ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
This townhome was spared by the August 2023 Lahaina fire, but part of our Puamana community was burned and as of January 2024 we are still waiting for water and sewer service to be restored before the property will be inhabitable again. Owner ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Come and relax in our newly renovated, two bed, two bath condominium on Kauai's famous North Shore. Adventures abound so rest assured our place will be your spot to rejuvenate. Surrounded by cool coastal towns like Kilauea and Hanalei, Puamana ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Indulge in a vacation like no other at Puamana #14B, and let the enchanting beauty of Kauai be your backdrop for an unforgettable Hawaiian escape. This two-story, 2BR/2BA condo with mini split AC, WiFi and a chef's kitchen has tranquil ocean, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Our condo is a total remodel end unit with lots of windows and green lawn all around and convenient to shopping beaches restaurants hiking golf literally everything you wish to do.
This is our personal home to share with our guests. We have ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
This home is located down an adorable street within the historic Puamana community. Its location is excellent being steps away from one of the 3 pools. Enjoy the relaxing lanai which includes a new outdoor sectional couch to relax on. Puamana is...
- 1 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
The townhouse has:
2 bedrooms upstairs (Master bedroom/king size - Guest bedroom/queen size)
2 full bathrooms upstairs
1 powder room downstairs
1 full kitchen
1 living room with a queen size sofa bed
1 dining room area
1 ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Experience an unforgettable retreat in Princeville's north shore community. This upgraded condo, a favorite in Puamana, is centrally located just minutes from world-class beaches, forest trails, and tropical landscapes. Just steps away from the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
A Tropical Paradise...
The unique neighborhood of Puamana will take you back in time. The quiet streets are perfectly landscaped. Banyan trees sprawl around the clubhouse and plumeria trees shower the streets with their flowers. Depending on ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Imagine your own little private, gated sanctuary, on the water … a stunningly lush paradise that is all yours. The warm, scented breeze … flowers … palm trees … exotic flora and fauna. It feels like you’re far from civilization as you ...
- 2 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Lahaina
A highly desirable and rare end-unit on the ocean side of the building, this rental unit is a one bedroom, two bath, single-level, one story townhome. With a southern exposure, it is very light; bright; and sunny throughout the beautiful Maui ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Lahaina
Our home was lost in the Lahania firestorm, August 2023. It will be at least a year until we know what the future holds. Please check back in 2024 for any updates.
Partial Ocean view condominium/townhouse, ground floor with loft, tennis, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Aloha and welcome to our posting! Our beautiful and newly remodeled 3 bedroom, 2.5 bathroom house in Puamana was designed to have plenty of space for up to eight guests to enjoy our favorite place on earth – Maui!
The quick pitch: The ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
Follow us on Instagram:
@alohastays
@simplymauitravel
E Komo Mai to the Newly Appointed Ocean Front Bella Mare! This is the first time this home has been offered.
Feel the welcoming spirt of the ocean and of Hawaii the ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Lahaina
PUAMANA PREMIER 2 BEDROOM GARDEN UNIT 153-6, 3 BATHROOMS & SLEEPS 9.
Less than 200 FT to the ocean. It is one of the largest two-bedroom units with 1,368 sq ft. Puamana is a private gated community of 32 acres of lush tropical gardens, open ...
- 2 bedroom
- 3 bathroom
- Sleeps 9
Hawaii> Lahaina
Follow us on Instagram:
@alohastays
@simplymauitravel
**If this home is booked, please check out our other 3 bedroom home in Napili/Kapalua**
**VRBO Number 480505**
E Komo Mai to the Aloha Cottage at Puamana! We have ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Puamana 254-1 is a beautifully remodeled oceanfront 3-bedroom 2.5-bath town home with an exceptional corner location on the sunny south shore of Puamana. The coconut tree filled lawn adds to the tropical charm of this magical oceanfront setting....
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Beautifully New Updated Decor 1-bedroom 2-bath - Lahaina's Best Value | | 74-1 offers a wonderful single-level floor plan with kitchen, dining and living room all oriented to the 28 foot wide lanai and garden view. Updated with new furnishings ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
