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, 30
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. Do not click on the "Book Now" Button as we are unable to approve requests...
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. This 3Br, 2.5Bath 2-story townhome features 1200sq ft of ...
Registration Number: TA-020-099-9424-01,+GE-020-099-9424-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Ewa Beach
This property is a 30-day minimum rental and is professionally managed by Ali'i Beach Rentals. ENHANCED PROTOCOLS : We have revised our protocols to include contactless check-in & check-out, professional EPA approved non-toxic ...
Registration Number: GE-152-743-2704-01, TA-152-743-2704-01, 910230320000
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Ewa Beach
This property is a 30-day minimum rental and is professionally managed by Ali'i Beach Rentals. ENHANCED PROTOCOLS : We have revised our protocols to include contactless check-in & check-out, professional EPA approved non-toxic ...
Registration Number: TA-152-743-2704-01, GE-152-743-2704-01, 910230320000
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Ewa Beach
Newly Remodeled 1942 Beachfront Luxury Cottage Perfect for teleworking families (sleeps 8) or visiting executives that want to experience and explore Hawaii. Originally built in 1942, this oceanfront home was lovingly reconstructed...
Registration Number: GE-163-994-1632-01, 910050170000, TA-163-994-1632-01
3 bedroom
2 bathroom
Sleeps 8
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: 910250040000, GE-134-978-5600-01, TA-134-978-5600-02
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Ewa Beach
Aloha, The Honolulu Mayor has suspended new short-term rentals in residential areas. Reservations can now only be taken for a 30-day minimum. We are taking 30-day reservations for Mar 10 to Apr 9, 2024; Jun 5 to Jul 5, 2024; and any ...
Registration Number: GE-142-020-5056-01, TA-142-020-5056-01, 910240260000
3 bedroom
2 bathroom
Sleeps 5
Hawaii> Ewa Beach
Notes • Comfortable accommodations for a maximum of 6 guests. (adults and children). Only registered guests allowed on property. • No visitors. No sub-leasing of the home and property. Maximum 2 vehicles on property. • ...
Registration Number: TA-207-438-4384-01, 910240110000, GE-207-438-4384-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Ewa Beach
CURRENTLY AVAILABLE as of 8/13/23. 30-day minimum stay by required by law has been waived for August, 2023. Usually, this property may not be rented for less than 30 consecutive days and rental prices will not be reduced or adjusted ...
Registration Number: TA-146-161-6640-01, 191023017000, GE-146-161-6640-01
1 bedroom
2 bathroom
Sleeps 4
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: GE-199-052-6464-01, 910050030000, TA-199-052-6464-01
4 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
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: GE-026-129-3568-01, TA-026-129-3568-01, 910371490000
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Ewa Beach
A classic single-story, semi-renovated, beach cottage just steps from your own, private sandy beach. A real chance to experience an authentic Hawaiian getaway, in an old sugar farmland neighborhood away from the tourist mobs of Waikiki. We're on...
Registration Number: 910250670000, TA-093-244-4672-01, GE-093-244-4672-01
2 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. -On a private/gated lane with key code access -Exclusive access to a secluded beachfront, no tourists ...
Registration Number: TA-093-244-4672-01, 910250670000, GE-093-244-4672-01
2 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. -On a private/gated lane with key code access -Exclusive access to a secluded beachfront, no tourists ...
Registration Number: 910250670000, TA-093-244-4672-01, GE-093-244-4672-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> EWA BEACH
Experience a newly upgraded home just 2 mins away from Ewa Beach. This invigorating home has fully furnished 2 bedrooms, and 1 bathroom, with workspaces and parking. A perfect place for family vacations, group beach trips, or travel nurses, ...
Registration Number: GE-140-425-7792-01,320200060000,TA-140-425-7792-01
2 bedroom
1 bathroom
Sleeps 6
Hawaii> Ewa Beach
Experience a newly upgraded home just 2 mins away from Ewa Beach. This invigorating home has fully furnished 3 bedrooms, and 2 bathrooms, with workspaces and parking. A perfect place for family vacations, group beach trips, or travel nurses, ...
Registration Number: GE-140-425-7792-01, TA-140-425-7792-01, 910390100000
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Ewa Beach
Experience a newly upgraded home just 2 mins away from Ewa Beach. This invigorating home has fully furnished 5 bedrooms, and 3 bathrooms, with workspaces and parking. A perfect place for family vacations, group beach trips, or travel nurses, ...
Registration Number: GE-140-425-7792-01, 910390100000, TA-140-425-7792-01
5 bedroom
3 bathroom
Sleeps 15
Hawaii> Ewa Beach
Choose to relax in a tucked away golf course neighborhood inside a fully furnished spacious open floor plan duplex home 3 Bed/2.5 Bath/2 car garage/ 2 level home fenced yard with lanai in Ewa Beach. The home is in a quiet area at the end ...
Registration Number: TA-043-216-1792-01, 911630650002, GE-043-216-1792-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Ewa Beach
A classic single-story, semi-renovated, beach cottage just steps from your own, private sandy beach. A real chance to experience an authentic Hawaiian getaway, in an old sugar farming neighborhood away from the tourist mobs of Waikiki. We're on ...
Registration Number: TA-093-244-4672-01, 91025067, GE-093-244-4672-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Ewa Beach
A classic single-story, semi-renovated, beach cottage just steps from your own, private sandy beach. A real chance to experience an authentic Hawaiian getaway, in an old sugar farming neighborhood away from the tourist mobs of Waikiki. We're on ...
Registration Number: TA-093-244-4672-01, 91025067, GE-093-244-4672-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Ewa Beach