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 '%50045faa-9550-4d65-9c0c-9155e880e657%' ) AND sleeps>=1 order by rating desc LIMIT 0, 15
This modern beachfront home was recently fully renovated. Hale Ekahi (One) is one of two properties on this beachfront lot. Ekahi offers modern beach furnishings to fill this luxurious beachfront home. The home has 3 bedrooms, 2.5 bathrooms, a fully equipped gourmet kitchen, formal dining room table, casual outdoor dining on the lanai, air conditioning, and high-speed internet. There are two homes on the property that are available to rent individually and experience a beautiful private escape for your oceanside adventure or you may want to rent them both for a family reunion, small business conference, or group of friends.
General Amenities Include:
Air Conditioning in all bedrooms
Ocean views from living room, kitchen and master bedroom
Beachfront - located on a stunningly beautiful tropical beach with swimming and snorkeling
Spacious raised lanai (covered porch) with full ocean views and BBQ
Hot tub on lanai
Lush landscaping, walking paths and garden reading area
Beach chairs, mats, children's beach toys
Outdoor shower with hot & cold water for rinsing after the ocean
Ping Pong table
Bed, Bath & Kitchen Amenities Include:
Private master suite with ocean views and double vanity
One king bed, two queens, all with high count cotton sheets
Quartz countertops throughout
Sub-Zero refrigerator/freezer,...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Hanalei
