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 = '96778' OR postalCode = '96778' OR region like '%|96778|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Private cottage, gorgeous 720 sq ft one bedroom, full kitchen, bathroom, spacious living/dining area. Covered lanai with gas grill, patio seating, covered gazebo area with hammocks and hanging chairs. Conveniently located right off famous ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
Nice, comfortable home on large lot with spacious lanai and fenced back yard. Use our home as a base for your Hawaiian adventure. Cook meals in our new kitchen with fresh, delicious and healthy fruits and veggies available at local markets. ...
- 4 bedroom
- 2 bathroom
- Sleeps 10
Hawaii> Pahoa
Spectacular ocean views await you from every level of this professionally decorated vacation home. Located on the Eastern side of the Big Island, The Jewel Box is a traditional Hawaiian bungalow remodeled with your comfort and luxury in mind ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Spacious Custom built newly remodeled 3bd 2ba home with 2,144+ sqft of living space that has been beautifully remodeled/renovated for enjoying Island living.
This home features vaulted ceilings, natural light with French Doors opening on ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Come celebrate your Life & new beginnings at the acclaimed Phoenix House!
Featured in countless media around the world, this epic, off-grid tiny home creation at the feet of an active Volcano has won the hearts of countless ...
- 2 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Pahoa
Experience an unforgettable stay at the Magical Lava Temple, a 3BR/1BA off-grid sanctuary located 4 miles from the base of the world’s most active volcano. Surrounded by lava fields and breathtaking views, this open-air loft offers a unique ...
- 4 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Pahoa
We are THE perfect luxury listing on the east side of the Big Island! A quick distance to black sand beaches, waterfalls, the volcano and charming Hilo town.
ALOHA! Welcome to Hale Kakahi or Hawaiian for “unique, outstanding, ...
- 4 bedroom
- 4 bathroom
- Sleeps 9
Hawaii> Pahoa
Enchanted cottage with outdoor clawfoot bathtub and private yard. Airy open space design with veranda and garden seating. Additional bath house with rainhead shower and toilet. Listen to the sounds of the forest and birds, and the waves at ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Pahoa
Hale Aikalani
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The open waters of the Pacific Ocean are right in your backyard at this modern ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Pahoa
Aloha & welcome to our lush 11 acre OFF GRiD Paradise!
This home operates completely off the grid meaning we gain 100% of our power from solar energy, our water is catchment (rainwater), and we have propane to power any gas devices. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
There is a black sand beach nearby to explore (Kehena), and a lava tube hidden in a kipuka surrounded by a lava field, and geothermally heated ponds, forest trails, farmers’ markets, yoga classes, chocolate and mushroom farms, dances, ...
- 4 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Pahoa
Welcome to our stunning 5-bedroom vacation home in the charming town of Pahoa! As soon as you step inside, you'll be captivated by the inviting atmosphere and all the comforts of home. Enjoy the convenience of free WiFi, a washing machine and ...
- 5 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Pahoa
We're delighted to announce a new year and another acre of native Hawaiian forest added to the retreat.
Immerse yourself in an exceptional once-in-a-lifetime all 5-star experience on the Eastern tip of the Big Island, in this newly ...
- 2 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Pahoa
You'll feel well-cared for and cozy in this new two-bedroom home. High-end design and luxury finishes bring art, comfort and beauty indoors; wrapping you in a jewel box of tranquility. The large open deck offers plenty of outdoor living space. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Right off 130 surrounded by lush jungle. We are ready to help you have the best adventure this side of Hawaii! Tours available by Host living in guest house on site. We just added a full size 7 person 58 jet Hot tub!! (Shared with host but we ...
- 5 bedroom
- 2 bathroom
- Sleeps 12
Hawaii> Pahoa
Tropical Dolphin B | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Discover a dreamy tropical retreat at this two-bedroom, ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Experience the Big Island while staying in this unique converted Hawaii trolley! The Hawaii trolleys are used to shuttle visitors from all over the world around the sights and sounds of many of our beautiful islands. Now you can enjoy a stay in ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
Puni Mauka A | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Located less than five miles from Pāhoa for convenient ...
- 4 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Welcome to our beautifully designed Lava House. Situated on 2 private acres of pristine beauty and tranquility. A long winding driveway leads you to our very private home which backs up to the most spectacular lava field. Our home is surrounded ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
WE ARE LOCATED CLOSE TO THE ONLY NATURAL HOT SPRINGS IN HAWAII! Come bathe in lava heated spring ponds to rejuvenate and explore THE untouched Beauty of Hawaii. THIS is truly a RETREAT. If touristy Hawaii is not for you, and quiet connection ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
Puni Mauka | 31- Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Located less than three miles from the ocean, and just a few ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Pahoa
Welcome to Hakuma House, a cozy off-grid retreat with stunning ocean and volcano views. Tucked away in Kalapana, where the wind, sky, and Pacific Ocean meet, this 2-story home + cottage offers a unique escape from the everyday. Relax on the ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Prime location for a paradise packed Big Island experience! Enjoy peaceful, relaxing nights in Seaview Estates, a quiet place for 1, a romantic getaway or perfect for the entire family. This luxurious Ohana unit has a private entrance, one ...
- 0 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Pahoa
High quality rental with Hawaiian Style home feeling. Upgraded kitchen. Fresh and clean. Relax and enjoy.
PLEASE NOTE: Our prices include TAT/GET tax -
Perfect for 1-5 people. This is a fully furnished guest 2 bedroom/1 bath house w/ ...
- 2 bedroom
- 0 bathroom
- Sleeps 5
Hawaii> Pahoa
Aloha Valley Shores | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Enjoy relaxing in a cheerful home filled with light, ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Pahoa
Kūaloli is for those wanting to escape the crowds & tourist hustle while immersing themselves in a vibrant and tranquil retreat. Nestled away from crowds and resorts, our spacious 3800 sq. ft. lofted home beckons with open arms, offering a ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Zuki House is a modern and elegant vacation rental close to the ocean. Enjoy our ocean view balcony upstairs outside the main bedroom suite. Our high ceilings and bright, modern furnishings give you comfort and style to enjoy throughout your ...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Pahoa
Located 5 minute drive to the ocean, 30 minutes from Hilo airport. Cottage is ideal for a couples, however if you have a family and wish to stay, we can provide air mattresses for the kids for up to two more people. Queen size bed in unit, WIFI,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
Hale Hawaii Ohana | 31-Day Minimum
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Ideal for long-term stays (30-night minimum), this ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Pahoa
Bright and beautiful, meticulously maintained, 2 bedroom plus loft, 2 bath custom home with hot tub and ocean views located in Kalapana Seaview Estates, Pahoa, Big Island Hawaii. Located just off the historic Red Road near Kehena Beach and ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
A fun room featuring full size bunk beds for adults able to sleep 4 and private bathroom. Quiet neighborhood with the sounds of nature & the smell of flowers. Walk the wooden sidewalks of Historic & colorful Pahoa Village...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Prime location for a Paradise Big Island experience! Enjoy peaceful, relaxing nights in Kalapana’s Seaview Estates at the Tropical Garden Getaway. This luxurious ohana unit has a private entrance, deluxe queen bed in one room with morning ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Pahoa
Welcome to the forest of Hawaii on Big Island!
The serene sounds and sights await you in this 2nd story home which is accessible by ramp! The home sleeps 4 in two comfortable king size beds accompanied by closets, fans, bedside table, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
Hear sounds of crashing waves at this 1 bed, 1 bath unit, about 0.9 mile walk to Kehena Black Sand Beach! Cook meals with fresh foods from local farmer markets and neighborhood fruit stands. Bask in the glory of a private outdoor shower with ...
- 1 bedroom
- 4 bathroom
- Sleeps 2
Hawaii> Pahoa
Kitsune Cottage: Your Oceanfront Oasis in the Heart of Seaview.
Welcome to Kitsune Cottage, where the beauty of the Hawaiian islands meets the comfort of home. Nestled on the Hilo side of the Big Island, our cottage offers an unforgettable ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
Escape to the beauty of Pāhoa and experience the perfect balance of luxury and location at this 3-bedroom, 2-bath vacation rental. Situated 5 miles from Puna Kai Shopping Center, this home is a dream come true for adventurers and relaxation ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Pahoa
Enjoy a peaceful stay at this 1-bedroom, off grid house in Pahoa. Nestled on the lava fields, this welcoming property offers a marvelous retreat for guests. With its enchanting ambiance, you'll feel right at home from the moment you step inside....
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
Reset and relax at our natural jungle paradise. Taste and take in the beautiful tropical fruit landscape during the day and some of the clearest star gazing at night. The rose cottage named for its ever blooming roses, provides a beautiful ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
Welcome to Popa’a Palms, our tropical oasis in the quiet coastal community of Hawaiian Beaches, near the village of Pahoa! Just two blocks away, stroll the breathtaking cliffs or lounge at the park and take in the colorful evening skies over ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Pahoa
Enter paradise-The cottage is well-equipped and clean with a kitchenette, full bath and a spacious living area. There is a cozy queen bed in the main bedroom featuring its own private lanai. Wake up to an incredible sunrise and ocean view from ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
Take it easy at this unique and tranquil getaway. A verdant oasis waiting for you to enjoy. Stunning views of tropical beauty from all windows of the home makes the place feel like you're in the trees. Only a short walk to the famous Kehena ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Pahoa
Studio with oceanview, full kitchen and bathroom, queen size bed, plus small couch for one child or small adult. Perfect for couples. The property offers 1 mile private rainforest trail, pool, steam vents, two steam vent hot springs and private ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Pahoa
The ultimate eco-retreat. Timeless design for indoor-outdoor relaxation, dining and sleeping. Experience ocean views and crashing waves from wide-open bifold doors. Walk nextdoor to Kehena black sand beach, or drive the Red Road to Hawaii's only...
- 2 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Pahoa
This adorable cottage in Pāhoa is the perfect home base for your trip. The cozy bedroom features a comfortable queen bed, and the living room offers an additional bunk bed for extra guests. With amenities like WiFi, a washing machine, and an ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
The Dolphin Cottage is a nice 1 Br/1 Bath Cottage w/ comfortable queen bed and a double sized futon in the living room. It can sleep up to 3 people. If necessary, we can add an aerobed.
The cottage is located Across the Street from Kehena ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kehena
Beautiful garden cottage in very private setting on several acres with fruit trees. Walking distance to Mermaid Ponds for swimming and just relaxing by the ocean. The unit has a full kitchen, bath and tub, fridge, stove and everything else ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Pahoa
We call it the Octopus house - and sometimes Pulpo or Tako for short.
This is a tiny off grid house! Quaint and comfortable with power, hot and cold running water and a full bathroom, that s 1500 feet from the ocean cliffs, a short walk ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kalapana
Stan and I had a property in Kapoho listed with VRBO for four and a half years. Mama J's Pond House was taken by Pele, the Hawaiian goddess of fire on June 5th, 2018 during the Kilauea eruption. During our time of being hosts, we learned a lot...
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Pahoa
🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺🌺
Charming Studio Home
Sleeps up to 4
Has a large , fully gated yard ,
Free parking on premises
Gorgeous
Outdoor space with Large Picnic table , Barbecue area ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
The Big Island Guest House is a private two-bedroom home including:
-bedroom with a new queen bed
-second bedroom with a queen bed
-full bathroom
-modern kitchen
-large covered lanai (deck or porch) with a bbq
-beautiful ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Pahoa
