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, 20
. 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
Aloha and welcome to Halawai ( the point where heaven and earth meet), a Sanctuary Oasis on the road to Magical Green Sands Beach in Naalehu, HI. We are located half way between Hilo and Kona on a 3 acre peaceful sanctuary in mystical South ...
1 bedroom
1 bathroom
Sleeps 3
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
Aloha and welcome to Halawai ( the point where heaven and earth meet), a Sanctuary Oasis on the road to Magical Green Sands Beach in Naalehu, HI. We are located half way between Hilo and Kona on a 3 acre peaceful sanctuary in mystical South ...
0 bedroom
0 bathroom
Sleeps 3
Hawaii> Naalehu
Plan your getaway to this gorgeous 3-bedroom, 2 full bath single level home with stunning salt water solar heated pool, Japanese-inspired Pagoda/Gazebo and views of the ocean. Tropical inspired décor and floorplan make for clean 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
The ultimate Hawaii getaway awaits at this 2-bedroom, 1-bathroom vacation rental apartment 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
Experience Big Island aloha from your fully outfitted vacation rental house in Kaʻū, Hawaiʻi's southernmost district.
2 bedroom
1 bathroom
Sleeps 4
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.
0 bedroom
1 bathroom
Sleeps 6
Hawaii> Princeville
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
Please read the entire description of the cabin so there aren't any surprises.
1 bedroom
1 bathroom
Sleeps 2
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
Want to get away from city lifestyle and experience the tranquil beautiful countryside?
3 bedroom
2 bathroom
Sleeps 9
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.
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Princeville
The Makai View House is located on the gentile slopes of the South Kona Coast. Enjoy the Big Island lifestyle in this spectacular and private 3 bedroom / 2 bath home. Outdoor living extends from the open kitchen to the large BBQ grill area , ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Na'alehu
Relaxing Getaway! Come visit this amazing area of the island. Close to South Point (cliff diving and world class fishing), Green Sand Beach, Black Sand Beach with basking Green Sea Turtles, Volcano National Park and the quaint older sugar ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Naalehu
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
Your next rejuvenating retreat awaits with a stay at this 3-bedroom, 1-bath vacation rental in Naalehu, Hawaii. This home is situated in a peaceful location surrounded by fruit trees and boasts a wraparound deck, a spacious interior, and easy ...
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Naalehu
