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 '%Hale Kona Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Aloha and welcome to HONU HIDEAWAY at Hale Kona Kai #403. This is the absolute destination should you want a relaxing vacation getaway on the Kona coast. The location of this condo complex adds convenience as fun activities and amenities are ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Hale Kona Kai unit 312 is one of our most popular units at Hale Kona Kai. This 3rd floor corner unit features a wraparound lanai facing west and south for full, unobstructed ocean views and the best possible breeze! The unit has been ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Complete and Total oceanfront condo with a lanai (balcony) that almost hangs over the beach. See the ponds below where the turtle and the eels want to come and feed. Let the sounds of the sea make you sleep after a day of sightseeing or ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Hale Kona Kai is a small complex of 3 dozen absolute oceanfront condos in town but off the beaten path! Located on a quiet side street and just a short distance to the village of Kailua-Kona also known as Kona Town. Hale Kona Kai is only 1/4 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Hale Kona Kai 405 is on the top floor of the building and is beautifully updated. Central air conditioning keeps the unit cool and comfortable, especially on those extra warm nights. Updated wifi equipment and settings provide high speed wifi, ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
True and total oceanfront condo with wide wrap-around lanai (balcony) giving you almost four lanai rooms. The lanai hangs over the ocean. See the tide pools and the lagoon where the turtles love to swim. Let the sounds of the sea make you sleep ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
AIR CONDITIONING - The Central AC at Hale Kona Kai has been repaired and is fully functional as of 8/4/23.
HALE KONA KAI UNIT 212 SPECIAL FEATURES: This unit has uv film added to the living room window and sliding glass door. The film ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
AIR CONDITIONING - The Central AC at Hale Kona Kai has been repaired and is fully functional as of 8/4/23.
HALE KONA KAI UNIT 404 SPECIAL FEATURES: The bedroom and living room are separated by sliding french doors instead of the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Ocean over lagoon front views from lanai (balcony). You are so close you can watch the waves roll onto the rocky shoreline as you enjoy the beautiful Hawaiian songbirds and monarch butterflies in the adjoining garden. Let the sound of the waves ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
True and complete oceanfront condo with a lanai (balcony) that almost hangs over the beach. See the ponds below where the turtle and the eels want to come and feed. Let the sounds of the sea make you sleep after a day of sightseeing or ...
- 2 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
True and Absolute oceanfront condo with a lanai (balcony) that almost hangs over the ocean. Any closer and you would be swimming. View the tide pools below where turtles and eels like to come to eat. Let the sounds of the sea lull you to sleep ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
Ocean over lagoon front views from the lanai (balcony), you are so close you will hear the ocean and watch the waves come sideways into the lagoon below. Enjoy watching the fish and turtles swim in the lagoon and the lighthouse off in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
True and Absolute oceanfront condo with a lanai (balcony) that almost hangs over the ocean. The tide pools below where turtles and eels like to come to eat. Let the sounds of the sea lull you to sleep after your day of sightseeing or relaxing; ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
True and full oceanfront condo with a lanai (balcony) that almost hangs over the beach. See the ponds below where the turtles and the eels want to come and feed. Let the sounds of the sea make you sleep after a day of sightseeing or relaxation. ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Real and Total oceanfront condo with a lanai (balcony) that almost hangs over the beach. See the ponds below where the turtles and the eels want to come and feed. Let the sounds of the sea make you sleep after a day of sightseeing or relaxation;...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Unit #407 is an ocean-front, comfortable, stylish, eco-conscious escape that features the newest technologies and conveniences for today's travelers.
The condo is an absolute oceanfront condo with a lanai (balcony) that offers beautiful ocean...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
AIR CONDITIONING - The Central AC at Hale Kona Kai has been repaired and is fully functional as of 8/4/23.
Find your beach with Kona Beach Properties! This oceanfront property offers a nice view of the ocean and of the beautifully ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Fully renovated in August 2024! Enjoy true comfort and class in this absolute oceanfront condo with large wrap-around lanai, providing a generous outdoor space and expansive ocean view. A stunning walk-in shower with custom tile work, open ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
AIR CONDITIONING - The Central AC at Hale Kona Kai has been repaired and is fully functional as of 8/4/23.
Hale Kona Kai 412 completed its beautiful transformation in 2023! Full interior painting.Completely remodeled bathroom with ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
True and total oceanfront condo with a lanai (balcony) that almost hangs over the beach. See the ponds below where the turtle and the eels want to come and feed. Let the sounds of the sea make you sleep after a day of sightseeing or relaxation. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
