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 (`uniturl` like '%9cb1c79f-2dd1-4491-93fb-d733bc94d80d%' ) AND sleeps>=1 order by rating desc LIMIT 0, 15
LICENSED VACATION HOME #90/TVU-0003
This home will not be affected by the new Honolulu County ban on vacation homes. Make sure when booking your vacation home the property that you are reserving has a TVU#
FAR FROM THE MADDENING CROWDS, RELAX IN THIS VINTAGE, CHARMING UPDATED TWO-BEDROOM BEACHFRONT COTTAGE. ONE OF FOUR BEACHFRONT HOMES IDEAL FOR FAMILY REUNIONS OR SMALL WEDDINGS.*
This home features a master bedroom (Queen), second bedroom (2 Twins), full bath with tub/shower, full kitchen and living room / dining area opening onto a large covered lanai. Air conditioning units in both bedrooms and living room. Sit and dine on the shady deck and feel the tropical island breezes. This home includes an outside Hot/Cold shower, free WI-FI Internet access, cable sports channels and HI-Def Flat Screen TV.
Fire up the grill and sip your evening drink as you view spectacular sunsets from your outdoor dining area. Step down from your deck to a generous lawn area overlooking the beach.
Take stairs down to the small sandy beach with reef and tide pools to explore; walk east 100 yards to the right and find a swimming area protected by a barrier reef; continue walking east another 100 yards to the west end of beautiful Kawela Bay.
You won’t forget your experience on the North Shore with stunning ocean views; watching whales and giant surf in the winter, swimming and snorkeling in the summer, and...
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Kahuku
