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 (`address` like '%75-6002 Alii Dr%' ) order by rating desc LIMIT 0, 15
Newly renovated Two Bedroom Two Bath Condo with Full Ocean View in Center of Complex on 2nd Floor on the Kona Coast. Corner Unit
Wifi equipped
...
-
1
bedroom
-
2
bathroom
-
Sleeps 5
HI>
Kailua-Kona
Welcome to Paradise!
Located in the beautiful sea village condo complex (unit 3216). We also own the 1 bedroom condo (unit 3217) right beside...
-
2
bedroom
-
2
bathroom
-
Sleeps 6
HI>
Kailua-Kona
Welcome to Paradise!
Located in the beautiful The Sea Village condo complex (unit 3217). We also own the 2 bedroom condo (unit 3216) that is ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Kailua Kona
Gorgeous oceanfront ground floor condo with a large private lanai set directly over the ocean! Enjoy ocean breezes all day long in this ...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Kailua Kona
What do you get when you combine unrivaled oceanfront views from a private lanai with gorgeous, high-end interiors? You get Sea Village 4207 and ...
-
2
bedroom
-
2
bathroom
-
Sleeps 4
HI>
Kailua-Kona
Probably one of the finest direct ocean views in all of Kailua-Kona. This top floor unit looks straight over the ocean and Kailua Bay. Watch the ...
-
2
bedroom
-
2
bathroom
-
Sleeps 4
HI>
Kailua-Kona
Spectacular Ocean front condo in highly desirable location. Magnificent sunsets from poolside/hot tub or your private lanai. "Shorefront is rocky...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Kailua-Kona
Stunning oceanfront views from the lanai, the sounds of the surf on the rocks, and the spirit of Aloha welcomes you to our home in Hawaii.
Sea ...
-
2
bedroom
-
2
bathroom
-
Sleeps 5
HI>
Kailua-Kona
Fantastic oceanfront unit. Enjoy watching dolphins, whales, and the beautiful sunset from a very private lanai or the calming sound of the surf ...
-
2
bedroom
-
2
bathroom
-
Sleeps 5
HI>
Kailua-Kona
Sea Village Resort sits right on the sea wall. Located on prestigious Alii Drive. The well kept grounds surround a newly renovated pool, spa, and...
-
1
bedroom
-
1
bathroom
-
Sleeps 4
HI>
Kailua Kona
Crashing waves and amazing sunsets from this corner end unit with views of the Pacific Ocean from both lanais off of the living room and the ...
-
2
bedroom
-
3
bathroom
-
Sleeps 6
HI>
Kailua-Kona
Our unit is nicely decorated and has been remodeled throughout. From the Lanai, which overlooks the Pool and Jacuzzi, you can watch cruise ships ...
-
2
bedroom
-
2
bathroom
-
Sleeps 6
HI>
Kailua Kona
Top Floor, Direct Ocean Front View, Close to Town, Upgraded
This 2 bedroom 2 bathroom condo is located on the top floor & is direct ocean front....
-
2
bedroom
-
2
bathroom
-
Sleeps 4
HI>
Kailua Kona
Welcome to Sea Village 1307! Experience Ocean Front Living, the pictures tell the story.
The property owner's favorite thing about the condo is...
-
2
bedroom
-
2
bathroom
-
Sleeps 4
HI>
Kailua Kona
This amazing 2 bedroom direct oceanfront condo is the PERFECT spot for your Vacation. Completely remodeled, extremely clean, the owners have done...
-
2
bedroom
-
2
bathroom
-
Sleeps 6
HI>
Kailua-Kona
