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 (registration_number like '%TA-%' or registration_number like '%TA %') AND (city = 'Ewa Beach' OR postalCode = 'Ewa Beach' OR region like '%|Ewa Beach|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
RARE - WIDE SANDY - PRISTINE BEACHFRONT! DO NOT CLICK ON THE "Book Now" as we want to converse with you first to make sure our beach house is a good fit for your family and the dates you desire. You must send us a note about your family/group ... Registration Number: GE-023-026-2784-01, TA-023-026-2784-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Ewa Beach
Aloha! Beach themed home in west Oahu! Located next to many golf courses (7 courses within 4 mile radius), including the prestigious, Ernie Els designed, Hoakalei Country Club. Registration Number: TA-020-099-9424-01, GE-020-099-9424-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Ewa Beach
Brand new luxury beachfront cottage with private section of sand for your personal use. 3 bedroom 2 bath professionally decorated. Sleeps 6. King size master bedroom looks out at the ocean and beach. View of beach from Master bedroom, ... Registration Number: GE-134-978-5600-01, TA-134-978-5600-02, 910250040000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ewa Beach
Hale O Hoakalei is you home away from home. The whole group will love the large open spaces, private gym and golf course views. Perfect for hosting little get together or just hanging out with your loved ones. Just a quick walk to restaurants, ... Registration Number: 1911460060000, GE-003-804-9792-01, TA-003-804-9792-01
6 bedroom
5 bathroom
Sleeps 8
Hawaii> Ewa Beach
Relax in this charming 2BR/1BA retreat in Ewa Beach, where comfort meets convenience. Only a 2-minute walk to the sandy beach, this updated home is perfect for relocating professionals or project teams. Take advantage of ample parking, dedicated... Registration Number: GE-140-425-7792-01, 320200060000, TA-140-425-7792-01
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Ewa Beach
Life is a breeze when you stay at this Ewa Beach vacation rental! Located less than a half-mile from the beach, this 3-bed, 2-bath apartment makes it easy to soak in the sunbeams. Practice your swing at Kapolei Golf Club one day, satisfy your ... Registration Number: 1910080130001, GE-119-842-5600-01, TA-119-842-5600-01
4 bedroom
2 bathroom
Sleeps 7
Hawaii> Ewa Beach
Aloha and welcome to Hale Kai Ewa,Registration Number: 910240260000, GE-142-020-5056-01, TA-142-020-5056-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ewa Beach
Registration Number: TA-146-161-6640-01, 191023017000, GE-146-161-6640-01
1 bedroom
2 bathroom
Sleeps 4
Hawaii> Ewa Beach
NotesRegistration Number: 910240110000, GE-207-438-4384-01, TA-207-438-4384-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ewa Beach
Located on the best part of Ewa Beach, on the southwest side of the island, this 4bd/3.5ba home boasts Diamond Head, Ocean, City Lights, Mountains, Sunrise, and Sunset views all on a pristine, white sandy beach! This beachfront home is well ... Registration Number: 910050030000, GE-199-052-6464-01, TA-199-052-6464-01
5 bedroom
4 bathroom
Sleeps 12
Hawaii> Ewa Beach
Enjoy the island lifestyle in this beautiful beach house that sits on the Pacific Ocean. Relax on the lanai and look out at the waves as the sea turtles pop their heads up for air. As you swing on the lanai with a cup of coffee, take a breath ... Registration Number: TA-035-252-4288-02, GE-035-252-4288-02
3 bedroom
3 bathroom
Sleeps 10
Hawaii> Ewa Beach
Aloha! This is a modern single home for your family. This is a 2 story single home with 2 bedrooms/2.5 bathrooms with golf course view in your backyard central AC. It comes with upgraded luxury vinyl flooring, stainless appliances, entertainment... Registration Number: 260190010044, TA-054-288-7936-01, 264440252235, GE-038-370-7136-01
2 bedroom
3 bathroom
Sleeps 4
Hawaii> Ewa Beach
The house is located in the premium neighborhood of Ewa Beach (25 min drive to HNL Airport) with walking distance to a Pu’uloa Beach and 7 min drive to Kapilina Beach (manmade laguna beach on Navy land but fully open to public with volleyball ... Registration Number: 910371490000, GE-026-129-3568-01, TA-026-129-3568-01
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Ewa Beach
Welcome to Paradise! Registration Number: 910080560000, TA-045-019-4220-01, GE-045-019-4220-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Ewa Beach
This unique place has a style all its own. Newly built unit located near the beach and golf courses, above a yoga studio with yoga classes included with your stay makes a wonderful yoga surf golf retreat on the beautiful island of O’ahu. ... Registration Number: 910300380000, GE-124-590-3360-01, TA-124-590-3360-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Ewa Beach
Newly Remodeled 1942 Beachfront Luxury CottageRegistration Number: GE-163-994-1632-01, 910050170000, TA-163-994-1632-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Ewa Beach
One bedroom apartment located in Ewa Beach. Enjoy the “local” side of the island in this newly remodeled unit located 5 minutes from the beach. Free parking on site with washer and dryer in unit! Kitchen is fully stocked for all of your ... Registration Number: GE-014-497-7920-01, 910300450000, TA-014-497-7920-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Ewa Beach
Tucked away from the tourist traps of Waikiki, our West Oahu abode is a renovated plantation-style home with several modern updates.Registration Number: GE-093-244-4672-01, TA-093-244-4672-01, 910250670000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> EWA BEACH
Experience island living in this delightful 3BR/2BA Ewa Beach home, located just a 2-minute stroll from the sandy shores, blending relaxation with adventure. Featuring modern amenities, dedicated workspaces, and ample parking, this fully ... Registration Number: 910390100000, GE-140-425-7792-01, TA-140-425-7792-01
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Ewa Beach
Experience coastal living in this expansive 5BR/3BA Ewa Beach retreat, located only a 2-minute stroll from the beach. This fully furnished home features dedicated workspaces and plenty of parking for your convenience. Explore nearby surfing ... Registration Number: 910390100000, GE-140-425-7792-01, TA-140-425-7792-01
5 bedroom
3 bathroom
Sleeps 15
Hawaii> Ewa Beach
