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, 30
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
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
Come to Paradise. Wake up to amazing sunrises and enjoy magnificent sunsets with an unobstructed view of the ocean, and take in the shoreline magnificence of beautiful Kona on the Big Island of Hawaii.
We are the proud owners of this ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
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 ...
- 1 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
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
ALOHA AND WELCOME TO HONU HAVEN! Managed by Kona Beach Properties
Kona Reef Unit A24 is a nicely appointed 1 bedroom, 1 bath oceanfront condo located downtown in Historic Kailua Village. Open the sliding glass door and let the sound of ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
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
Just released for rent!
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 ...
- 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 ...
- 1 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! Located just minutes from town, a short walk will take you to some of the finest dining and shopping destinations on the Big Island. Our newly ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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
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
Come and enjoy your stay in this beautifully upgraded 1 bedroom condo at Kona Reef. Located on a desirable single level overlooking the pool and the ocean, you will find all the comforts you would expect in this tropical paradise. Let the sound...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Escape to tropical paradise at this 1-bedroom,1-bathroom Kailua-Kona vacation rental condo which is located in walking distance to Kailua town. ! Sleeping up to 2 guests, this fully furnished home boasts an ideal oceanfront location along with ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Our End Unit ground floor condo has 1BR, with a King-size bed, 48" flat screen Smart TV, A/C, sliding barn door to the bathroom and bamboo privacy shades in the bedroom. We also offer a queen sleeper sofa in the Living Room.
The kitchen is ...
- 1 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> Kailua-Kona
This fully-furnished oceanfront condominium, is located 23 feet from the water on Alii Drive in Kailua-Kona. We are on the third level of a four level complex, A23. Listen to the ocean waves or catch a beautiful sunset while dining on your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Kona Reef, beautiful one-bedroom, direct oceanfront condominium, on world-famous Alii Drive. Our condo is 23 feet from the ocean and offers spectacular sunsets. Watch the surfers and the dolphins from the lanai.
This immaculate, recently ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua-Kona
Kona Reef D-8 is a large 2-bedroom panoramic floor to ceiling oceanfront ground floor condo with breathtaking year-round golden sunset views from the lanai! Hear the waves crashing day and night!
This recently upgraded, spacious 1000+ sf...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> 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
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
Spectacular, Direct Oceanfront, Kona Reef Condominium! This incredible unit truly is one of the best at the development.
Unit#D-17 is on the 1st Floor of the very popular Kona Reef Development.
Notable features include:
~Gorgeous ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Awesome third floor ocean front unit, with a rare corner/end location near the pool. Sit back and relax on your own private ocean front lanai and take in some of the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Located on the water's edge in scenic Kailua-Kona, this vacation home features oceanfront views and is perfect for a family or group of friends. The condominium ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona