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, 30
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
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 ...
- 0 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. ...
- 0 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
Welcome to our ground floor ocean front condo, “Ohana Sunset.” We are only 10 FEET from the water! Located in Kahana, between Ka’anapali and Kapalua, it is the perfect condo for a couple.
With the ocean at your back door, ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Lahaina
