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 '%Kahana Reef%') AND sleeps>=1 order by rating desc LIMIT 0, 20
SPECTACULAR!! HIGHLY desirable third floor condo!! Enjoy beautiful, unobstructed, panoramic ocean views from your own spacious and private lanai. Listen to the gentle sounds of the ocean at this DREAM get-away.Unbeatable location right on the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
**Due to the uncertainty of West Maui Vacation Rentals, we have blocked out all available dates until the end of 2024. We will update once a decision is made by the Governor of Hawaii**Welcome to Kahana Reef #215. You will enjoy the serenity of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Completely remodeled Kahana Maui vacation condo with direct oceanfront views. Updates include new marble floors throughout (excluding bedroom), kitchen cabinets and appliances, imported Brazilian granite countertop, marble bathroom, carpeting in...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Welcome to our newly remodeled, ocean front Maui condo Kahana Reef 406! This popular top floor, one bedroom, one bath condo literally sits a few feet from the ocean, has spectacular unobstructed views, and sleeps four.
Enjoy the private ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kahana Reef 120 has an unbeatable location just yards away from the Pacific Ocean. Enjoy beautiful, unobstructed, panoramic ocean views from your own spacious and private lanai. View the neighboring islands of Lanai and Molokai against the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Deluxe remodeled vacation condo with ocean front views. Face-lift in Summer '12 included remodeled bathroom with tiled walk-in shower, new cabinetry and granite counters. Carpet replaced with new tile throughout condo including lanai. Bring your...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Kahana Reef 217 is an oceanfront condo with an unbeatable location just yards away from the Pacific Ocean and featuring all the comforts of a home away from home. Enjoy beautiful, unobstructed, panoramic ocean views from your own spacious and ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Take your Maui vacation to the next level with this newly remodeled ground-floor deluxe suite with all the space and upgraded amenities for the perfect retreat in paradise. Adorned with a beachy, island aesthetic, guests enjoy all the comforts ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Hurray...after three years, construction on the sea wall is complete with new landscaping, new steps to the ocean and beach. It looks beautiful. Also, because of COVID-19 we have updated our cleaning process to include complete sanitation of ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Gorgeous Oceanfront Kahana Reef 205, it doesn't get any better! This renovated unit features magnificent Pacific Ocean, Molokai, and Lanai views; what Hawaiian getaways are made of!Kahana Reef is a quaint oceanfront property centrally located in...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Indulge in the ultimate oceanfront retreat at this stunning second-floor condo with expansive views of the Pacific Ocean! Relax and enjoy a beverage on your lanai and let the spectacular Maui sunsets take your breath away as you soak in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Ocean Front Views!! Gorgeous Ocean Front views from this one-bedroom Condo. This unit sleeps up to four people with a king bed in the bedroom and a pull-out sofa bed in the living room. The furnishings are a mixture of rattan with tropical ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> LAHAINA
Located ground floor and on the ocean front in Kahana, this 1 bedroom, 1 bath fully equipped condo has all you will want and or need for the quiet Maui vacation. The living room contains a window/portable a/c . Walk right out to the ocean front...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kahana Reef 315! The views are never-ending here! Highly desirable third floor view condo on the Kahana Coast of beautiful west Maui. Appreciate beautiful, unobstructed, panoramic ocean views from your own spacious and private lanai. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Modern and contemporary designs, furnishings and accents complement this completely renovated, and newly updated, Kahana vacation rental. No detail was missed in this well-appointed condo. The fully equipped kitchen is completely open to the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahana
Direct oceanfront views of the Pacific Ocean and the neighboring island of Molokai from this ground-floor one-bedroom vacation rental located in Kahana area of W. Maui. This vacation condominium features a remodeled kitchen and bathroom with ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahana
Stunning central Maui location! Close to many world-class beaches and snorkel spots including Kaanapali Beach and Napili, Kapalua, and Honolua Bays Just minutes to a number of great restaurants. The quiet condominium is one of only 88 units in 3...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Experience the tranquil charm of our direct oceanfront Kahana Reef unit, ideally located on the top floor. Enjoy breathtaking views of the island of Molokai and Lanai and enjoy Maui's stunning sunsets from your private lanai. During whale ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Amazing views from this top-floor oceanfront condo. Enjoy views of the island of Molokai and expansive views of the sparkling blue Pacific Ocean right from your private balcony overlooking the horizon. This beautifully furnished condo features ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahana
Enjoy your whale song, oceanfront at Kahana located in the center building.
If you're working remotely, bring your laptop and wireless card and surf the internet at no charge. Stay out late and let the answering machine take your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
