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 '%poipu makai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Words cannot describe the incredible view from this penthouse condo. It's mesmerizing!! You'll hear the waves crash below you along the coastline and marvel at the Pacific Ocean stretching out as far as you can see. Turtles, spinner dolphins, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> koloa
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. This small property has only 15 units, providing privacy and intimacy while being ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
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. This small property has only 15 units, providing privacy and intimacy while being ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
This beautiful, privately owned condominium is absolutely perfect for the Kauai-bound traveler. Here you can greet and close each day from your private lanai with spectacular ocean views. The Poipu coastline. Spouting Horn. The island of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Poipu
Enjoy this gorgeous condo with contemporary furnishings on the second floor with unbelievable views of the Pacific ocean. Watch sea turtles, playful dolphins, and giant humpback whales from your living room and Lanai. Colorful sunsets are ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Poipu
Poipu Makai E-1 is situated on a slight bluff for maximum ocean viewing. It is completely remodeled and includes sound mitigation between the ceiling and floor of the neighboring unit for quiet enjoyment. Some of the many features include:
...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Koloa
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. This small property has only 15 units, providing privacy and intimacy while being ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
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. This small property has only 15 units, providing privacy and intimacy while being ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
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. This small property has only 15 units, providing privacy and intimacy while being ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Koloa
