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 (city = 'hana' OR postalCode = 'hana' OR region like '%|hana|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Entabeni Cottage, sits above the Road to Hana overlooking the Pacific Ocean on the north shore of Maui, Hawaii.
0 bedroom
2 bathroom
Sleeps 2
Hawaii> Hana
The view of the ocean and coastal pasture makes guests exclaim "wow" as they enter the dutch doors of this cozy cottage.
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Hana's Heaven - a charming 2 bedroom cottage complete with full kitchen, living room and 3 lanais. Wake every day to the sunrise and ocean expanse. Off the beaten track, this home away from home is quiet and serene, the perfect place to relax ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hana
Maui County Permit # STHA 2016/0006
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
The Hamoa Bay Bungalow is HANA MAU'Is most charming vacation hide-away. Balinese inspired, private, with partial ocean views, secluded and romantic.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Discover paradise in Heavenly Hana, Maui, and surround yourself with the most breathtaking views in all of Hana.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hana
Popolana” – Unit 2 – is our one and only oceanfront studio condominium. Just 100′ off the water, this lovely unit has warm tropical decor with bamboo walls and rattan furniture, a queen-size bed, granite counter tops, hardwood floors and...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Ka’ahumanu – Unit 5 – is a top-floor, deluxe oceanfront 2-bedroom/2-bath unit with a king bed in master bedroom, a queen bed in the 2nd bedroom and a queen hide-a-bed in the living room. Beautiful kitchen with porcelain tile floors, ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Hana
Maui County Permit # STHA 2016/0006
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hana
Aloha!
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Watch the video to get a feel for our home!
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Hana
Exuding to the comfort and ambiance of an authentic Hawaiian beach house, the Manini cottage offers a coveted, secluded location. Nestled oceanfront on Hana Bay's edge, it features an open living space and a covered ocean deck with unobstructed ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
OCEANFRONT SPLENDOUR IN PERFECT HARMONY WITH NATURE
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hana
Ala Aina Ocean Vista is an unique, 100% solar-powered private dwelling with front and back private entrances situated on a former banana plantation. Nestled in an exotic lush tropical setting, our accommodation promises peace and quiet and ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hana
Hale Nanea is close to everything Hana, yet far enough away that it's private and peaceful at night. The cottage is well equipped and very comfortable with custom Bali furnishings.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hana
Maui County Permit #STHA T2012/0007
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hana
Come and enjoy the natural beauty of Hana, the essence of Hawaii in an undeveloped paradise. Enjoy spectacular waterfalls, pools, rugged shorelines, and some of the best beaches on the island just outside your door.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Hana
Hana Homestead is our eco friendly home in the heart of Hana town. The spectacular ocean views, large decks, new hot tub and inferred sauna and modern rustic elegance of this unique home is just the beginning. A 3 minute stroll will ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Hana
The house sits in the middle of the 4 acres surrounded by lawn, flowers, fruit trees and forest. Guests are welcome to harvest the fruit and flowers. It is very quiet, and on clear nights it is like a bowl of stars turned upside down on our ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hana
SPECIALS
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hana
