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 '%island surf%') AND sleeps>=1 order by rating desc LIMIT 0, 15
IMPORTANT INFORMATION REGARDING COVID-19 - December 2021
As of October 15, 2020, Hawaii is allowing visitors to avoid the 14 day quarantine upon proof of a negative Covid-19 test result at the point of entry into the islands and membership in ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kihei
Island Surf #502 is a 1 bedroom, 1 bath ocean view unit on the 5th floor. It features room unit A/C in both the living room and bedroom, ceiling fans, a fully-equipped kitchen, washer and dryer, a flat-screen TV, telephone service with free long...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kihei
E komo mai! This is the Hawaiian way of saying all are welcome.
Mahalo (thank you) for taking a moment to view my home in beautiful Kihei, Maui. This 4th floor condo is located in the most convenient location on Maui. From the moment you walk ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
This ocean view condo with a generous lanai at the Island Surf resort in Kihei is just steps away from amazing beaches and in the perfect location for enjoying all South Maui has to offer. Open the gigantic sliding glass door to soak in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
Thank you for looking at our listing! Our unobstructed ocean view condo has 1 bedroom, 1 bathroom, a sleeper sofa, and a Murphy bed. Sleeps 6. Direct view of OCEANFRONT & BEACHFRONT with Amazing views!!! Watch sunsets over the ocean on the ...
- 1 bedroom
- 1 bathroom
- Sleeps 6
HI> Kihei
If you are looking for a Maui condo with views of the Pacific Ocean and quiet beaches close by, you belong at Island Surf 312. Just across the street from your complex are Kalama Beach and world-famous Cove Beach, the perfect place for beginning...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
E Komo Mai to our unit at the Island Surf located in South Kihei. This unit with partial ocean view is situated directly across from Kalama Beach Park and in walking distance to shopping, restaurants, surfboard rentals, surf school and a short ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kihei
Looking for a Maui condo with views of the Pacific Ocean and quiet beaches close by? You belong at Island Surf 506!
Just across the street from the complex, you'll find Kalama Beach and world-famous Cove Beach, the perfect place for beginning ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kihei
