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 '%Kuleana%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
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
Hawaii> Lahaina
Aloha and welcome to Kuleana Green Cottage!
If you're looking for a serene getaway in the heart of Hawaii, our newly renovated cottage is the perfect destination for you. With a refreshing private pool and lush greenery surrounding the ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Hilo
Newly remodeled OCEAN FRONT one bedroom, ground floor condo. Enjoy breathtaking nightly sunsets while sipping your favorite beverage from your private lanai. This fully equipped condo features a custom kitchen with granite counter tops and a ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Summary:
Located on Maui's tropical west coast, the Kuleana Club has a panoramic view of the Pacific and the lovely offshore islands of Molokai and Lanai. This unit is a 3rd floor ocean view condo, only steps to the ocean, surrounded by a ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> 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
Hawaii> 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
Hawaii> Naalehu
Kuleana is perfectly located between Kaanapali and Kapalua
Ocean FRONT, 1 bedroom, 1 bath condo
Spacious lanai
Swimming Pool
Tennis
BBQ Area
Air conditioning
Free parking and NO Resort Fees!
Step into paradise at this...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Perfect gem!! oceanfront.
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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kuleana is perfectly located between Kaanapali and Kapalua
Ground Floor Ocean Front, 1 bedroom, 1 bath condo
Lanai just steps to the water
Swimming Pool
Tennis
BBQ Area
Air conditioning
Free parking and No Resort ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kuleana is perfectly located between Kaanapali and Kapalua.
Ocean Front, 1 bedroom, 1 bath condo.
Private lanai
Swimming Pool
Tennis
BBQ Area
Air conditioning
Free parking and NO Resort Fees!
Take a breath of fresh ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Summary:
Building 5. Unit 516. 3rd floor. Kuleana provides the perfect combination of relaxation and recreation within a 4 acre oceanfront and garden setting. Pool, BBQ's, and putt putt green. There is no elevators in the Kuleana community. ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahana
When you arrive at our condo, you will know that you have reached paradise! Kuleana is a low rise condominium complex built around serenity. As an OCEAN FRONT property, this unit is on the ocean front side. The property is located near Kahana, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
Kuleana is perfectly located between Kaanapali and Kapalua.
Ocean FRONT, 1 bedroom, 1 bath corner unit.
Spacious lanai
Swimming Pool
Tennis
BBQ Area
Air conditioning
Free parking and NO Resort Fees!
This beautiful ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Kuleana is perfectly located between Kaanapali and Kapalua
Ocean Front, 1 bedroom, 1 bath condo
Lanai just steps to the water
Swimming Pool
Tennis
BBQ Area
Air conditioning
Free parking and NO Resort Fees!
Kuleana 713...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Lahaina
