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
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
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
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
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
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Poipu Beach
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 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
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, ...
- 3 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
Makahuena at Poipu 2104: Toast a perfect day with Kauai rum Mai Tais and farmers market treats on your ground floor ocean view lanai flowing to landscaped lawns and the Pacific. Comforts include an open kitchen, cool wood floors, washer/dryer, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Poipu
Makahena at Poipu unit #4303: Go with the flow in this two-level, 1,380 square-foot, three-bedroom, two-and-a-half-bath haven. Pull up a barstool and watch your designated chef prepare your feast in the morning as you enjoy your coffee from your...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> POIPU
Makahuena at Poipu unit #2204 on the 2nd-floor: This ocean-view space is absolute bliss! The living room, dining area, and kitchen all enjoy the ocean view, as does the master bedroom. The living room and both bedrooms are cooled by ceiling ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> POIPU
