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 = '96772' OR postalCode = '96772' OR region like '%|96772|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Our luxury home is located in the quiet, pristine Hawaiian countryside, 60 miles southeast of the Kona Airport and 4 miles from the quaint town of Na’alehu. It’s a pleasant 30 minute drive from Volcanoes National Park and a 15 minute drive ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Na’alehu
A craftsman-designed home in a beautiful old Hawai‘i valley is perfect for a retreat or friends traveling together. Five single beds and one queen in four bedrooms, two of which have ensuite baths. Lots of artistic details, open floor plan, ...
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Waiohinu
The ultimate Hawaii getaway awaits at this 2-bedroom, 1-bathroom vacation rental unit with sweeping views of the Pacific. Ideally located between Kona and Hilo, this home-away-from-home provides easy access to popular areas such as Hawai'i ...
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Naalehu
A rare opportunity awaits! Volcano National Park is calling you... this is Hawaii's number one attraction. From this park, you can take endless trails and see both the Halemaumau crater for the Kilauea Volcano! By far the safest viewing and ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Naalehu
Named for the spring on the land, Waiola (Water of Life) is a romantic hideaway, a peaceful retreat, an artist's getaway or a perfect stay-cation. Stone walls, taro lo`i (the garden plot that taro was grown in), a trail, and a stone house site ...
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Naalehu
. Centrally located on the Kau South coast with Emerald Green Olivine roads in a Christmas Berry Forest, without all the hustle and bustle of Kona or Hilo Located halfway between Hilo and Kailua Kona and close to Green Sands Beach and volcano ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Naalehu
A super cute New 24 x 24 Yurt and SPARKLING PRIVATE POOL! equipped with new appliances, Granite counters, custom tiled shower, new furniture, washer and dryer, photo voltaic electrical system, (Solar) , Ultra violet water filter system, Your ...
1 bedroom
1 bathroom
Sleeps 3
Hawaii> naalehu
Off grid luxury. Powered by state of the art photovoltics solar hot water. Surrounded by thousands of acres of DNLR land. Super quiet and private. Complete with granite counters, open beam celings, tiled shower, mountain and ocean views. ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Naalehu
Please read the entire description of the cabin so there aren't any surprises. Come experience this cute cozy OFF GRID cabin nestled in the woods, surrounded by tall Ohi'a trees with lots of tropical birds to serenade you. Situated at 2000' ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Naalehu
Fall in love with the spirit of Hawaii at this charming vacation rental in Naalehu. Sitting on 20 acres and boasting a fully equipped kitchen, this 3-bedroom, 2-bathroom home is perfect for families looking to live the island lifestyle. You'll ...
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Naalehu
This spacious, newly renovated second floor unit provides unique, breathtaking views of South Pacific with Whale Watching against the backdrop of mesmerizing, romantic sunsets behind Bali’ Hai’ from your front porch. The rear balcony...
0 bedroom
1 bathroom
Sleeps 6
Hawaii> Princeville
Want to get away from city lifestyle and experience the tranquil beautiful countryside? If you long for solitude, the sound of rustling leaves (and coqui frogs at night), gently falling rain, cool breezes, crashing waves & rustic ...
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Naalehu
This is a quiet retreat on South Point at the top of Discovery Harbour, where the weather is not too hot or cold year round. We now have wi-fi internet throughout the house, but no television. We do have good signal for most cell phone plans. ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Naalehu
This spacious, newly renovated second floor unit provides unique, breathtaking views of South Pacific against the backdrop of mesmerizing, romantic sunsets behind Bali' Hai' from your front porch. The rear balcony offers equally ...
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
This is a gorgeous 3 bedroom single level home with salt water solar heated pool, hot tub, Japanese-inspired Pagoda/Gazebo and views of the ocean. Functional floorplan, clean and comfortable. Large master bedroom with its four poster bed and...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Naalehu
Welcome to our cozy and spacious guesthouse suites on the south side of the Big Island! Explore the World-famous Volcano National Park, Kula Kai Caverns, Whittington Beach, Punalu'u Black Sand Beach, SouthPoint, South Point Blow Hole, Green Sand...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Naalehu