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 Magic Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Studio located directly across the street from Magic Sands beach with 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 sea ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
*LOOK NO FURTHER! YOU HAVE FOUND AN INCREDIBLE PROPERTY!**
Kona Magic Sands#309 is a DIRECT oceanfront, Top floor condo that is meticulously maintained, beautifully furnished, and has absolutely anything any guest could dream of.
Air ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> 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
Hawaii> Kailua-Kona
Absolutely stunning offering at Kona Magic Sands.
This truly Ocean Front/Beachfront Condo at Magic Sands Beach has the WOW factor. A gorgeous remodeled Kitchen and Bath with Granite Counters, Maple Cabinets, Porcelain Tile in beautiful Walk ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
