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 (`headline` like '%kahaluu beach%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Love to snorkel and want to see over 100 species of tropical fish & turtles? Then why not stay right across the street from Hawaii's best snorkeling beach?! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
That home-away-from-home feel, walking distance from one of Kona’s most popular snorkel beaches, Kahalu’u Beach! Privacy plus convenience is what you’ll get in this 2 bedroom 2 bath unit. It comes complete with a full kitchen, living room,...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
Love to snorkel and want to see over 100 species of tropical fish & turtles? Then why not stay right across the street from Hawaii's best snorkeling beach?! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
Wonderful ocean views. Across the street from Kahaluu Beach, one of the best snorkeling beaches on the Big Island. One bedroom, one bath, fully furnished, second floor condo. Lovely pool and BBQ area. Private Lanai for outdoor dining, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Beautiful and spacious 2 bed, 2 bath, corner unit condo with ocean views is located on the 3rd floor and has 1,475 sq. ft. of living area. The condo, with tasteful island design, has indoor/outdoor dining, 4 tiled lanais with spectacular ocean, ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Be inspired on your next trip to the Kona Coast when you stay at this fabulous 2-bedroom, 2 bathroom vacation rental condo. At. Home comfort and convenience fill the space. The condo is comfortably decorated and ready to accommodate four ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Kahaluu Beach Holmes is a vacation rental located 300 feet from Kahaluu Beach Park, a premier snorkeling beach. It is the 3rd floor of a large home that is tasteful furnished, clean and spacious. The view is truly spectacular!
The 3rd floor ...
- 2 bedroom
- 2 bathroom
- Sleeps 7
HI> Kailua-Kona
Your adventure to the Kona Coast starts at this oceanfront Big Island property, just steps away from the sun, sand, and surf. Guests will enjoy spectacular dining options, including a waterfront restaurant and poolside bar. The on-site spa and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
