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 (`headline` like '%Kona Reef%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Enjoy breathtaking sunset views every night from this beautifully updated condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom.
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to our beautifully decorated ocean front condo in paradise - Kona Reef A-22.
Our condo is our home away from home, so we respectfully request a maximum of 2 adult guests and no children under 18.
Whether this is your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> Kailua-Kona
Watch the Surfers from Your Lanai - Kona Reef Ocean Front Unit
Recently Remodeled.
Light and Airy with direct Ocean Views from the Lanai, Living and Kitchen areas. Large Ocean Front 611 square foot condo with a full 128 square ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
You're gonna love your Kona Reef Beachfront getaway. Imagine the big smile on your face when you walk through the door of this freshly renovated, top-floor, jewel of a condo... a place you get to call your Kailua-Kona "home" away from home. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Welcome to our beautifully decorated ocean front condo in paradise - Kona Reef D-24.
Our condo is our home away from home, so we respectfully request a maximum of 2 adult guests and no children under 18.
Whether this is your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Enjoy every moment of your get away to paradise in this beautiful direct ocean view condo. F14 is a 650 sq ft 1 BR / 1 BA condo located at the desirable Kona Reef complex; a very short walk just south of Kailua Kona. This condo has full AC! ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua-Kona
We are the proud owners of this beautiful vacation home and we are excited to share this slice of Paradise with you! We are experienced Superhosts and Premier Partners and we have worked hard to make sure you will have the most comfortable stay ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Wonderful Ocean Views Throughout your Spectacular 2 Bedroom, 2 Bath Condo! When you are dreaming about your much deserved Hawaii Oceanfront Getaway, this condo will exceed your expectations!
Your ocean view lanai (balcony) is ready for ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
NEW OWNERS! NEW UPDATES AND IMPROVEMENTS!
Welcome to our private and comfortable ocean front condo in paradise - Kona Reef D-34.
As soon as you arrive to our very private top floor ocean front condo located on the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Kona Reef Ocean front Resort sits a few steps away from the Pacific Ocean and a short walk to town, where shopping, dining, beaches, family entertainment and nightlife are all nearby a perfect location for a holiday, honeymoon, or ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua-Kona
Located on the edge of Kailua Village. Short walk to shopping and restaurants.
Indulge in the Hawaii resort experience from this luxury top floor unit that lies near the ocean at Kona Reef. Relax on your lanai watching for whales, dolphins ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
* PERFECT LOCATION * BEAUTIFUL OCEANFRONT VIEWS *
Leave your worries behind as you step into this exquisite beach-front sanctuary just minutes from downtown Kona. With breathtaking ocean views and an unbeatable location, this ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Located just outside the heart of Kailua- Kona, this ocean front condo is your ticket to paradise! Just minutes from town, a short walk will take you to some of the finest dining and shopping destinations on the Big Island. Our modern, updated...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Beautiful and fully appointed condo with walkout to Hon'ls beach. or enjoy the pool, jacuzzi and BBQ area to laze your day away. Relax on the Lanai for coffee and meals while the ocean waves sooth away your worries. Walking distance to ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
*New Listing* Oceanfront, fully updated one-bedroom condo in the Kona Reef Resort. AC throughout the entire unit.
Perfectly located just a 5-minute walk from dozens of shops, restaurants, and beaches.
As you walk through the door ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Welcome to Paradise! From our direct oceanfront top floor condo, you can enjoy the close-up sights and sounds of the ocean all day long, as the waves come rolling in right in front of you and crash on the black rocks. From our private lanai ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> Kona
