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 (`headline` like '%Kona Reef%') AND sleeps>=1 order by rating desc LIMIT 0, 15
NEW OWNERS! NEW UPDATES AND IMPROVEMENTS!
*We understand the uncertainty surrounding travel in the age of the Covid-19 pandemic so we take into consideration all extenuating Covid-related cancellations that are beyond guests' control. We ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Enjoy every moment of your get away to paradise in this beautiful direct ocean view condo. F14 is a 1 BR / 1 BA condo (King Sleep Number Bed in bedroom/plus Queen Sleeper Sofa with chaise lounge in living room) located at the desirable Kona ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
Awe inspiring views and once-in-a-lifetime experiences await you from our newly renovated, ocean/beachfront (direct ocean access) Kona Reef Condo. 2 BR/2BA (sleeps 4-6).
Kona Reef is a highly desired resort for it's walking proximity to Kona ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
See dolphins, whales, turtles and surfers from our ocean front lanai, in a desirable one bedroom condo in Kona! Kona Reef Oceanfront Resort is the 1st choice in vacation rentals. Unit A-16 offers spectacular views of the Pacific Ocean and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Welcome to your beautifully appointed Hawaiian hideaway, with Pacific Ocean views as far as the eye can see. This second-floor, end-unit condo in the popular Kona Reef complex has extra windows for taking in all the spectacular scenery from the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
The condo is located In Kailua Kona near Kona village Free fast cable WIFI as opposed to DSL.There is a security screen door so the ocean breeze can flow through the condo. At the front door...there are no buildings or walls to block the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Location, Location, Location - Oceanfront - D Building.
Direct View through Towering Palms into Kailua Bay.
Watch the Dolphins, Whales, Parasailers, Surfers and Beautiful Hawaiian Sunsets from your Oceanfront Lanai.
Listen to the soothing ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Welcome to our beautifully decorated ocean front condo in paradise - Kona Reef A-22.
Whether this is your first trip to Kailua-Kona or you are a frequent visitor to the island, our condo at Kona Reef Resort will definitely be your favorite ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
All Short Term Vacation Rentals on Hawaii are deemed Non-Essential Businesses under Hawaii's COVID-19 regulations. We can only rent to those who are not required to be under quarantine on arrival. Beginning October 15th, people arriving to ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Two great views from one lanai (ocean and pool) - oceanfront over the pool. Living room offers a queen sized sofa bed and other seating to view the ocean or watch your cable TV.
Kona Reef has an open floor plan and your newly remodeled kitchen...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
This fully air-conditioned beautiful oceanfront corner unit is less than 30 feet from the water's edge. Enjoy spectacular sunsets from your large lanai, watch the surfers, para-sailers, boogie boarders, playful dolphins, turtles, whales and ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
*We understand the uncertainty surrounding travel in the age of the Covid-19 pandemic so we take into consideration all extenuating Covid-related cancellations that are beyond guests' control. We willingly work with you to override our ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Fully updated 1BR/1BA in Kona Reef just steps from the beach and downtown Kailua-Kona. Air conditioning in both the living area and bedroom (rare!), Memory foam California King size bed, direct ocean facing with sweeping views of the horizon.
...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Our fabulous condo at the Kona Reef overlooks Honl's Beach Park and the beautiful turquoise blues and greens of the crystal clear ocean. From the 3rd floor lanai complete with ceiling fan, you have a fantastic palm-dotted view of the sand and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kona
Enjoy sunset views every night from this beautifully updated 1br 1ba condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom (rare!).
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
