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 = 'Hawi' OR postalCode = 'Hawi' OR region like '%|Hawi|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This suite is located on the second floor of an exquisite turn-of-the-century mansion on the northwest tip of the Big Island. Incredible ocean breezes flowing in filtered by the dancing sway of 100 year old pines and a modest view of the ocean ...
1 bedroom
1 bathroom
Sleeps 2
HI> Hawi
'The Cowboy House' is a very charming & historic home built by Plantation working families at the turn of the century.(1870''s!) This home offers three bedrooms and sleeps 6. It has been lovely restored and is a very special place to vacation ...
3 bedroom
2 bathroom
Sleeps 6
HI> Hawi
Head to the northwest tip of the Big Island and land in one of the most exquisite island countryside abodes. Isolated in a tiny, island village is the Aloha Boutique Hotel which sports over 6 spacious suites including a lovely, saltwater pool ...
1 bedroom
1 bathroom
Sleeps 4
HI> Hawi
Hawi Haven! Come away for a time of rest and rejuvenation at our lovingly restored plantation cottage. Our comfortable home consists of three bedrooms, two and a half baths and is hidden away on a quiet lot, complete with many tropical flowers ...
3 bedroom
3 bathroom
Sleeps 6
Hi> Hawi
Thank you for showing interest in Kohala Lodge. This Beautiful 2400 square ft craftsman-style Paniolo (cowboy) retreat is your's for you and your guest only. The retreat sets on 3.5 acres located at the Northwest coast of the Big Island above ...
1 bedroom
2 bathroom
Sleeps 2
HI> Hawi
Extra clean fully furnished 920 Square feet two bedroom upstairs apartment
450 square feet covered lanai
When we say fully furnished we mean fully furnished
Coffee maker, cooking utensils, linens, towels, toilet paper, paper towels, alarm ...
2 bedroom
1 bathroom
Sleeps 4
HI> Hawi
Wonderful spacious, modern cottage with all the tropical Hawaii charm located on a one+ acre macadamia nut farm near Hawi Town. The cottage has a large open living area with high ceilings, bamboo floors and tasteful decor. Enjoy cooking in the...
1 bedroom
1 bathroom
Sleeps 2
HI> Hawi
Fantastic VIEWS, Spacious, Country Living, Hosts with Aloha
Private, Ocean, and Maui Views on a three-acre farmstead
Executive Suite for the price of a studio * Enjoy the spirit of Aloha in this private setting with a fruit orchard and gorgeous...
1 bedroom
1 bathroom
Sleeps 3
HI> Hawi
Come stay and relax at this quartzite stone-sided custom built home on a 10 acre private estate located in historic Hawi on the Big Island of Hawaii. The house is built with loving care and attention to details by the owners. Our home is perfect...
3 bedroom
3 bathroom
Sleeps 8
HI> Hawi
🌺 Enjoys the spirit of Aloha in this private setting with a fruit orchard and gorgeous ornamental and edible gardens accompanied by a magnificent view of the ocean and the nearby island of Maui.
There are only two units on the property, ...
1 bedroom
1 bathroom
Sleeps 2
HI> Hawi
Our historic bungalows are stocked with everything you need to enjoy your home away from home. Think of The Ranch as your very own country estate with high speed Wi-Fi and panoramic ocean vistas. Puakea Ranch is proud to offer an organic on site...
2 bedroom
2 bathroom
Sleeps 6
HI> Hawi
Located in the quaint, island village town, Hawi, this special country cottage offers a fabulous place to rest and relax away from the hustle and bustle of the city. Including, a 22x27 pool is a few steps away for a refreshing dip on warm, sunny...
1 bedroom
1 bathroom
Sleeps 3
HI> Hawi
Located in the quaint country village of Hawi is a fabulous suite with queen size bed,, private space on the lanai and own bathroom. One of 6 suites in a grande estate built in the late 1800's, this charming country mansion offers a unique ...
1 bedroom
1 bathroom
Sleeps 2
HI> Hawi
