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 '%Keauhou Kona Surf%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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
Hawaii> Kailua-Kona
Aloha! Beautifully remodeled luxury oceanfront condo in the exclusive Keauhou Kona Surf & Racquet Club. This gorgeous unit features designer touches and high end finishes throughout. Relax on the spacious and private lanai where you’ll enjoy...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Oceanfront Top Floor with sunsets over the clear blue Pacific waters every evening: Loft and Vaulted CeilingsDirect, Unobstructed Ocean ViewOceanfront Master Bedroom • Great Oceanfront Location• Pool, Cabana & BBQs• 3 Lighted Tennis ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Keauhou Kona Surf & Racquet Condo 2-202 is a perfect place to stay in Hawaii. Located next to the ocean, it is fully furnished with wonderful amenities, including a pool. It offers easy access to beach activities and is clean and neat. Ideal for...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> 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 ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Arguably one of the best units in the Keauhou Kona Surf and Racquet Complex, this fully renovated 2-bedroom, 2-bathroom ground floor condominium unit features a wrap-around lanai, fantastic ocean views, and a multitude of amenities. Located ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Keauhou Kona Surf & Racquet Club Condo 1-301 offers a comfortable, well-furnished stay with exceptional ocean views. Conveniently located near shops and restaurants, it makes exploring Kona easy. The wrap-around lanai provides breathtaking ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
ABSOLUTE OCEANFRONT! AIR CONDITIONED! REMODELED!
Nestled at the edge of a secluded cove and beach, the newly remodeled 'Waving Turtle' Oceanfront Townhome offers dramatic ocean views from its lanai, living room, kitchen, master bedroom ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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• ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
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 televisions and sound system. This ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
