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, rewritten_description,
(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, 50
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
COOL A/C!!!
PLEASE NOTE:CONSTRUCTION: Several Lanais being rebuilt on the end of the building M-F 8:30-4:30. Noise during this time is to be expected.
UNIT # 307 AT KONA MAGIC SANDS!
We purchased this unit in 2017. Since then, we have
- 1 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
- 1 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
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
