select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 AND (`headline` like '%Kuleana%') AND sleeps>=1 order by rating desc LIMIT 0, 15
The Kuleana Resort is located between the West Maui mountains and the ocean on the gold coast of Maui. Unit 411 is a one-bedroom, second-floor oceanfront unit amidst lush gardens. It is newly remodeled with granite counter tops and travertine ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lahaina
This most sought after location in the complex is a ground floor corner unit offering privacy and just 10 paces away from one of the two almost private beaches, where you may find yourself alone.(Be aware that the beaches are subject to tidal ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lahaina
--Coronavirus Cancelation note: If you book our condo and need to cancel, due to the Coronavirus pandemic restrictions, you will be refunded all of your payments up until your scheduled arrival date. Once we get through this difficult ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
The Kuleana Resort, the most beautiful property location in the world . Two ground floor side-by-side condominiums. Rent 1one or rent both to take advantage of the adjoining passage door . See Listing 77031 and 77036 for adjoining condo.
This ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
The Kulean Resort, the most beautiful property location in the world. Rent one or rent both of our side-by condos to take advantage of the adjoining passage door. See Listing 77031 for adjoining condo.
This is the best location on Maui. From...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Lahaina
The views from this remodeled and upgraded (April, 2013- Present) ground floor direct OCEANFRONT unit are spectacular, with the clear blue-green ocean vista and radiant sunsets over the islands of Lana'i and Moloka'i. Step off your lanai and you...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
The views from this recently remodeled and continuously upgraded third/top floor direct OCEANFRONT unit are spectacular, with the clear blue-green ocean vista and radiant sunsets over the islands of Lana'i and Moloka'i. From the lanai, you are ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
Whether you are looking for a "private" beach, a swim in a heated pool or just want to take a few steps out to a lanai that almost reaches the beach, you will find it in our condo. You can sit on the lanai and watch the whales frolick (during ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
Beautiful - Oceanfront - Ground Floor - Walk Out
Fabulous updated oceanfront condo location: Kuleana 605 is right on the ocean on Maui's pristine West Coast with gorgeous panoramic views of the Pacific Ocean and the lovely off-shore islands of ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
Relaxation at its finest with this remodeled and refurnished 1 bedroom, 1 bathroom oceanfront condo. Just mere steps from the water, this truly exceptional suite offers spectacular unobstructed ocean and sunset views from the lanai and living ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lahaina
Off grid luxury. Powered by state of the art photovoltics solar hot water. Surrounded by thousands of acres of DNLR land. Super quiet and private. Complete with granite counters, open beam celings, tiled shower, mountain and ocean views. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Naalehu
SEE OUR VIRTUAL TOUR BELOW! THIS UNIT SITS RIGHT ON THE WATER. Its a Corner second floor end unit OUT ON A POINT. The six foot picture window to your left views open ocean and the beach 50 feet away.
The lanai straight ahead views the islands ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Lahaina
Beautiful - Oceanfront - Ground Floor - Walk Out
Fabulous updated oceanfront condo location: Kuleana 607 is right on the ocean on Maui's pristine West Coast with gorgeous panoramic views of the Pacific Ocean and the lovely off-shore islands of ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kahana
Summary:
Building 5. Unit 516. Kuleana provides the perfect combination of relaxation and recreation within a 4 acre oceanfront and garden setting. Pool, BBQ's, and putt putt green. Incredible snorkeling/diving in front of resort with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
Enjoy the luxury of this beautiful oceanfront condo including all brand new furniture! You will find travertine floors, granite counter tops in the kitchen, an island bar, and bathroom with a custom walk in shower. There is a luxurious King Bed ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Lahaina
