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 = '96762' OR postalCode = '96762' OR region like '%|96762|%') 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.
Step inside this beautifully furnished and spacious bungalow, where you'll find comfort and style ...
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!
My name is Lindsey and I'm happy to be your host in beautiful Laie, located on the world-famous North Shore of Oahu. Like the hibiscus in our yard, I grew up here and love this town so much that I'm raising my own children in this side...
Registration Number: GE-171-005-3376-01, TA-171-005-3376-01, 550160680000
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Laie
Aloha!
My name is Lindsey and I was born and raised right here in beautiful Laie, HI located on the world famous North Shore of Oahu. This is our personal family home and we will go going on vacation. I love this town so much, that once I got...
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 rental
Beautiful studio apartment 5 min walk to the beach! Sparkling clean!
Close to the Polynesian Cultural Center, BYUH, Laie Temple, beautiful beaches and 20min drive to popular North Shore beaches ...
Registration 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
- 8 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
- 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-6920
This is your perfect getaway paradise for those seeking for quiet, private, and relaxing Hawaiian vacation. The sand and water are just steps away. You will love the sound of the ocean lulling ...
Registration 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.
Welcome to Moku Paradise! This 3 bedroom, 3 bath + studio cottage island style oceanfront home has breathtaking ocean views and a semi-private beach. This is a perfect getaway paradise for those seeking a ...
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.
Welcome to Moku Paradise B! This 1 bedroom, 1 bath island-style oceanfront cottage has breathtaking ocean views and a semi-private beach. This is a perfect getaway paradise for those seeking a calm, private,...
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:
Living ...
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!
Popular beachfront rental well known as the LAIE BEACH RETREAT or LAIEBEACHRETREAT
Sandy BEACHFRONT North Shore retreat located on the Hukilau Beach of Laie Bay!...
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.
You'll love the proximity to beautiful Hukilau Beach-an easy 5-8 min walk. Delicious Restaurants and food trucks are near by but there is a cook-top and ...
Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160390001
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Laie
