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 Sands%') AND sleeps>=1 order by rating desc LIMIT 0, 50
This rare ocean-front condo is stunning! Listen to the sound of the ocean from the comfort of your home, while watching whales (Dec-April) and Hawaiian Green Sea Turtles all year long!
Located a five minute walk from boats, Maui Ocean
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Location, location, location! Wake up to diamonds sparkling on the ocean top.
Spectacular top floor views of Makena, Molokini crater, and the Island of Kahoolawe. Whales in full view from our lanai during whale season, all while enjoying
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
With the ocean just a stones throw away, you will be amazed by the spectacular breathtaking panoramic views! Immaculate and upgraded this spacious 989 sf 2 bedroom, 2 bath, oceanfront condo is your oasis in paradise. Master bedroom and living
- 3 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Wailuku
New split system a/c cooling living room and both bedrooms! This 5th story unit offers gorgeous views of the ocean, Haleakala and peaceful Maalaea Bay that you can enjoy on your large, shady, private lanai which is accessible from the living
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
A wonderful just renovated modern unit on Maalaea Bay with a beautiful ocean view that was recently renovated with all new kitchen, bathroom, floor surfaces and furniture.
The oceanfront pool is a wonderful place to spend the day
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
"Spring into Aloha - Catch the Rainbow, - Your Island Getaway Starts Here!".
Oceanfront Condo with Ocean and Mountain Views on Beautiful Ma`alaea Bay. Features oceanfront saltwater swimming pool.
Please review the property
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Wailuku
Hawaii Tax ID: TA210-586-8288-01
TMK: 380140150061
Completely renovated beautiful oceanfront condo overlooking Maalaea Bay.
From your private lanai, enjoy the spectacular ocean views with Kahoolawe and Molokini in the backdrop. Or
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
A wonderful modern unit on Maalaea Bay with a beautiful ocean view. The unit was just totally renovated throughout to Five Star Quality with all new floor, bathroom, kitchen, bedroom, living room, and lanai surfaces and furniture. Pleae see the
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Cooling breeze throughout the condo in living room and both bedrooms! This 6th story unit offers gorgeous views of the ocean, Sea Turtle sanctuary, Haleakala and peaceful Maalaea Bay that you can enjoy on your large, shady, private lanai which
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Island Sands Resort 111
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
This top floor corner unit offers amazing ocean views!! Enjoy radiant sunsets from your private lanai overlooking Ma'alaea Bay. Light and bright as you enter, this beautifully designed 2 bedroom, 2 bathroom unit features fresh paint and new wood
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Island Sands Resort 107
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
Island Sands Resort 208
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Island Sands Resort 302
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
The Island Sands Resort on Maui is located oceanfront in Maalaea, just
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailuku
Welcome to Island Sands 500, your perfect Maui escape with breathtaking ocean and West Maui Mountain views. This beautifully remodeled fifth-floor unit offers two separate private lanais, accessible from the living room and primary bedroom,
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
