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, 30
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
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
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
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
*STAY UP TO DATE WITH THE LATEST GUIDELINES FOR TRAVEL TO MAUI HERE:
https://www.hawaiitourismauthority.org/news/alerts/covid-19-novel-coronavirus/
BEAUTIFULLY REMODELED ocean front home at Puamana, great ocean views that soothe ...
- 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
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
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
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Our Princeville vacation rentals at Puamana offer space, privacy, and a very convenient location. Updated, comfortable, and spacious, these north shore Kauai ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
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
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 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
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Completely remodeled with gorgeous upgraded kitchen and master bathroom, new flooring throughout, all new furnishings. You'll love the ocean view from Puamana 16B. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
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
Our Princeville vacation rentals at Puamana offer space, privacy, and a very convenient location. Beautifully remodeled, comfortable, and spacious, this north shore Kauai vacation rental has a great location! Puamana 25B is the ideal ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Elegant Puamana home with top quality finishes, expansive open floor plan and ocean views from every room. The downstairs lanai doors open to a scenic greenbelt lined with coconut palms, the perfect setting to enjoy dining, grilling and the ...
- 2 bedroom
- 3 bathroom
- Sleeps 5
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
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
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Puamana 9C: Updated Princeville condo with AC in the master bedroom, and so many extras provided for your comfort and convenience! A spacious 1550sf of living space...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
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.
Soft waves sounding in the background, multicolor spectacular sunset, birds ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
Enhanced COVID cleaning and disinfecting procedures for every check-in.
Puamana 21D - walk to Anini beach, shopping, dining. This Princeville vacation rental is a great choice for a family looking for plenty of space at an affordable ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
'PEOPLE'S CHOICE' Aloha Cabana
END OF SUMMER SALE. BOOK NOW FOR AUGUST AND RECEIVE A DISCOUNTED RATE!
A unique Puamana private unit with TROPICAL VIEWS and expansive greenbelts! Ideal for outdoor living and beach days steps from ...
- 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
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