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
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
- 3 bathroom
- Sleeps 4
Hawaii> Lahaina
Experience a relaxing Hawaiian vacation in one of Maui's most serene oceanfront settings. Despite the devastating fire in Lahaina, Puamana remains a haven of tranquility and beauty, where the spirit of "Old Hawaii" lives on. Unit 9-1 is located ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
*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 including our historic clubhouse. All services have been restored as of July 2024, one pool is open at Puamana, and there are no restrictions...
- 3 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Aloha! Welcome to your Maui Oceanfront Dream home, Puamana 150-1, nestled in a secure, serene setting that withstood the Lahaina Fire.
Thankfully, a tropical haven of 130 properties, including this home on the south shore of Puamana, were...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Lahaina
Summary:
4 Bedroom / 3.5 Bathroom - Sleeps 8
Enjoy a Tropical Paradise - This one of a kind property features two primary bedroom suites each with ocean views.
E Komo Mai (welcome) to Puamana 240-2! This property is one of only ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
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, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Welcome to Puamana 254-2 by Gather, exclusively offered by Stice Team, where luxury meets the rhythm of the ocean on the shores of Lahaina, Hawaii. Nestled within the prestigious Puamana community, this three-bedroom, three-bathroom oceanfront ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
Unit 254-4 is a stunning four-bedroom oceanfront end unit located on the south side of Puamana, offering breathtaking, unobstructed views of the ocean from its lanai. Whether you’re enjoying a relaxing breakfast or taking in a Maui sunset ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
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...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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 townhome 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
***Please note that Puamana 2403 does not have cable service, but rather smart TVs for your viewing enjoyment.***
***Please note that many buildings of the Puamana oceanfront community were impacted by the Lahaina fires of 2023. Although ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> Lahaina
This oceanfront unit was recently remodeled and remains an incredible retreat, even as the area recovers from the Lahaina fire. The owners have thoughtfully designed this space with modern comforts, including central air conditioning, TVs in ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
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
This two-bedroom oceanfront home in Puamana remains a serene and inviting retreat, even after the devastating fires in Lahaina. While much of the town and surrounding community suffered significant damage, this home is still standing and has ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
