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 = '96772' OR postalCode = '96772' OR region like '%|96772|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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
HI> Waiohinu
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
HI> Naalehu
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
HI> Na’alehu
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 spectacular ...
0 bedroom
1 bathroom
Sleeps 4
HI> Princeville
. 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
HI> Naalehu
Come experience this cute cozy cabin nestled in the woods, surrounded by tall Ohi'a trees and lots of tropical birds to serenade you. Situated at 2000' elevation (which means it might get a bit chilly at times), on a very private, peaceful, ...
1 bedroom
1 bathroom
Sleeps 3
HI> 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
HI> Princeville
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
HI> 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
HI> 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
HI> naalehu
Starlit nights and exquisite ocean views await at this charming, remote home 1.5 miles from the town of Na'alehu. Situated near South Point and 1 hour from Hawaiʻi Volcanoes National Park, you'll easily reach all of your adventures on the Big ...
2 bedroom
1 bathroom
Sleeps 4
HI> Naalehu
Hawaii's #1 Small Hotel 3 years running, featuring complimentary locally sourced breakfasts daily and Chef Kenny's dinner nightly. Prices are per room per night.
Looking for the perfect compliment to the luxury resorts in the north? You've ...
1 bedroom
1 bathroom
Sleeps 2
HI> Naalehu
Lovely and well-maintained 3/2 Home in Discovery Harbour.
Has a beautiful salt water pool and hot tub and comes with most things you'd need to enjoy a relaxing getaway, including on-site washer and dryer. High Speed Internet available.
Visit...
3 bedroom
2 bathroom
Sleeps 6
HI> Naalehu
Experience the country life on the Big Island, Hawaii. Come and stay at our huge, 7,000 square foot farm house and cottage. Located in Naalehu on a 15-acre spread with breathtaking ocean views. Our property has two separate buildings. One, big ...
5 bedroom
5 bathroom
Sleeps 10
HI> Naalehu
Reserved the whole guesthouse & have two spacious studio rooms for friends and family. Located on the South side of the Big Island close to all scenic points of the Island. Guests can easy access to roads like H-11 & min to Naalehu town. We’re...
2 bedroom
1 bathroom
Sleeps 4
HI> Naalehu
