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
- 4 bathroom
- Sleeps 4
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...
- 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
Aloha and welcome to your Maui Oceanfront Dream home. Puamana 150-1 is a spectacular front-line 5-bedroom, 5 full bathroom, corner two-story townhome just 35 feet from the water’s edge. Enjoy magical sunsets, surfers rolling in on the waves,...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Lahaina
Summary:
4 Bedroom / 3.5 Bathroom - Sleeps 8
E Komo Mai (welcome) to Puamana 240-2! This property is one of only 10 four bedroom properties in all of the highly sought after gated Puamana Community! Perfectly situated with just grass ...
- 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, ...
- 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
Aloha Beach Home is a beautiful 3-bedroom 3-bath, ocean-view, two-story townhome that has been completely renovated and newly re-furnished showcasing a modern tropical beach house decor. The downstairs kitchen, living room, and lanai open to an ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
We are a local family that owns and manages this property and we live full time in Lahaina. By booking this home you are supporting a local family. Our Puamana community was affected by the 2023 wildfires and we lost many of our homes and ...
- 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
***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 ...
- 3 bedroom
- 4 bathroom
- Sleeps 9
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
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
Our townhouse is thoroughly cleaned and disinfected between each guest to ensure that you will be arriving at a safe and clean environment for your vacation.
The unit is by the fire zone and not affected by the fire. The grassy area ...
- 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
