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 (city = 'makawao' OR postalCode = 'makawao' OR region like '%|makawao|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
THE DUKE'S COTTAGE
BBMP 2017/0003
The Duke's Cottage is a room offered at the North Shore Lookout Maui Bed and breakfast!
This very spacious ocean and pool view cottage has everything you need to truly relax and enjoy the serenity of Maui. ...
1 bedroom
1 bathroom
Sleeps 2
HI> makawao
Aloha, sorry but at this time we are not hosting to families with children younger than 16. Mahalo.
We encourage you to respectfully enjoy our property however please
no loud and boisterous parties, thank you.
This is a NO SMOKING property. ...
3 bedroom
2 bathroom
Sleeps 6
HI> Makawao
*** ALERT***
MAUI’S VRBOS ARE NOW OPEN TO TRAVELERS FROM THE MAINLAND AND ELSEWHERE. FOR LATEST INFORMATION PLEASE GO TO travel.hawaii.gov.
WE PROVIDE:
1. ENHANCED CLEANING PROCEDURES BETWEEN GUESTS...
3 bedroom
2 bathroom
Sleeps 6
HI> Makawao
Removed from the hotel / resort beachfront crowds, the 2.1 acre Banyan Tree Estate simply overflows with peace and Hawaiiana. Giant Banyan, Monkeypod and Jacaranda trees create a peaceful, shady oasis a midst the fruit trees, flowers and wide...
7 bedroom
8 bathroom
Sleeps 16
HI> Makawao
Visit hawaiicovid19.com/travels
for info.
Aloha, sorry but at this time we are not hosting to families with children younger than 16. Mahalo.
This is a NO SMOKING property. No smoking is allowed any where upon the property.
Our Jungalow ...
1 bedroom
1 bathroom
Sleeps 2
HI> Makawao
Makani Suite
BBMP 2017/0003
The Makani suite is named after the wonderful trade winds we receive here on Maui! Enjoy the breeze in the front lanai rocking chairs while sipping your cup of coffee. Though this is our economy suite it is spacious...
1 bedroom
1 bathroom
Sleeps 2
HI> Makawao
THE HOKU SUITE
BBMP 2017/0003
This Hawaiian plantation style suite is one of our largest and most spacious. It is very relaxing and romantic, especially in the evening when stars can be seen. This suite was named Hoku, meaning star in Hawaiian....
1 bedroom
1 bathroom
Sleeps 2
HI> makawao
This is a beautiful jewel of a house. It's all one level and close to shopping, activities such as hiking, biking, zip lines, farm tours, horseback riding, art school, dining and Haleakala National Park. Just an eight to ten minute drive to ...
0 bedroom
1 bathroom
Sleeps 4
HI> Makawao
Our open beamed 3 bedroom home is surrounded by trees with windows full of light and airiness to let the trade winds blow. Conveniently near Haleakala highway, shopping, beaches, airport with a Maui country lifestyle. Sleeps six comfortably. ...
3 bedroom
2 bathroom
Sleeps 6
HI> Makawao
Cozy, light-filled cottage, walking distance to Makawao town and a 15 minute drive from beaches. My upcountry cottage is surrounded by lush gardens, with fruit trees and hibiscus in a private front yard, and on the slopes of Haleakala. My ...
2 bedroom
1 bathroom
Sleeps 4
HI> Makawao
The Sacred Garden Retreat is a one of a kind find in Hawaii! A genuine log house in a beautiful jungle-garden-organic farm setting. 5 bdrms. Great for a romantic getaway fro two or group vacation. "Away from it all" but just 20 minutes to ...
5 bedroom
4 bathroom
Sleeps 10
HI> Makawao
Enjoy Sensational Panoramic views of Maui’s North Shore, Central Valley and South Shore. Our clean, cozy well-appointed Studio is quiet and private below the Main House. All amenities are High Quality: Cal King Size memory foam bed with 100% ...
1 bedroom
1 bathroom
Sleeps 2
HI> Makawao
Our Maui home is permitted with the County of Maui #STMP 2013-0001.
The upstairs has a master bedroom with a California King mattress with adjoining bathroom having a Jacuzzi tub separate from shower. The secondary bedroom also has a California ...
4 bedroom
4 bathroom
Sleeps 10
HI> Makawao
Please note, this is a permitted vacation rental property. Maui County Permit: STMP 2016/0002, Hawaii Tax Numbers: TA-042-382-9504-01, GE-042-382-9504-01.
Welcome to the perfect location! This house is very private and provides plenty of ...
3 bedroom
2 bathroom
Sleeps 8
HI> Makawao
This renovated and adorable "Gingerbread House" is a studio cottage nestled amongst beautiful trees and tropical flowers and an organic farm growing Turmeric, Ginger, Papaya, Tangerines, Avocado, Oranges, Lemons, Grapefruit, Macadamia Nuts, ...
0 bedroom
1 bathroom
Sleeps 2
HI> Makawao