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
Check out our photos, check out our reviews, now here’s the highlights:
• One king bed, one queen bed, and one queen pullout
• Unique layout: Very few condos in Hawaii, in this price range, are constructed with so much frontage ...
- 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 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 ...
- 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
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
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
