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, 30
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
**Hale Ono Loa condos DID NOT catch on fire. Ka'anapali, the Whalers Village, and Northward (i.e.,Honokowai, Kahana, Napili, Kapalau) WERE NOT AFFECTED by the fires. The Old Lahaina Town is closed but the rest of West Maui and the beaches are ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Lahaina
Gorgeous top floor Penthouse Suite featuring two-bedrooms and two bathrooms, breathtaking oceanfront 270 degree views from the spacious 50 foot wrap-around lanai. Enjoy turtle, whale, and sunset watching at its best! Open the screened front door...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
We recently spent two wonderful weeks on Maui. The temperature is in the 80’s, the sky is blue, the sunsets are breathtaking, the water is warm, and the humpback whales are back.
While we were able to get a glimpse of the extensive ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
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 you need for a Maui getaway! Fully stocked kitchen for those evenings you want...
- 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
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
A paradise within Kaanapli. My oceanfront unit is the perfect escape destination. Come unwind on the special ocean-side sundeck and watch the whales play in the Pacific during the winter or be amazed at the numerous turtles right out front. An ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Napili-Honokowai
*Yes Lahaina is open, no we were not impacted by the fires, YES you should come to Maui!! Hale Ono Loa Unit 308!
A beautiful one-bedroom condo overlooking the ocean. Sit on your lanai in the morning with your beverage of choice and see ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina