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, 50
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. Everything has been cleaned up. All services have been restored as of July 2024, one pool is open at ...
- 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
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
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**
or
3 BEDROOM IN PUAMANA
**VRBO NUMBER ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
This beautifully renovated oceanfront townhome offers the ultimate Maui experience, where the ocean is truly your backyard. Newly furnished and fully equipped, this home provides everything you need for a relaxing vacation in Puamana. No need to...
- 3 bedroom
- 3 bathroom
- Sleeps 6
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
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 that wasn’t harmed by the Lahaina fire. The coconut tree-filled lawn adds to the tropical ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Puamana, a 30-acre oceanfront, low-density gated community, sustained some damage from the fire. Despite the recent fire, this charming three-bedroom, 2.5-bath townhome continues to offer spectacular views, breathtaking sunsets, and the soothing...
- 3 bedroom
- 3 bathroom
- Sleeps 6
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
Step into an elegance and lifestyle so mesmerizing you may never want to return home! Enjoy your adventure and make many treasured memories at our three-story charming townhouse that really is anyone’s dream of ocean front living so close to ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
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.
The August 2023 Lahaina Fire spared our unit. Maui County has cleared this ...
- 3 bedroom
- 3 bathroom
- Sleeps 9
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
This three-bedroom oceanfront unit offers a great value for guests who prioritize location over amenities, especially as the area recovers from the Lahaina fire. The new owners are planning a much-needed remodel, which will take place once the ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Lahaina
Unit 105-1 is a charming 3-bedroom townhome, perfect for families or couples traveling together. This townhome offers partial ocean views and easy access to the beach and surf. Unfortunately, the historic clubhouse and some amenities were lost ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
In the wake of the Lahaina fire, Puamana continues to embody the spirit of Hawaiian resilience, even as the surrounding landscape has changed. While some amenities were lost, this coastal community remains a beautiful and peaceful ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Puamana 177-1 is like staying at a Hawaiian home. Living area is 1250 SQ FT with 220 SQ FT of front and back lanai’s and a added on room to the rear of unit that provides a 3rd bedroom at night. It has an 800 SF backyard and 300 SF front yard ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Lahaina
Discover paradise in this elegant Puamana 185-4, offering expansive ocean views from every room. The open-plan layout creates a seamless flow between living spaces, while the downstairs lanai beckons with its tropical ambiance and stunning ...
- 3 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Lahaina
Aloha to Maui Dream family vacation in a 4 bedroom 3 ½ bath spectacular ocean front condo at 226-1 Pualei Dr. in Puamana, Lahaina. This idyllic escape with unparalleled views and natural beauty is about 100 feet from the ocean. A beautiful ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
Puamana! Our quiet one bedroom is at the end of a Cul de sac in a gated community with dedicated parking. We've recently remodeled with all new granite countertops, new appliances, vanities & tiled showers in both bathrooms and a new king size ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Puamana 80-3 is a two-bedroom, two-bathroom townhome ideally located just a short stroll from Puamana’s private ocean access, swimming pool, and playground located on the secluded south side of Puamana, which was unharmed by the Lahaina fire. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
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
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
We invite you to enjoy this beautifully updated 2 bedroom, 3 bathroom condo in the pristine Puamana community. Tucked away amidst 30 tropical acres, with a beautiful corridor ocean view from the lanai and upstairs bedroom and bathroom and ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Puamana 80-2 is a beautiful three-bedroom two bath two story townhome that enjoys a spacious floor plan and new furniture and finishes.
The expansive back lanai opens to a large open greenbelt offering the perfect place to lounge in the sun, ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Let your Maui adventure begin at Puamana in a tropical oasis next to the beautiful blue Pacific Ocean. Puamana is a perfect setting for your family's vacation or a group getaway. As you enter the townhome you are greeted by the sights and sounds...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
Puamana 62-4 is a beautifully decorated 3 bedroom, 2 bath tropical townhome that comfortably sleeps 6. This poolside home features an open concept floor plan with large lanai for outdoor living. Just a short walk to the pool or beach this home ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Puamana 272-3 presents a unique opportunity to experience oceanfront living at its finest. Relax on your spacious lanai, just 50 feet from the water’s edge. Here, you can grill, dine, enjoy the ever-changing ocean views, watch surfers riding ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
Welcome to Puamana Retreat by Gather
Discover the ultimate oceanfront retreat, where breathtaking views and endless adventure come together in perfect harmony. Situated on the channel between Maui and Lanai, this property offers unmatched...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
