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 '%Makahuena%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Located on the southern most point of Kauai, right between Poipu Beach State Park and Shipwreck Beach. Partial ocean view. Nicely remodeled 2 bedroom, 2 bath condo with Lanai. Master bedroom has a king bed, 2nd bedroom has 2 twin beds that can ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
E Komo Mai, Welcome to The Makahuena 2102!
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanside resort is situated on the edge of a bluff providing spectacular coastal views. This spacious 3BR/2BA ocean...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Koloa
Makahuena 5202 is one of the best oceanfront condos that sunny Poipu has to offer. The Makahuena Resort is located on the southernmost point of Kauai. This unique oceanfront resort is situated on the edge of a bluff providing spectacular coastal...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Welcome to koolkondo where you will experience luxury vacation rentals at its finest. Our stylish boutique hotel style condos and homes have it all. You will absolutely enjoy your home away from home with VIP style in modern island ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Koloa
Located on the southernmost point of Kauai, this unique oceanfront property is situated on the edge of a bluff providing spectacular views of the blue Pacific and the entire Poipu coastline from each unit. Incredible ocean views are the dominant...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
About this space
Direct Oceanfront Penthouse - All you see is WATER! NOW FULLY AIR-CONDITIONED
The space
Imagine sitting on your lanai, overhanging the lava cliffs of the southernmost point of Kauai where you can see the sunrise and the...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Private, below ground level lanai allows for relaxation on comfortable furnishings. The condominium complex is oceanfront on a rocky coastline. Our premium upgraded unit will fit your party's needs with many fine upgrades and amenities. Two ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Poipu
Enjoy your morning coffee while sitting on the Lanai with a beautiful ocean view. You may see a pod of dolphins swim by, or whales breaching in the winter months. Watch the sunrise and sunset from the Makahuena point, just steps from our ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Poipu Beach
Makahuena is a small, quiet, privately owned, condominium resort located on Poipu Point with resident manager on site. It is near the Hyatt Regency and Poipu restaurants. Poipu & Shipwreck Beaches are within 10 minute walking distance. The site ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
E Komo Mai, Welcome to The Makahuena 4205!
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanside resort is situated on the edge of a bluff providing spectacular coastal views. This 2-bd/2-ba is a ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Located in the "almost always" sunny, resort town of Poipu, on the gorgeous south shore of Kauai, this oceanfront complex is situated on the southernmost point of the island. From the edge of the dramatic cliffs, just above the crashing waves,...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Welcome to koolkondo where you will experience luxury vacation rentals at its finest. Our stylish boutique hotel style condos and homes have it all. You will absolutely enjoy your home away from home with VIP concierge style services in modern ...
- 3 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Koloa
MAKAHUENA 1204 AT KOLOA KAI VACATION RENTALS
Experience a stunning location in the heart of Poipu with this two-bedroom condo. Situated on the second floor of Makahuena at Poipu, the condo features air conditioning, to keep you cool on ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
E Komo Mai, Welcome to The Makahuena 4305!
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanside resort is situated on the edge of a bluff providing spectacular coastal views. This is a multi-level condo...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
E Komo Mai, Welcome to Makahuena 5101
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanfront resort is situated on the edge of a bluff providing spectacular coastal views. This 2-bd/2-ba condo is on the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Welcome to our beautifully renovated condo, perfectly situated to offer southeast-facing, uninhibited views of the majestic mountains, Shipwreck Rock, and the breathtaking Pacific Ocean. During the winter months, you can enjoy the awesome sight ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Poipu
Breathtaking ocean views and spacious living accommodations await you at this fabulous 3 bedroom Makahuena Condo in Poipu. Well-appointed fully equipped kitchen, accommodating up to 6 people, in a total of 1300 square feet of living space. ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Poipu
E Komo Mai, Welcome to The Makahuena 4304!
“The Makahuena” is located on the southernmost point of Kauai. This unique oceanside resort is situated on the edge of a bluff providing spectacular coastal views. This 2-bd/2.5-ba is a ...
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Koloa
Brand new AC!!
Two bedrooms upstairs with a king and a queen bed, both with their own bathroom, plus loft on the third floor, with a queen bed and a trundle bed ( 2 singles), ample lanai space, washer and dryer in unit, large kitchen, ...
- 2 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Koloa
Makahuena 5103 offers the most Amazing views of the vast Pacific blue! View turtles, winter whales, and periodic dolphin pods frolic in the surf. Makahuena 5103 is one of the best oceanfront properties on the sunny Poipu coast, perfect for ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
