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, 20
Newly built LUXURY designer home including a 6-person HOT TUB and an amazing view of the ocean. The living space on the top floor is 2300 sq. ft. including a covered lanai of 1150 sq. ft. Bedrooms with attached bathrooms are 30 ft. apart with ...
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Captain Cook
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Captain Cook
Ask about 10 day specials/ 2 person stays and 8th night free if you book a 7th night stay!!!!
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
Special rates are available for Summer and Fall! Book now and enjoy an incredible adventure on beautiful Hawaii Island!
3 bedroom
4 bathroom
Sleeps 6
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
