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, 30
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
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
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
Unsurpassed Ocean & Sunset Views from your Lanai.
Designer Decorated & Furnished.
Located within Steps of Water's Edge.
Fully Equipped Kitchen.
Covered Private Oceanfront Lanai. Oceanfront Master Bedroom & Living ...
- 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 ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
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
Please note that there will be ongoing pool maintenance from April 15 - May 3, 2024. As it stands, the pool is scheduled to reopen on May 4, 2024 at 8am. During this time, the only pool available is the county pool at the Kona Aquatic Center. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Kailua Kona
Please note that there will be ongoing pool maintenance from April 15 - May 3, 2024. As it stands, the pool is scheduled to reopen on May 4, 2024 at 8am. During this time, the only pool available is the county pool at the Kona Aquatic Center. ...
- 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• ...
- 2 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...
- 2 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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
