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, rewritten_description,
(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, 50
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
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kailua-Kona
SEE the SEA @ Sea Village Resort
Escape to Hawaii for a great vacation !!
Great condo , Great Location, Great Rates
Free onsite parking, gated oceanfront pool/hot tub (SLEEPS 2 MAXIMUM)
Aloha and welcome to a special place aka
- 1 bedroom
- 1 bathroom
- Sleeps 2
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. MIni-split A/C units were recently added to both bedrooms, along with the stand alone A/C unit in the living room. Lanai seats four for outside dining with fab views of
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Oceanfront Escape with A/C | Modern 2BR/2BA Condo in Kona – Sea Village 1105
Your dream Hawaiian getaway starts here at Sea Village 1105, a newly upgraded 2-bedroom, 2-bathroom oceanfront condo in the heart of Kailua-Kona. Now featuring
- 3 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
Summary:
This 2 bedroom/2 bathroom home features stunning ocean views. Bring the whole family as the unit can accommodate up to 4 guests.
The Space:
Watch the whales or enjoy a Mai Tai while relaxing on your private lanai. Sea
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
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 enjoying views of the sparkling ocean. Beautiful ocean
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Summary:
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 home. PLEASE BE AWARE THERE IS NO AC IN THE UNIT. Maximum occupancy
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Unwind with modern comforts, including a flat-screen TV with cable, high-speed WiFi, and a fully equipped kitchen perfect for preparing island-inspired meals. Step outside to soak up the sun in the oceanfront complex, where you can lounge by the
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to Sea Village 3304 – Your Oceanfront Oasis on the Kona Coast!
Experience the ultimate Hawaiian escape at Sea Village 3304, a beautifully appointed oceanfront property offering stunning views and unparalleled relaxation. This
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
