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,
rewritten_description, custom_url, ai_description,
(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 = 'Volcano' OR postalCode = 'Volcano' OR region like '%|Volcano|%') AND sleeps>=1 AND (
(postalCode NOT IN('96701','96706','96707','96712','6717','96730','96731','96734','96744','96782','96786','96789','96792','96826','96795','96797','96813','96814','96815','96816','96817','96818','96819','96821','96822','96825')
AND city NOT IN('Aiea','Ewa Beach','Haleiwa','Hauula','Hawaii Kai','Honolulu','Kaaawa','Kahala','Kahuku','Kailua','Kaneohe','Kapolei','Laie','Manoa','Makaha','Mililani','Nanakuli','Pearl City','Wahiawa','Waialua','Waianae','Waikiki','Waimanalo','Waipahu','Lanikai','Maili')
) OR (registration_number like '%TA-%' or registration_number like '%TA %')
) order by rating desc LIMIT 50, 50
Our Romantic Volcano Artist Cottage is nestled on a serene 3-acre historic garden estate just steps from Hawaii Volcanoes National Park. This charming, recently restored one-bedroom redwood cottage offers a cozy retreat with a queen-sized bed, a fully equipped kitchenette stocked with fresh island fruit, yogurt, gourmet coffees, and teas.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Escape to our secluded Bamboo Treehouse on Hawaii's Big Island, perched 14 feet above the ground amidst lush jungle. Designed for couples, this cozy retreat features a luxurious queen bed with premium mattresses and soft bamboo sheets, ensuring a restful night's sleep.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Set out on an adventure and return to a peaceful retreat just five minutes from Volcano National Park. As you step through the red front door of Kuono at Volcano, you'll be greeted by 14-foot floor-to-ceiling windows that seamlessly connect you to the lush outdoors.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Experience the tranquility of Hale Hapu'u, a charming two-bedroom, three-bath rainforest retreat set on five lush acres in Volcano Village. This family-friendly home combines a lodge-like ambiance with modern comforts, making it an ideal base for exploring Hawai'i Volcanoes National Park and surrounding attractions.
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Volcano
Escape to a charming, meticulously clean private guest apartment nestled in a tranquil rainforest neighborhood, just 5 minutes from Volcanoes National Park. This romantic retreat offers a perfect blend of comfort, art, and nature. The spacious master bedroom features a plush queen memory foam mattress and a private bathroom, ensuring restful nights.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Set on a peaceful street in historic Volcano Village, Hiʻiaka Guesthouse offers a private, romantic retreat on the second floor of a beautiful custom home surrounded by lush old-growth ʻōhiʻa forest. The property provides a unique treehouse-like experience with close contact to nature and stunning views of the tropical landscape.
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
5-bathroom Volcano home offers a perfect blend of comfort, privacy, and natural beauty. The property boasts an outdoor grilling and dining area, ideal for enjoying meals al fresco, along with a six-person hot tub nestled under a custom gazebo for year-round relaxation.
5 bedroom
3 bathroom
Sleeps 10
Hawaii> Volcano
Experience the unique beauty of Volcano, Hawaii, with a stay in this charming private cottage. Perched just across the street from Volcanoes National Park, the property offers breathtaking views of the Kilauea volcano, which is currently active as of January 2023, allowing you to observe the glowing plume from the comfort of your accommodation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Welcome to Eco Kō Mai Retreat, a tranquil haven nestled in the peaceful town of Volcano, Hawaii. This retreat offers a perfect blend of serenity, spirituality, and modern convenience, making it an ideal escape for those seeking relaxation and connection with nature.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Volcano
Tucked away on 3 peaceful acres of native Hawaiian rainforest, this charming cedar cabin is just 2 miles from Volcanoes National Park, making it an ideal retreat for families and friends seeking tranquility and adventure.
3 bedroom
3 bathroom
Sleeps 7
Hawaii> Volcano
Escape to the charming Volcano Cottage, a cozy retreat nestled at the end of a peaceful street on the Big Island of Hawaii. This well-appointed home offers a perfect blend of comfort and convenience, just minutes from the iconic Kilauea volcano and Volcanoes National Park.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Discover tranquility at Hale Palila, a spacious A-frame cottage nestled on a lush golf course on Hawaii's Big Island. Located just 5 minutes from the entrance to Volcano National Park, this retreat offers a perfect blend of comfort and natural beauty.
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Volcano
Welcome to The Pacifica Inn, a charming retreat nestled in Volcano on Hawaii's Big Island. Surrounded by lush Hapu�u trees and serenaded by endemic birds and Coqui frogs, this property offers a peaceful escape into Hawaii's natural beauty. The Inn features two comfortable rooms: the Volcano Room upstairs and the Gecko Room downstairs, each designed to provide a cozy, authentic Hawaiian experience without luxury, just genuine island hospitality.
2 bedroom
2 bathroom
Sleeps 9
Hawaii> Volcano
Welcome to this unique cedar-style home nestled in the lush Volcano rainforest. As you arrive, you're greeted by a calming, vibrant rainforest that instantly relaxes you. The top-floor open-concept room offers breathtaking views of the surrounding nature, giving you a true treehouse feel with complete tranquility.
1 bedroom
1 bathroom
Sleeps 7
Hawaii> Volcano
Experience a tranquil escape at Kahu 'lo - Guardian Hawk, a beautifully situated home on the Big Island. Nestled among native Ohia trees, Kahili ginger, and Hapu'u ferns, this property offers a unique blend of natural beauty and convenient access to Hawaii Volcanoes National Park, just 10 minutes away.
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Volcano
Welcome to Hale Nene, your ideal one-bedroom getaway nestled in the heart of Volcano Village. Designed for romance, creativity, or relaxation, this cozy home comfortably hosts 2-4 guests with a brand new queen-sized bed and a fold-out futon couch in the living room.
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Volcano
Discover the beauty of Volcano from this charming 2-bedroom lodge, nestled in a tranquil setting. The retreat offers two comfortable queen beds, ensuring restful nights. Stay connected with WiFi and enjoy the cozy warmth provided by the heating system.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Volcano
This peaceful mountain hideaway offers a perfect escape into Hawaii’s natural beauty. Situated in a tranquil rural area close to Hawai'i Volcanoes National Park, the private home provides a serene retreat with modern comforts. Enjoy covered lanais with dining and lounging areas, perfect for relaxing or entertaining, alongside a cozy fire pit for cooler evenings.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Poliahu is a newly constructed, modern-style bungalow designed to offer a luxurious and refined experience, comparable to a high-end hotel. The interior features a plush king-sized bed, radiant heated floors for comfort, and contemporary lighting that enhances the elegant ambiance.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Volcano
Enjoy a relaxing stay at Tree Fern Escape, a charming and comfortable home just minutes from Volcano National Park. This cozy 3-bedroom, 2-bath property offers a perfect blend of comfort and convenience, making it an ideal choice for families or couples seeking a memorable Hawaiian getaway.
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Volcano
Want to try an unforgettable experience where you can shed shame, build body confidence and connect with nature like never before? Give it a try at our body positive, non judgmental, family friendly wellness retreat center.
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Volcano
Welcome to Ohia Lehua Hale, located just 6 minutes from Hawaii Volcanoes National Park.
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Volcano
