select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Puamana%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Hawaiian living at its best awaits you and your family in this beautiful 2 bedroom 2 bath townhome. This home has been recently updated featuring new flooring, newly painted, central A/C installed, new cooking appliances & kitchenware, new ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Lahaina
New listing! Incredible location within the highly sought after gated community! 24-3 is located 75 feet from the ocean! Grab your morning coffee and take a few steps to check the surf or catch a glimpse of Maui's part time residents - humpback ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> Lahaina
New listing! Incredible location within the highly sought after gated community! This home is made for kids of all ages and adults young at heart! 24-4 is located 75 feet from the ocean! Grab your morning coffee and take a few steps to check the...
- 3 bedroom
- 4 bathroom
- Sleeps 9
HI> Lahaina
Premium Oceanfront Location in Gated Community! Never been rented before - family home for generations now available to share with others!
Perfect Oceanfront Location - 4 Bedroom 3.5 Bath - Sleeps 8
Puamana is a 30 acre gated community that ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
HI> Lahaina
Totally Rebuilt Puamana Townhome - Great Ocean Views - Our beautiful beach-style home exudes a feeling of joy, happiness and aloha. This gorgeous unit offers a high-end granite kitchen, three top quality bathrooms, 2 ocean view master suites, ...
- 3 bedroom
- 3 bathroom
- Sleeps 7
HI> 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
HI> Lahaina
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 the...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Lahaina
225-2 is a 3 bedroom, 3.5 bath oceanfront unit in a very private setting in Puamana. This unit was completely remodeled in 2012 and is perfect for the discerning traveler looking for a quiet retreat. It is located on the north end of the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
HI> Lahaina
Treat yourself to a wonderful vacation on the water in one of Maui’s most relaxing Hawaiian settings. Enjoy "Puamana Old Hawaii" with family and friends in this updated oceanfront vacation home on the ocean's edge of West Maui. This ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
HI> Lahaina
A TROPICAL PARADISE!
Perfect Oceanfront Location - 2 Bedroom 3 Bath with Loft! Sleeps 6
Puamana is a 30 acre gated community that includes lots of open green spaces and almost a mile of coastline with a great beach nestled right in the ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Lahaina
Partial Ocean view condominium/townhouse, ground floor with loft, tennis, three pools. 400 ft from the ocean. You will have swimming, snorkeling, and surfing with the Pacific Ocean right out front. Puamana is a tropical oasis where the sun ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Lahaina
Puamana at it's best!
This beautiful 2 bedroom 2.5 bath townhouse was remodeled last year and outfitted with top of the line furnishings and appliances. With contemporary design elements and spacious outdoor living areas you have the best of ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
HI> Lahaina
Aloha Future Guests,
In regards to COVID: We are serious about cleaning and keeping you safe!
Cleaning: We have stepped up our cleaning in response to COVID-19. The following areas have enhanced cleaning: High-touch surfaces such as... ...
- 3 bedroom
- 3 bathroom
- Sleeps 10
HI> Lahaina
We are following strict guidelines for cleaning and disinfecting the house for your safety and comfort. You can relax and renew with confidence in this quiet environment without contacting staff or other guests if you prefer not to. Self check...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> 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 the spirit. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Lahaina
