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 '%Sea Village%') AND sleeps>=1 order by rating desc LIMIT 0, 30
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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> 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,...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
Imagine - warm tropical climate, views and sounds of the ocean just out your door, panoramic views, and the perfect accommodations - is there anyway else to experience Hawaii? This two-bedroom, two-bath condominium gives you all of that and ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> 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
Hawaii> 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
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Beautifully decorated upper floor ocean front unit with modern upgrades. Enjoy watching the famous Kona Sunsets from your own private lanai or just nap by the sound ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Turn moments into memories in this incredible oceanfront Sea Village #3212 property. This two-bedroom, two-bathroom has all one is looking for. Enjoy the open layout of the living area and stunning ocean views from the kitchen to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Incredible ocean front location with gorgeous views of the Kona Coast. Enjoy the private lanai while you take in the ocean activities or dine while watching the ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances. Please be aware that there is no ac in unit.
This 2 bedroom/2 bathroom home features stunning ocean views. Bring the whole family as the unit can accommodate up to ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Listen to the ocean, watch the whales or enjoy a Hawaiian drink while relaxing in your very own deluxe oceanfront townhome! Located at the end of building 4 in the ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
Awesome location with a private lanai situated right over the ocean! This very well kept 1 bedroom 1 bath unit boasts comfort in paradise, with all the amenities of ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
About this space
Renovated 2-bedroom 2 bath condo. Make the meal of your choice in full kitchen and sit on the lanai enjoying the ocean and sunset view.
Pool, hot tub, and BBQ grills available. You can watch the dolphins, skydivers and ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
