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 '%Hale Kona Kai%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Aloha and welcome to HONU HIDEAWAY at Hale Kona Kai #403. This is the absolute destination should you want a relaxing vacation get-away on the Kona coast. The location of this condo complex adds convenience as fun activities and amenities are ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua-Kona
Our central Air Conditioning in the entire complex is currently not functioning, we do not have a firm date yet for it to be fixed. Due to this inconvenience, we are offering a 20% discount for the dates that you stay where the central air ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua Kona
True and absolute oceanfront condo with large wrap around lanai (balcony), affording a huge lanai that sits on top of the ocean. The view stretches from the Kaiwi Point and the Kailua Pier to the north, all the way to Ka'awaloa Point at ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
THE CENTRAL AC AT HALE KONA KAI HAS BEEN FULLY RESTORED AND IS FULLY FUNCTIONAL AS OF FEBRUARY 8, 2022.
True and absolute oceanfront condo with large wrap around lanai (balcony), affording almost 4 times the lanai space of other condos at ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
Hale Kona Kai 212 has a wraparound lanai that sits at the very edge of the ocean! This oceanfront property offers unobstructed views of the Kona coastline. The ocean is teeming with life! Sit on your lanai and watch spinning dolphins year ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
THE CENTRAL AC AT HALE KONA KAI HAS BEEN FULLY RESTORED AND IS FULLY FUNCTIONAL AS OF FEBRUARY 8, 2022.
True and absolute oceanfront condo with large wrap around lanai (balcony), affording a huge lanai that sits on top of the ocean. The view...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua Kona
Resort, Huggos and a short walk to town, we have heated pool, elevator and BBQ area.
Central Air Conditioning.
Hale Kona Kai offers a heated salt water pool, elevator and BBQ area. In-condo wifi, & cable TV.
Hale Kona Kai is a small ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Complete and Total oceanfront condo with a lanai (balcony) that almost hangs over the beach. Central Air Conditioning. 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
True and Absolute oceanfront condo with a lanai (balcony) that almost hangs over the ocean. Central Air Conditioning. View 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
THE CENTRAL AC AT HALE KONA KAI HAS BEEN FULLY RESTORED AND IS FULLY FUNCTIONAL AS OF FEBRUARY 8, 2022.
Find your beach with Kona Beach Properties! This beautifully upgraded oceanfront property offers unobstructed views of the Kona coastline....
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua Kona
True and total oceanfront condo with wide wrap-around lanai (balcony) giving you almost four lanai rooms. Central Air Conditioning. The lanai hangs over the ocean. See the tide pools and the lagoon where the turtles love to swim. Let the sounds ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
True and Absolute oceanfront condo with a lanai (balcony) that almost hangs over the ocean. Central Air Conditioning. View 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
Ocean over lagoon front views from lanai (balcony). Central Air Conditioning. You are so close you can watch the waves roll into the rocky shoreline as you enjoy the beautiful Hawaiian songbirds and monarch butterflies in the adjoining garden. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
THE CENTRAL AC AT HALE KONA KAI HAS BEEN FULLY RESTORED AND IS FULLY FUNCTIONAL AS OF FEBRUARY 8, 2022.
Find your beach with Kona Beach Properties! This oceanfront property offers a nice view of the ocean and of the beautifully landscaped ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
THE CENTRAL AC AT HALE KONA KAI HAS BEEN FULLY RESTORED AND IS FULLY FUNCTIONAL AS OF FEBRUARY 8, 2022. This unit also has built-in climate control, which is a big plus for summer months!
Find your beach with Kona Beach Properties! This ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kailua Kona
