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 '%Honokeana Cove%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Unit 203 is a 2nd floor, Northwest facing unit that enjoys shade year round on the lanai that directly overlooks the cove with turtles, the heated pool, and a spectacular view of the island of Molokai in the distance as well as Humpback whales ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to Unit #101, Honokeana Cove’s only 3 bedroom 2.5 bath Townhouse! Sleeps 6. Two remodeled lanais with abundant seating give you an amazing view of a beautiful cove, filled with tropical fish and Honu (Hawaiian Sea Turtles) with ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lahaina
BEAUTIFUL REMODEL TO THIS VACATION RENTAL
Beautifully remodeled(11/22) for welcoming ambience, this newly updated second floor unit is light and bright, featuring bamboo and teak cabinets, LVP flooring, and updated furnishings for your ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
Oceanfront 2 Bedrm / 2 Bath Ground Floor Unit with Fully equipped Kitchen. Superb location within the complex . Offers spectacular views and only steps away from the Pool and the Best Snorkeling on Maui! Nestled in a private cove, with a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Lahaina
At the tip of breathtaking Napili Point, sits one of the most sought after sunset spots on the whole island of Maui. Looking out over Molokai and Lanai, A-6 is uniquely positioned to see everything Honokeana Cove has to offer, right from your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
This beautifully appointed 2nd floor condo has a King bed in the Mezzanine bedroom and a Queen bed in the lower bedroom. The lower bedroom has an air conditioning unit.
Each condo has a lanai overlooking Cove itself, the grassy lawn, the...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Welcome to Unit 112!
You'll be on the beach in no time!
About 15 minutes north of Lahaina in the Napili area. Just finished painting the entire unit. New bed as less than a year old and just added AC so you can sleep in air ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
