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 '%2a35220e-02eb-4c4f-b03a-e959fdeec0a8%' ) AND sleeps>=1 order by rating desc LIMIT 0, 15
Elegant and spacious King Hale, set beachfront in a lush tropical jungle, is located in the serene community of Haena, just 4 miles north of Hanalei. Here you will discover beautiful stretches of white sand beaches and the most breathtaking of sunsets.
This 4BD/4BA eco-friendly home is built around an elevated open floor plan that is ideal for family gatherings or gracious entertaining. The fully-equipped gourmet kitchen with top-of-the-line appliances and granite countertops is perfect for intimate meals or large dinner parties. Off the kitchen, a large, round dining table with seating for 8 overlooks the blue Pacific Ocean. Oversized sliding doors to the lanai allow for seamless indoor/outdoor living and capture the gentle tradewinds blowing in off the sea. The living room has two large separate seating areas, a wall mounted flat screen television, a fireplace, and floor to ceiling windows that illuminate the tropical garden and beach beyond. Electronic amenities include DVD/CD players, Home has TV internet (so guests can use their own Netflix account, etc.), Wireless High Speed Internet, and iPod docking stations. the house is eco-friendly with extensive solar, battery backup and near zero carbon-footprint.
There are 3 air conditioned bedrooms on the main upper floor of the home. Two of the rooms are master suites with King beds, flat screen TVs, and ocean views. Both have en suite bathrooms that feature large walk-in showers and soaking tubs. The...
- 4 bedroom
- 4 bathroom
- Sleeps 10
HI> Kapaa
