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 '%Sea Village%') AND sleeps>=1 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
Ceiling Fans
Portable Air Conditioner in living room
Enjoy Dophins spinning and whale watching in ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
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 Village is a beautiful oceanfront complex with well maintained gardens, swimming pool, hot tub, gas ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kailua-Kona
Fantastic oceanfront condo. Enjoy watching dolphins, whales, and the beautiful sunset from a very private lanai, or listen to the calming sound of the surf while sipping a cup of Kona coffee at breakfast. Feel the soothing ocean breeze. As of...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kailua-Kona
Your ideal Hawaiian vacation begins at Sea Village 1105. Every amenity is at your fingertips in this recently remodeled two-bedroom, two-bathroom condominium, which comfortably accommodates up to four people. Sip Kona coffee from the lanai while...
- 2 bedroom
- 2 bathroom
- Sleeps 6
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 one amazing Hawaiian vacation! Bring your ohana – family – and experience the Big Island of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Lovely oceanfront 2nd floor unit totally upgraded and showing beautifully. Lanai seats four for outside dining with fab views of Kona Bay and those unforgettable Kona Sunsets. Very popular vacation complex with Oceanfront Pool and Hot Tub, BBQ ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua Kona
Covid-19 Notice: Available amenities vary at each Complex, many have restricted use. Please inquire with KCV.
This bright cheery and completely renovated two-bedroom, two bath condo is a short distance to downtown Kailua Kona on the Big Island...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
Our privately owned unit is within Sea Village, a Condominium complex less than 1 mile from downtown Kailua-Kona. We love this location because it's in between the great surfing and snorkeling to the south, and the white sand beaches to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
HI> Kailua-Kona
