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 '%Kona by the Sea%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This stunning property is now accepting longer-term stays at reduced rates. Please inquire for details.
In the resort setting of Kona by the Sea, unit #124 is located in the historic Ali'i Dr. district, in downtown Kailua-Kona. This fantastic ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Kona by the Sea is an absolute oceanfront property. Unit 323 is a spacious 1 bedroom - 2 bath well-lit condo with many beautiful upgraded features.. Enjoy a great view of the ocean looking south along the Kona Coast. An elevator makes for easy...
- 0 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Wonderful direct oceanfront view from this Kona By The Sea top floor 2 bedroom/ 2 bath condo. This unit is fully equipped to provide all the essentials you will need for your stay, including high speed wifi, a must for working remotely! Enjoy ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Luxurious Ocean Front Condo! Stunning direct ocean views. This one bedroom - two bath condominium has been fully updated with quality materials. Custom solid cherry wood cabinets and interior doors. Gleaming granite counter-tops in kitchen and ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
HI> Kailua Kona
Ocean Front Condo with mauka/makai views. This two-bedroom - two bath condominium is fully equipped to provide all the essentials you will need for your stay. This end unit offers ocean and mountain views. Enjoy the ocean breezes during the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Mesmerizing Ocean & Coastline Views with Sandy Private Beach! Romantic Sunset Views & Ocean Views depending on the time of year– Updated throughout! Direct ocean front! Spacious with two Bedrooms & two bathrooms, central air conditioning, top...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona