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 = 'Keaau' OR postalCode = 'Keaau' OR region like '%|Keaau|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Welcome to Pua Lani, a gated one acre estate two blocks from the ocean that features resort landscaping and large lawn areas that create a very private and serene setting for your stay. The second story home features 2 bedrooms and 2 ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Modern furnishings and recently updated with Air Conditioners in every room!! New Kitchen appliances makes meal prep a breeze. Private home is conveniently located 30 minutes from Hilo, 40 minutes from the newest beach in the world, and less ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Keaau
Explore the Big Island and use Casa Tortuga as your home base. Only 40 feet from the ocean, this 1400 sq. ft. luxury custom built home has a feeling that brings serenity to all of its guests. It lies on one half acre in a quiet upscale ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Cool tradewinds will carry the sweet fragrance of jasmine and plumeria through the air as you relax in this tropical garden with bananas, pineapples, guavas and papayas. The Paradise Cottage is a charming two bedroom / one bath home on 1/2 acre ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Keaau
Welcome to Hale Anuenue Nuku - The Rainbow's End in sunny Hawaiian Paradise Park on the Big Island! This newly renovated and decorated 2-bedroom, 2-bath home sleeps 2-8 and has everything you need for that getaway to the Big Island. The ...
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Keaau
You can stay just minutes from the sunrise cliff views at Maku'u in a recently remodeled respite home in a charming community with local flair. This stunning 2 story space, is perfect for a single or multi-family getaway with an upstairs Queen ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> KEAAU
The Bird's Nest is located in a peaceful neighborhood that is surrounded by lush greenery and bird songs can be heard throughout the day & tree frogs at night. The house sits high above the ground which makes it feel as if you are staying in a ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Aloha and welcome to my Hawai’i Island cottage! I am delighted to share my 1 acre of rainforest and hope you will choose to stay at Hawaiian Paradise Cottage. Enter the property through a private electric gate and down a tree-lined ...
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Private Acre, Entire Home has Air-Conditioning located in a Quiet peaceful neighborhood. Easy access to everything the Big Island of Hawaii has to offer. Experience the best of the Big Island of Hawaii while staying at our “Hawaiian Vacation ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Hale kilo hōkū means "star gazing house" because you can clearly see the stars while sitting on the Lanai on any given night. This is a brand newly constructed property located in Hawaiian paradise park approximately 1 mile from the ocean. ...
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Tropical island paradise! Breathe in the ocean air from your private balcony. Step out 100 yards on to Magnificent Oceanfront. Enjoy the fresh modern design of the newly built property. Every our room has its own entrance, own bathroom, ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Welcome to Hale Kailani, the 'Sea and Sky House'. Enjoy breathtaking views of the Pacific Ocean, amazing sunrises, occasional lightning storms, year round turtle and spinner dolphins and seasonal humpback whales right off the back lanai. ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
Cool trade winds will carry the sweet fragrance of jasmine and plumeria through the air as you relax in this tropical garden with mango, banana and papayas. The property is situated in the residential community of Hawaiian Paradise Park, on one ...
3 bedroom
3 bathroom
Sleeps 4
Hawaii> Keaau
Aloha and e komo mai to the Cliff House, At this oceanfront escape on the cliffs of Hawaiian Paradise Park, you'll enjoy beautiful ocean views on a large lanai (deck) where you can dine, glimpse passing whales (when in season), turtles, ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
Enjoy indoor-outdoor living in our bright, open home... Relax on the lanai overlooking lush vegetation and lava outcrops, smell the fragrant Hawaiian air and listen to the waves crash in the distance. Secluded and quiet--a garden oasis far ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
The cozy peaceful studio apartment has everything you need for your Hawaii trip. The unit comes with High-Speed internet Wi-Fi, self-check-in with a keyless entry smart lock keypad, and free parking right in front of your unit. During your stay,...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
Hawaii STVR Reg. Number: STRV-19-348723 Hawaii NUCR Number: NUC-19-389 Come relax in our two-bedroom, two-bath house with a large ocean view lanai. The house is on a quiet street that runs along the ocean and has two ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Swap the daily grind for luxurious absolute oceanfront tranquility with an escape to Paradise Breeze Retreat in Keaau, Hawaii. Only a 25 minute drive from Hilo Airport but a world away from stress. Stunning panoramic oceanfront views from every ...
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Keaau
Enjoy our incredible custom home has a charming warm Hawaiian feel. With a world class custom kitchen with huge island that opens to a super comfy living room. 2 huge bathrooms, the master with jacuzzi tub and royal shower. You will be ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Keaau
Located on the Big Island of Hawaii, this detached studio guest house is fully furnished, very private, with vaulted ceilings on a beautifully landscaped 3 acre privately enclosed property. Property has a gated entrance with private gate code, ...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Keaau
Kalei’s Hawaiian Hideaway is a well-appointed tropical getaway that is nestled in the lush forests of Puna on Hawaiʻi Island. This 1750sqft custom built cedar home has 3 spacious bedrooms and 2 baths. Our home offers a rustic feel, yet ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
E Komo Mai (welcome) to Hale Hapu’u! Our little Hawaiian hut is perfect for couples seeking an authentic Big Island experience. The Hale is adorned with hand carved art from local artists, an outdoor bath set in the midst of a tropical garden...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
Welcome to our Tropical Hideaway 20 minutes from Hilo. Our home is situated on 1 acre in the coveted Hawaiian Paradise Park area. You will love the beautiful, landscaped garden with many native trees and plants, the large Lanai, carport,...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
New home and new VRBO listing. House was built in 2020 and has all modern amenities! Get away from the crowds! Experience the best of all worlds; 3 blocks from the ocean on 1 country acre, a gated private home, and only a 45 minute drive to the ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Keaau
The Lava Suite. BRAND NEW beautiful, large, ground floor studio apartment with tons of class. Cool AC. Open kitchen with all the amenities, full private custom bathroom, extremely plush 18" memory foam king size bed. Get comfortable on ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Keaau
This spacious house is perfect for families looking to experience all of the sights and attractions of the eastern side of the Big Island while being in a peaceful quieter area. Located a few blocks away from the trail head to hike 2.5 miles to ...
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Keaau
Beautiful Cottage Home Located on the big island sunbelt, large patio open's to double french door's Welcoming You Into a Very Large Living Room and Dining Room Combination.. Very Clean!! Open..... Airy..... Cozy ...
3 bedroom
2 bathroom
Sleeps 9
Hawaii> Keaau