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 = '96704' OR postalCode = '96704' OR region like '%|96704|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Captain Cook
https://www.youtube.com/watch?v=VigSB3M4z3w
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Captain Cook
Rick’s Place
1 bedroom
3 bathroom
Sleeps 2
Hawaii> Milolii
South Kona Hideaway is near some of the best snorkeling Hawaii has to offer. We are situated between Honaunau (Place of Refuge) and Kealakekua Bay at an elevation of 900 ft. Your beautifully decorated suite has a living room with a queen sofa...
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Captain Cook
Hale Pali (Cliff House) is a spectacular (non-smoking) OCEANFRONT home overlooking the blue Pacific. We heartily agree with our guests who have enthusiastically stated: 'there is nothing that compares to actually being on the water' - what ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Milolii
HAWAII LAW COMPLIANT !!
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Captain Cook
Marvel at moonrises that turn into astounding starry skies, pick fresh papayas and passionfruit in the yard, and be awed by Pacific Ocean views, all from this breezy bungalow. This 3-bedroom, 2-bath vacation rental house provides an epic ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ocean View
Kona recalls the Beach Boys playing their pop hits by the pool. The band left but the good vibrations linger. Situated on 9.5 gated acres, gated for with an amazing Hawaiian plantation-style home with separate sleeping quarters and a guest ...
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Captain Cook
Aloha & Welcome to Maka Nui. The name means Big Veiw which you will see as soon as you enter thru the gate to our paradise. The home sits on the cliff overlooking Kealakeakua Bay which is a marine preserve with wonderful snorkeling & kayaking. ...
4 bedroom
5 bathroom
Sleeps 10
Hawaii> Captain Cook
Beautiful island style cottage with sunset views and tropical garden. If you want to experience true Hawaiian living in an eco-friendly natural, non smoking environment this is your place. Have a soak in the tub, pick fresh herbs, veggies and ...
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Captain Cook
Your hosts Tammy and Joe are committed to providing a 5-star experience. The home is maintained in immaculate condition with the highest levels of spotless cleaning before you check in.
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Captain Cook
If you are adventurous and free spirited, but still require all the amenities a luxury rental home has to offer then Pu`uhonua ('Place of Refuge') is the place for you. Experience tropical Hawaii at it’s finest in this unique pole house ...
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Captain Cook
'Okika Lani sits at the south end of Kealakekua Bay on a private road with spectacular views of the Pacific Ocean, the Captain Cook Monument, and crescent shaped, white sand Ke'ei Beach.
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Captain Cook
The property is a one-acre lot with a small mango orchard (30 trees), fruit trees (lime, lemon, orange and avocado) and a beautifully manicured landscape. The cottage is separate from the main house, so your privacy is assured. There is a ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
This rustic wooden mountain cabin is situated next to a forest, in a secluded spot away from the main house, on a 3.5 acre farm. At an elevation of 2,100 feet, it avoids the summer heat at sea level ('Cool and peaceful..' - Teri), yet is close ...
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Captain Cook
STVR 19-360891, NUC 19-1179
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Captain Cook
Front row views of Kona sunsets
4 bedroom
4 bathroom
Sleeps 9
Hawaii> Captain Cook
Dates are still available for early 2024!
3 bedroom
4 bathroom
Sleeps 6
Hawaii> Captain Cook
Sun Tracks Across the Water ---
2 bedroom
1 bathroom
Sleeps 3
Hawaii> captain cook
Everything you might need to enjoy an exciting or relaxing vacation right by the ocean at the beautiful and world famous, "Kealakekua Bay'.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
'HALE GEDIMINAS' vacation rental is in a lush tropical setting on sunny Kona Coast. Spectacular sunsets on private lanai with ocean view overlooking Ili-Ili beach, which is 5 min down by car. The unit is studio like with private entrance, has a...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Please check out the main house on the same property for larger groups:
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
Welcome to PonoMana Oasis. A perfect and secluded destination if you are coming to Hawaii for a personal retreat, vacation or much-needed rejuvenation.
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
The Pagoda House is a beautiful, almost ocean-front traditional Hawai'ian screen home with a private pool and lush tropical garden overlooking Kealakekua Bay Marine Sanctuary - widely considered some of the best snorkeling in all of Hawai'i. The...
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Captain Cook
The Downstairs unit has 2 bedrooms, 2 baths with a living room and full kitchen. There is a private Sun Deck with a spectacular view of the bay and a great place to enjoy meals and/or an evening sunset. The property has a its own kayak launch ...
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Captain Cook
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Captain Cook
Please check out our cottage on the same property for smaller groups: Vrbo 953957
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Captain Cook
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
DUE TO OUR AGE & MEDICAL HISTORY WE ARE ONLY ACCEPTING FULLY COVID 19 VACCINATED GUESTS! We have both had 3 shots of the Phizer vaccine, wear masks when in public and have kept our little coffee farm a safe place. We will not accept ...
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Captain Cook
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Captain Cook
