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 '%island surf%') AND sleeps>=1 order by rating desc LIMIT 0, 20
A note to all of our prospective guests: Island Surf is undergoing construction on the ground floor, front of the building. How will this affect you? Some of our first floor shops may be closed. There may be construction noise at times. Pool and...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> 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
Hawaii> 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...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Island Surf 507
Enjoy panoramic view from our beautiful unit. Island Surf is in the heart of all the action in Kihei, surrounded by everything you need for a perfect tropical vacation!
Walk across the street to beach access to the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
🌺 The Island Surf is located in the Heart of South Maui. For guests that do not want to spend a fortune in hotel bills, but want a clean affordable place in a great location.
Walk to Charley Young Beach, South Maui Gardens, Cove ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> Kihei
Island Surf 304, located directly across the street from Kihei's stunning Kalama Park and the famed Cove Beach, this ocean view condo is the perfect location for your Maui adventure. Clean, beautifully decorated, Split AC, cable, Wi-Fi, and a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Summary:
Aloha and welcome to our 2 bedroom condo, your home away from home! My family and I live on Maui and we are so excited to host your vacation. Don't get stuck with a major rental company that doesn't care about your individual group. ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Within steps of sunny Kalama Beach’s warm turquoise waters, you’ll discover the perfect Maui, Hawaii getaway for a romantic retreat or small family vacation or friends’ trip at Island Surf 506.
Light, bright, and beautiful, Island ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Discover the perfect Maui retreat at Island Surf 312, where stunning ocean views and serene beaches await just steps from your door. Nestled across the street from Kalama Beach and Cove Beach, this two-bedroom, two-bathroom condo offers the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
Escape to the serene paradise of Island Surf 510C, a cozy tropical condo nestled in the heart of Kihei, Hawaii. This charming vacation rental offers a tranquil retreat for up to 4 guests, boasting 2 spacious bedrooms and 2 bathrooms all ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
Escape to the serene paradise of Island Surf 510A, a cozy tropical studio nestled in the heart of Kihei, Hawaii. This charming vacation rental offers a tranquil retreat for up to 2 guests, offering a studio bedroom and 1 bathroom all ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Escape to the serene paradise of Island Surf 510B, a cozy tropical condo nestled in the heart of Kihei, Hawaii. This charming vacation rental offers a tranquil retreat for up to 2 guests, boasting 1 spacious bedroom and 1 bathroom all ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
