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, 50
Escape to paradise in this gorgeous 1-bedroom, direct oceanfront condo, the perfect spot for your vacation! Enjoy breathtaking ocean views and the serene beauty of the Big Island from the comfort of your fully remodeled unit, located on the 2nd ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
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
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...
- 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:
Incredible ocean front location with gorgeous views of the Kona Coast. Maximum occupancy strictly enforced in accordance with local ordinances.
The Space:
Experience the ultimate comfort and relaxation in our revamped ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
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
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
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
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> Unknown
