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, 50
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
Experience the epitome of Beach Town living while relaxing in the welcoming comfort of our top floor, South Kihei vacation home. Indulge in breathtaking ocean and sunset vistas from your private double lanai. Stunning panoramic views from ...
- 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
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
🌺 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. Speedi Shuttle runs from...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
🌺The Island Surf Condominium Building has been completely remodeled and is zoned as Condo/Hotel and as such is NOT subject to any of the efforts to convert short term vacation rentals to long term rentals. 🌴You...
- 1 bedroom
- 1 bathroom
- Sleeps 2
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
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
If you want to be within walking distance to all the best food and fun to be found in South Maui, this is the property for you! This stunning ocean view condo offers an unparalleled blend of comfort and luxury. As you step into the spacious, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
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
