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 '%Hale Ono Loa%') AND sleeps>=1 order by rating desc LIMIT 0, 20
This unit has an incredible 3rd floor oceanfront view of our neighboring island, Lana’i. Enjoy the sounds of the surf while watching turtles and whales from the shoreline or your private lanai. The complex provides a relaxing sundeck and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Book with us and come to Maui to explore everything we have to offer!
Hale Ono Loa means, "House of good living" in Hawaiian. This 1 bedroom 1 bathroom ground floor condo between Kaanapali and Kapalua is nicely appointed with everything ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Hale Ono Loa 111
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Welcome to Hale Ono Loa! Relaxation comes easy at this ground-floor 1BR ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
This remodeled condo with a beach cottage feel is ready to treat you to essential amenities and amazing winter sunsets and views of Lanai off the furnished patio. You'll love feeling the tradewinds as you lounge poolside or watch for turtles ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
This is a legally permitted vacation rental according to current County of Maui Ordinance.
Sleep steps from the ocean and enjoy private views of West Maui sunsets at Hale Ono Loa 121
Experience oceanfront living in this exclusive ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
