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 = 'laie' OR postalCode = 'laie' OR region like '%|laie|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
Recently updated ocean view home on an ocean front peninsula. Enjoy the views of Laie Bay from the comforts of the home or deck of the house, or venture to the Ocean Front cliffs right in the back yard and indulge in the beauty captured only ... Registration Number: GE-061-340-5184-01, TA-061-340-5184-01
4 bedroom
3 bathroom
Sleeps 14
Hawaii> Laie
This beautiful 4 bedroom home is an easy 3 minute walk to Hukilau Beach. It has been fully gutted and rebuilt and looks and feels like a brand new home. It is a comfortable, clean and relaxing home equipped with A/C, new King beds in the two ... Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160200000
4 bedroom
3 bathroom
Sleeps 11
Hawaii> Laie
Completely restored in 2024, this light-filled, 2,800-sq-ft, open concept home features breathtaking views of Malaekahana Bay. Enjoy the fully equipped modern kitchen, living/dining room for easy entertaining and a recreation room that has a ... Registration Number: GE-117-816-7296-01, TA-117-816-7296-01, 560010720000
3 bedroom
4 bathroom
Sleeps 10
Hawaii> Laie
Hukilau Beach and the blue waters of the Pacific Ocean on one side and the Ko'olau mountains on the other, Hale Lani lives up to its name as the “House of Heaven.” The perfect choice if you’re traveling with a large family or a group of ... Registration Number: 000055003031, TA-048-589-8240-01, GE-048-589-8240-01
6 bedroom
5 bathroom
Sleeps 14
Hawaii> Laie
Welcome to the Coco Bungalow, a delightful Oahu beachfront getaway! This stand-alone studio home offers a unique and enchanting experience.Registration Number: TA-064-440-0128-01, GE-064-440-0128-01, 550020820000
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Laie
This Oahu ocean side vacation rental with direct views of the ocean and direct access to Laie Beach right out your front door. Decorated with 100 year old antiques from Java and China, mixed with modern comfort furnishings make this a great ... Registration Number: TA-064-440-0128-01, GE-064-440-0128-01, 550020820000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Laie
Welcome to Papaya 2 at Tiki Moon Villas, offering an idyllic escape just across from the Polynesian Cultural Center. Ideal for families or intimate gatherings of friends, this two-bedroom vacation rental on the North Shore invites you to ... Registration Number: TA-064-440-0128-01, GE-064-440-0128-01, 550020820000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Laie
Aloha!Registration Number: GE-171-005-3376-01, TA-171-005-3376-01, 550160680000
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Laie
Aloha!Registration Number: GE-171-005-3376-01, TA-171-005-3376-01, 000055016068
4 bedroom
2 bathroom
Sleeps 9
Hawaii> Laie
Welcome to Pineapple Place🍍30 day rentalRegistration Number: GE-074-604-3904-01, TA-074-604-3904-01, 550170650002
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Laie
Experience the ultimate beach getaway at our recently constructed home in Kahuku! Located on Oahu's breathtaking North Shore, this cozy home offers the ultimate beach getaway. With convenient quick access to several stunning beaches and popular ... Registration Number: 560010190000, GE-146-255-8720-01, TA-146-255-8720-01
4 bedroom
2 bathroom
Sleeps 12
Hawaii> Laie
Laie Bay views are right outside your window. The Oceans Edge is literally 50 feet away!! Experience island-style living in this beautiful 4-bedroom Hawaiian beach house. Not only is it in the perfect location, but also offers everything you'll ... Registration Number: TA-436-291-3801-01, GE-436-291-3801-01, 550100380002
4 bedroom
5 bathroom
Sleeps 14
Hawaii> Laie
White Hibiscus Estate is located right off the ocean on one of the most scenic views on the island. Wake up to spectacular sunrises and spot whales during the winter whale season. People come from all over to see the spectacular views and watch ... Registration Number: 550100380000, TA-118-525-3376-01, GE-118-525-3376-01
3 bedroom
5 bathroom
Sleeps 15
Hawaii> Laie
30 day rental. Please inquire. 888-799-6920Registration Number: 550010130000, TA-210-165-8624-01, GE-210-165-8624-01
4 bedroom
2 bathroom
Sleeps 14
Hawaii> Laie
LOCATION:Registration Number: GE-550-010-0200-00, TA-550-010-0200-00, 550010020000
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Laie
30-day rental. Please inquire.Registration Number: 310080170003, TA-018-203-1020-01, GE-032-910-7681-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Laie
30-day rental. Please inquire.Registration Number: TA-130-961-3586-01, GE-191-801-2100-03, 130049850002
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Laie
Welcome to our coastal haven, a North Shore oasis that seamlessly blends comfort, style, and panoramic ocean views. This private upstairs duplex unit invites you into a world of relaxation and luxury. Here's what awaits you:Registration Number: 550040010000, GE-064-189-2909-01, TA-064-189-2909-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Laie
WE ALREADY HAVE A VERY HIGH CLEANING STANDARD FOR OUR HOME AND DUE TO COVID-19, WE HAVE ADDED INCREASED SANITATION WITH APPROVED ANTI-BACTERIAL CLEANERS THROUGHOUT.Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160200000
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Laie
This cozy studio apartment has recently been renovated and is ideal for 2-3 people.Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160390001
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Laie
