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 '%kahaluu beach%') AND sleeps>=1 order by rating desc LIMIT 0, 20
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 4
Hawaii> 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
Hawaii> Kailua-Kona
Kahaluu Beach Villas 1-203
Our calendar is updated in real-time & our nightly rates are accurately displayed. Simply enter your desired dates and click 'Book Now!'
Set right across the street from Kahalu’u Beach, this ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Kailua-Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
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 ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Located on Kona Coast in Hawaii, this tropical garden resort is less than one kilometer from Kahaluu Beach. It features a restaurant, tennis court and villas with a fully-equipped kitchen. The resort has 2 outdoor pools, 3 hot tubs and a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Located on Kona Coast in Hawaii, this tropical garden resort is less than one kilometer from Kahaluu Beach. It features a restaurant, tennis court and villas with a fully-equipped kitchen. The resort has 2 outdoor pools, 3 hot tubs and a ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> 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
Hawaii> Kailua-Kona
