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 Magic Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 15
COOL A/C!!!
PLEASE NOTE:CONSTRUCTION: Several Lanais being rebuilt but construction is currently ceased. No start up date is available at this time. There will be noise when construction resumes, but should be minimal.
UNIT # 307 AT KONA MAGIC ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Studio located directly across the street from Magic Sands beach with newly renovated kitchen and bathroom. The bedroom is separated from the living/dining area by a wall with an arched opening. You will notice the small touches honoring the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Steps away from Kona Magic Sands Beach. Total Ocean View. This comfortable , absolute ocean - front 3rd Floor unit is perfect for your special romantic occasions; however it will sleep four.This is a studio unit. Privacy,security & sunset ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
**LOOK NO FURTHER! YOU HAVE FOUND THE PERFECT PROPERTY!**
Kona Magic Sands#213 is a DIRECT oceanfront, 2nd floor condo that is meticulously maintained, beautifully furnished, and has absolutely anything any guest could dream of.
Wifi, beach ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
Stunning unobstructed ocean views greet you through the floor-to-ceiling glass doors as you enter this lovely waterfront studio condo. Located in the Magic Sands Condominium Complex right next to Magic Sands Beach, you will have access to their ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
