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 = 'hawi' OR postalCode = 'hawi' OR region like '%|hawi|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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
Hawaii> Hawi
Extra clean fully furnished 920 Square feet two bedroom upstairs apartment
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Hawi
James Cottage is a historic home that has been completely restored for our guests to enjoy. Located with in The Historic Puakea Ranch, your bungalow was once home to the paniolos who lived and worked at Puakea 100 years ago.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hawi
Charming 1930's Authentic "Hawaiian Paniolo" bungalow on our private ocean view ranch. Less than a mile from the shore line with 180º sweeping ocean, green pastures and Maui views.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> 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
Hawaii> Hawi
Welcome to Hawi Hideaway,
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Hawi
🌺 Enjoy 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.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hawi
Always In Season Farmstead Suite sits in a park-like setting with majestic views. This Eco-friendly upcountry property will take your breath away. Spacious, Country Living, Hosts with Aloha.
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Hawi
PRIVATE OCEAN VIEW VACATION RENTAL. A Whale Crossing Vacation Rental is nestled on the coast of North Kohala, near Upolu Point, on the northwest tip of the Big Island of Hawaii. Enjoy the breathtaking ocean view from this cozy country cottage ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> 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
Hawaii> Hawi
PRIVATE OCEAN VIEW VACATION RENTAL. Awhalecrossing Cabin Vacation Rental is nestled on the coast of North Kohala, near Upolu Point, on the northwest tip of the Big Island. Enjoy the breathtaking views from this cozy country little house, ...
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Hawi
This guest room is located on the second floor of an exquisite turn-of-the-century mansion on the northwest tip of the Big Island and features a queen size bed and is perfect for a solo traveler. Incredible ocean breezes flowing into this room ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hawi
Located inside a historical bed and breakfast inn is this fabulous, spacious, executive suite with mini refrigerator, walk in closet and a large bathroom featuring oversized tub, walk-in tiled shower and fresh ocean breeze.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Hawi
An exquisite private, detached suite with a luxurious king size bed and located behind the majestic, turn-of-the-century mansion on the northwest tip of the Big Island. Escape to a private, serene environment with Incredible ocean breezes and ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> 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
Hawaii> Hawi
