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, rewritten_description,
(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
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
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
🌴Panoramic Ocean Views from the moment you enter.
6th Floor Ocean View Penthouse: free Wifi, 65” TV, wall wall sectional, crown molding, AC, ceiling fans, security screen door for cool island trade wind breezes. King
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Welcome to Island Surf 304, your perfect Maui getaway with stunning ocean views and unbeatable location! Situated directly across the street from Kihei's Kalama Park and the iconic Cove Beach, this beautifully decorated and well-appointed condo
- 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
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
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
