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
5 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
1 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
2 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
3 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
"Tiki Moon Villas 5" is comprised of five bungalows nestled within +22,000 square feet (+half acre) or (+2000 square meters) of lush oceanfront gardens. Each professionally decorated bungalow provides guests with full kitchens, high-end linens, ... Registration Number: TA-064-440-0128-01, GE-064-440-0128-01, 550020820000
9 bedroom
7 bathroom
Sleeps 26
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
5 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
6 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
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
Escape to paradise with this 1-bedroom cottage nestled on the shores of Laie, Hawaii. Embracing the serene beauty of the beach, this cozy retreat offers a tranquil escape with its Bali-inspired decor and warm wooden accents. As you step inside, ... Registration Number: TA-130-961-3586-01, GE-191-801-2100-03, 55002025
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Laie
30 Day Rentals Available. High Speed Internet. Remote Work in Paradise!Registration Number: GE-142-706-2784-01, TA-142-076-2784-01, 550030190000
5 bedroom
6 bathroom
Sleeps 16
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
