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 = 'Hilo' OR postalCode = 'Hilo' OR region like '%|Hilo|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Enjoy the easy pace of life right in the heart of Hilo Town by the sea. Walk to the Farmers Market and near-by downtown area. Warm tropical air year round without feeling like a tourist. Local Old Hawaii charm awaits your stay on the lush ...
2 bedroom
2 bathroom
Sleeps 6
HI> Hilo
*PLEASE NOTE: We are following the Hawai'i State Government mandated guidelines due to Covid19. Because the mandate continues to be revised, please ask us any questions you may have about traveling to Hilo, Hawai'i during Covid19.
*New Listing ...
4 bedroom
3 bathroom
Sleeps 7
HI> Hilo
Aloha! We are Keone and Yonnell Farias. We were both born and raised here on this wonderful island. We welcome you to our beautiful re-done home and would be happy to share with you our favorite places. Located in a quiet neighborhood of Waiakea...
3 bedroom
2 bathroom
Sleeps 6
HI> Hilo
Hilo Terrace Ohana A is located in the heart of Hilo, central to many attractions such as Hawaii Volcanoes National Park, Honoli'i Surf Beach, Rainbow and Akaka Falls, Kaumana Caves, Boiling Pots, Hawaii Botanical Gardens, Liliuokalani Gardens, ...
0 bedroom
1 bathroom
Sleeps 4
HI> Hilo
For the ultimate Hilo Adventure; Look no further! Best views here, in Hilo's finest NEW Fully Air Conditioned vacation condo rental in a location that can not be beat! This beautiful 2 bd, 2 bth condo is located right in the heart of Hilo with...
2 bedroom
2 bathroom
Sleeps 4
HI> Hilo
The Hilo Bay View Estate is a gorgeous European-style home located in the exclusive area of upper Wainaku in Hilo. This stunning home has spectacular panoramic views of the ocean and Hilo town. Watch the magnificent sunrises in the morning and ...
4 bedroom
3 bathroom
Sleeps 8
HI> Hilo
You looking for a real detail authentic Hawaiian Nautical experience? Let the Whaler's Inn be your perfect spot to relax, set your sails with us and put down your anker. We are locted right in downtown Hilo just minutes away from shops, ...
3 bedroom
1 bathroom
Sleeps 5
HI> Hilo
Our one bedroom beach cottage is located on a quiet one lane road leading to the ocean. While in a quiet location it is still just a few minutes drive from downtown Hilo, supermarkets, restaurants, airport, medical centers, and scenic parks and...
1 bedroom
1 bathroom
Sleeps 2
HI> Hilo
Hale Kahoa Annex is an oceanfront one-bedroom flat facing the ocean and Hilo Bay. It is a spacious lower-level unit where one can sit and watch the sky change colors from sunrise to dusk. Waves crash all day; coqui frogs sing every night.
A ...
1 bedroom
1 bathroom
Sleeps 4
HI> Hilo
Are you looking for a relaxed, s-l-o-w vacation in old Hawaii? With a peek-a-boo ocean view, beautiful pool and cabana and a newly remodeled, bright cottage, set amidst lush green jungle foliage, you can’t go wrong! Start with the pool, bar, ...
1 bedroom
1 bathroom
Sleeps 3
HI> Hilo
A cozy 6 Bedroom 6 Bath home with 2 separate kitchens in the heart of Keaukaha. Walking distance to Onekahakaha Beach Park and Keaukaha Beach Park along with other beach access. Escape to old Hawaii....
6 bedroom
6 bathroom
Sleeps 12
HI> Hilo
Cheerful and bright, this studio is located In a quiet neighborhood overlooking Hilo. The studio occupies the first floor of 2 story home. It has a private entrance and parking on site. A full private bathroom is adjacent to the studio. Guests ...
0 bedroom
1 bathroom
Sleeps 2
HI> Hilo
Luxury Suite with Private Entrance~
We have created a peaceful, sparkling clean and private Studio Suite, including plush linens and stocked with over-the-top amenities. We provide all the basics and much more, like flashlights/lanterns, ...
1 bedroom
1 bathroom
Sleeps 4
HI> Hilo
Welcome to the Hilo Beach House - A Unique Boutique Inn located in the quiet tropical oceanfront setting of Onekahakaha Beach Park. Stay at this upscale 4 room Inn. Take Fitness Classes in the Inn's Outdoor Yoga Wellness Atelier. Rest, Relax, ...
1 bedroom
1 bathroom
Sleeps 3
HI> Hilo
*Please note: Maximum stay is 12 nights. Mahalo.
*Please note: Bamboo Retreat is not suitable for children under age 10. Mahalo.
Located at 1400' elevation in the lush foothills above Hilo, just 15 min from downtown, this cloud forest home ...
3 bedroom
2 bathroom
Sleeps 6
HI> Hilo
