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 '%Alihi Lani%') AND sleeps>=1 order by rating desc LIMIT 0, 20
ALIHI LANI. This small, 6 unit complex is a 'Real Gem', according to the Ultimate Kauai Guidebook. Enjoy endless views from our oceanfront pool. The front landscaped area also has two gas grills and comfy lawn furniture.
OCEANFRONT. Our ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Koloa
May we welcome you to Alihi Lani at Poipu. Our beautiful air conditioned condo located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Poipu
ALIHI LANI. This small, six unit complex called a 'Real Gem' by the Ultimate Kauai Guidebook. Enjoy endless views from our oceanfront pool. The front landscaped area also has two gas grills and comfy lawn furniture. We are named 'Premier Hosts' ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
You will love this Beautiful Ocean View! The fully equipped kitchen has everything you will need. There is plenty of beach gear for your Kauai outdoor adventures. Meticulously manicured landscaping and tropical flowers abound. For additional ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Koloa
May we welcome you to Alihi Lani at Poipu. Our beautiful, condominiums located in the heart of Kauai's sunny south shore! Alihi Lani. or 'Beautiful Horizon Views', aptly describes the magnificent ocean panorama of Hawaiian sunsets, ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Poipu
You will love the expansive and breath taking Ocean View, and the luxurious air conditioned home. Alihi Lani is a private boutique condo community. The home is ground floor, and a corner unit. This spacious condo with two master suites and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
