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 = 'Volcano' OR postalCode = 'Volcano' OR region like '%|Volcano|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Ola'a (meaning "lively" in Hawaiian) House is a 1,700 square foot home in the heart of Volcano Village featuring vintage Hawaiian furnishings and original art. The house is owned and has been thoughtfully updated by the co-founder of Kilauea ...
2 bedroom
1 bathroom
Sleeps 5
HI> Volcano
On site for your convenience, just call Kiarah for any help you need. Her phone number can be found in our detailed manual that we provide that lists the destinations and amenities in the area, as well as resources and recommendations! This ...
1 bedroom
1 bathroom
Sleeps 2
HI> Volcano
Tutu's Place is owned by Hawaii-born Lorna Larsen-Jeyte, the former co-owner of Kilauea Lodge. The 1,000 sq. ft (92m) cottage, located in the heart of Volcano Village, 1 mile from Volcanoes National Park on a private lane with no through ...
2 bedroom
1 bathroom
Sleeps 5
HI> Volcano
The cottage is built 6 feet above ground on the front side (with 14 stairs up to the front door) and at ground level on the back side. It is all wood and glass with open beam construction. It’s tucked into mostly native forest of Hawaiian Ohia...
1 bedroom
1 bathroom
Sleeps 2
HI> Volcano
E Komo Mai Retreat Ohana
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Channel your yogi self and experience the serenity and spirituality of the ...
1 bedroom
1 bathroom
Sleeps 4
HI> Volcano
'Volcano Mist Cottage is the perfect combination of camping out and 5-Star Hotel accommodations' (from our guestbook). This deluxe, personal and very private eco-resort is nestled in the Kilauea Forest Preserve adjacent to Hawaii Volcanoes ...
1 bedroom
2 bathroom
Sleeps 3
HI> Volcano
No detail overlooked! A serene custom-designed studio retreat in the treetops of Volcano, Hawaii. Tree cottage includes a beautiful kitchen, spacious bathroom with indoor/outdoor shower, private driveway, top-level lanai off the living area for ...
0 bedroom
1 bathroom
Sleeps 2
HI> Volcano
Laukapu Forest Cottage (LFC) is a spacious studio, vacation-rental cottage handcrafted as a "Work of Heart".
LFC has a well-equipped kitchen, with refrigerator/freezer, electric range/oven, microhood, toaster, coffee and tea making supplies....
0 bedroom
1 bathroom
Sleeps 2
HI> Volcano
The spacious Enchanted Forest Studio is named for the beautiful native Hawaiian rainforest, our home is nestled in. When you see the amazing tree ferns here you will indeed know why we call it an enchanted forest experience.
The private ...
1 bedroom
1 bathroom
Sleeps 4
HI> Volcano
Come Rejuvenate in the Rainforest at the Honeycreeper room, situated on 5 lush tropical acres in the heart of Volcano Village near Hawaii Volcanoes National Park. At the Honeycreeper room, our guests can enjoy a cozy room with full walk-in ...
1 bedroom
1 bathroom
Sleeps 2
HI> Volcano
Pele's Perch is a hidden gem set on the slopes of Mauna Loa in Volcano Hawaii just 4 miles away from Hawaii Volcanoes National park.
Countless amenities include a private hot tub on the 2nd story Lanai overlooking Hawaii's beautiful rainforest ...
2 bedroom
2 bathroom
Sleeps 6
HI> Volcano
Mauna Nani is a very quiet, secluded home surrounded by ohia trees that is close to all there is to see in Volcano. 3 bedroom 2 bathroom vacation house that truly feels like home. From the tranquil garden, to the pineapple patches and bananas, ...
3 bedroom
2 bathroom
Sleeps 10
HI> Volcano
ENTIRE HOUSE RENTAL; YOUR PARTY WILL BE THE ONLY GUESTS ON THE PROPERTY.
The Volcano Rainforest Lodge is a luxury craftsman house in an ancient forest.
We are located only a few minutes from the Hawaii Volcanoes National Park main entrance ...
3 bedroom
3 bathroom
Sleeps 6
HI> Volcano
Rejuvenate in the Rainforest at Ginger room, situated on 5 lush tropical acres in the heart of Volcano Village near Hawaii Volcanoes National Park. Our guests enjoy four spacious rooms with full walk-in closets, thick curtains, hardwood floors,...
1 bedroom
1 bathroom
Sleeps 4
HI> Volcano
Enjoy peaceful Big Island Hawaii in a Contemporary Hawaiian Bed & Breakfast set in a Tropical Rainforest directly outside Hawai’i Volcanoes National Park. The custom designed & built Craftsman Cottage style home features artisan beds with fine...
1 bedroom
1 bathroom
Sleeps 2
HI> Volcano
