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
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...
- 2 bedroom
- 1 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 ...
- 2 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. Many guests feel no need to rent car. Shuttle direct to Island...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Amazing Ocean Views from from the moment you enter.
Top 6th floor: free Wifi, 65” TV, King Koa Wood Bed, new stainless steel appliances, Keurig coffee machine, washer dryer in unit....
- 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 ...
- 2 bedroom
- 1 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
Island Surf AOAO will be performing spalling and structural repairs from August 1, 2024 through December 15th, 2024. Residents should expect noise and debris and limited use of the lanais during working hours which are Monday through Friday, 8 ...
- 2 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
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 ...
- 3 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 ...
- 1 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
Located in Kihei, this delightful studio is the perfect retreat for guests seeking a "noncooking" experience. With its excellent location, you'll have easy access to all the restaurants, shopping, and surfing attractions this quaint beach town ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
