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 by the Sea%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Perched above a rock cove on the Kona Coast and surrounded by a lush tropical landscape, Hale Nalu at Kona by the Sea is the perfect setting for your next trip abroad! This stunning ocean front home can accommodate you for the long haul whether ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona by the Sea 408 is an absolute oceanfront condo. This spacious suite is a 2 bedroom - 2 bath well-lit condo with CENTRAL AC and has seen many recent improvements. A new central AC system and washer dryer were installed recently, as well as ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
TMK: 750200160027
TA-053-340-5696-01
Relaxing 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. ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Kona by the Sea 315 is a roomy 2 bedroom 2-bath unit with all the amenities that you will need, including CENTRAL AC, full-size washer/dryer and high speed wifi. This rental requires a minimum 30-day reservation. No smoking. No pets. Free ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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.. The kitchen is well stocked with everything you might need for an extended stay. Enjoy a great...
- 0 bedroom
- 2 bathroom
- Sleeps 3
Hawaii> 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 4
Hawaii> Kailua Kona
Beautiful Ocean & Coastline Views. Private Beach! Romantic Sunset Views – Fully renovated! Ocean front unobstructed views, maybe a palm tree or two! Two bedrooms & two bathrooms, central air conditioning, top of the line kitchen and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
