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
Aloha E, welcome home. This beautifully remodeled oceanfront home at Puamana is like no other getaway. Incredible ocean views that soothe the spirit and calm the mind. Experience a feeling of joy (hau'oli), peace (maluhia) and love (aloha) here....
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
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
Welcome to Hale Nalu, a beautifully updated townhome in the tranquil Puamana community just south of Lahaina. This 30-acre private paradise offers a peaceful retreat away from high-rise hotels and crowded tourist areas, yet remains close to ...
- 2 bedroom
- 2 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
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
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 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
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
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
Located in Puamana, a 30-acre oceanfront gated community just south of Lahaina, this charming three-bedroom, 2.5-bath townhome offers a tranquil escape amidst Maui’s natural beauty. While the 2023 Lahaina fires affected parts of Puamana, ...
- 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
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 extensively remodeled oceanfront townhome is the perfect home base for exploring Maui. With a brand-new kitchen, two updated bathrooms, a powder room, new flooring, fresh paint, and all-new furniture, it offers both comfort and style. The ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
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
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
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 an 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
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
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
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 ...
- 4 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 ...
- 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
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
