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 '%Hono Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 50
In the beautiful ground floor Hono Kai A11 garden-view unit, you will be immersed in rich tropical foliage from your spacious private lanai only steps from the complex beach access! Remodeled and decorated with the spirit of Aloha, this unit ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Maalaea
In this direct ocean and beach view unit, you will feel immersed in the rich tropical views and ocean sounds as you enjoy your private patio only steps away from prime beach access!
Soak in amazing ocean views on your private 3rd-floor ...
- 3 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
Immerse yourself in the rich tropical views and ocean sounds while enjoying your private patio just steps from prime beach access!
Located just steps away from the longest beach on Maui with sugary sand, this remodeled condo on the 2nd ...
- 4 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Maalaea
In this beautiful second-floor garden-view unit, you will feel immersed in the rich tropical foliage as you enjoy your private patio only steps away from complex beach access!
Nestled at the entry of the complex and garden koi pond, just...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Maalaea
In this beautiful ground-floor garden-view unit, you will feel immersed in the rich tropical foliage as you enjoy your private patio only steps away from complex beach access!
Recently remodeled, painted, and refurnished, this unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Wailuku
There are spectacular unobstructed views of the ocean and Haleakala with floor to ceiling windows from inside the condo and on the lanai! You simply can't get closer to the beach or ocean! Perfect for whale watching! Steps away from the ocean, ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
Beautiful ground floor and newly remodeled! Hono Kai A14 is a garden-view unit, immersed in rich tropical foliage with a spacious private lanai only steps from the complex beach access! Decorated with a modern beachy decor, this unit features a ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Maalaea
Relax and rejuvenate with stunning Ocean and Haleakala views from your living room and lanai. Perfect for whale watching, this beachfront escape features a fully stocked kitchen and all the comforts you need. Lounge by the oceanfront pool, savor...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Wailuku
