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 '%Pohailani%') AND sleeps>=1 order by rating desc LIMIT 0, 50
The Pohailani 113 is a special place where you can enjoy the sound of the waves and the occasional ocean spray from the comfort of your own lanai. Watch sea turtles swimming by and catch a glimpse of passing whales during whale ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
Ocean Front Studio in Kahana. Remodeled and ready for occupancy! This condo is so close to the ocean you can hear and smell the ocean. Whale watch or view the large sea turtles from our lanai. Sleep like a baby on our Westin Heavenly King bed. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
State of HA Licenses/Certification GE/TA #023-115-9808-01
Gorgeous Plumeria Tree next to Lanai!!
This quality 2 bedroom, 1 bath cottage with spacious lanai opens to beautiful tropical setting. The 13 foot high vaulted ceilings provide a ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Lahaina
Close to sailing, scuba diving, and more, this smoke-free condo brings it all within reach. Thanks to the convenient onsite parking, you can either leave your vehicle behind and let your feet make short work of the 13-minute walk to Kahana ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
The true spirit of aloha will long endure in your memories after your stay in this ground floor oceanfront Pohailani studio. Located just 15 feet from the ocean on the first floor of a two-story building, you will enjoy panoramic views of the ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kahana
