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 '%Keauhou Kona Surf%') AND sleeps>=1 order by rating desc LIMIT 0, 15
ENHANCED CLEANING PROCEDURES! Sit and relax on the lanai and enjoy stunning ocean and sunset views. Fully equipped kitchen with everything you could need. New kitchen cabinets, counter tops, floors, and bathrooms. New high definition ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
The best location in the resort with Sunset Views Year Round:
Desirable Building #5
Ground Floor
Corner Unit with Large Lanai
Non-Smoking no vaping No service fee if booked at alohacondos.com email ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
This OCEANFRONT complex,adjacent to the Kona Country Club, which has two 18 hole golf courses, sits on 17 prime acres with coconut palms,lush tropical landsacpaes and a grass volley-ball court.
We are 5 miles south of Kailua/Kona, 25 miles south...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Kailua-Kona
Vacation in this spacious, absolutely oceanfront 2 bedroom, two story townhouse! It’s located on Hawaii's BigIsland, 5 miles from the town of Kailua-Kona at the Keauhou Kona Surf & Racquet Club. This gated 17 acre oceanfront complex has ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Condo 1-102 at the Keauhou Kona Surf and Racquet Club Resort is as close as you can get to the ocean on the Big Island of Hawaii. The condo section of the Keauhou Kona Surf and Racquet Club Resort was built when the construction permits didn't ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kailua Kona
Come and enjoy Hawaiian living at its best in our lovingly cared for, spacious and elegant, two bedroom, two bath oceanfront condominium situated on 16 tropically landscaped acres in a gated oceanfront resort. Just a stone's throw from the ocean...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> KAILUA KONA
Best oceanfront location in Kona with sunset views year round. Watch whales right from your condo during the winter months and fall asleep to the soothing sounds of the ocean.
30 Seconds from Blender to Ocean
Private Top Floor Corner Condo ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Panoramic, 270 degree Oceanfront Views The best location and views in the complex Fully equipped Kitchen, Large and Small Plastic Coolers Tennis racquets, Various snorkel gear, Free Wireless High-Speed Internet Access Unlimited, Free Telephone ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona
Aloha! Nicely furnished oceanfront condo on 2nd floor of the exclusive Keauhou Kona Surf & Racquet Club. Relax on the spacious and private lanai where you’ll enjoy the soothing sounds of the waves, the 180⁰ ocean views, and the stunning Kona...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Oceanfront Location Expansive Ocean Views Ground-Floor 2-BR /2-BA Condo with Kitchen Kitchen & Washer/Dryer Private Oceanfront Lanai Oceanfront Master Bedroom • Great Oceanfront Location• Pool, Cabana & BBQs• 3 Lighted Tennis Courts• ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona
Direct Oceanfront Location
Free Wireless High Speed Internet
Fully Equipped Kitchen & Washer/Dryer
Private Oceanfront Lanai
Oceanfront Master Bedroom
• Great Oceanfront Location• Pool, Cabana & BBQs• 3 Lighted Tennis Courts• Tropical ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona
