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 (city = 'Wainiha' OR postalCode = 'Wainiha' OR region like '%|Wainiha|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
This entirely renovated private oceanfront view retreat on the North Shore of Kauai in Wainiha on the edge of Hanalei is perfect for short- or long-term getaways for family or friends seeking luxurious accommodations with breathtaking views. ...
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Wainiha
Hanalei Colony Resort # G1 condominium has a semi open floor plan with sleeping for 4 people, 2 full bathrooms with 1 tub. Bedding consists of 1 King Bed, and 2 Twin Beds. It is situated right on the beach on Kauai's unspoiled north shore. And ...
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Wainiha
This incredibly special home situated on nearly half an acre, perched among the trees, and with over 200 feet of frontage along the Wainiha River allows for privacy and serenity during your stay while providing all the amenities you’ll need. ...
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Wainiha
This spacious, renovated home is nestled on the lush, tropical north shore of the "Garden Island" of Kauai. Wainiha Palms is down a long driveway, surrounded by a big lawn with many mature fruit trees on more than 3/4 acre of land. Enjoy this ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wainiha
Traveling north past Hanalei, you enter the dense jungle and intense blue waters of Kauai's far north shore. There are no words that can describe nature's beauty in this region. Many beaches, including tunnels and ke'e, are in the area. Trail ...
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Wainiha
