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, 30
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
A clean, spacious home with comfortable living space and privacy for the ohana. This private 2 bed 1 bath sleeps up to six guests. Beautiful views to the ocean and Koolau Mountains. Very nice and good Location! 5 minute walk to the ...
Registration Number: TA-087-032-6272-01, 1550110540000, GE-087-032-6272-01
2 bedroom
1 bathroom
Sleeps 5
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
30 day rental. Please inquire. Island Hibiscus Hale 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. There is a gorgeous family ...
Registration Number: 540015600001, GE-118-525-7896-01, TA-118-515-4562-01
4 bedroom
5 bathroom
Sleeps 15
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
Ocean Cliffs Getaway 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. There is a gorgeous family friendly beach just down the way of a ...
Registration Number: GE-116-377-4919-01, 440600890000, TA-112-425-7135-01
4 bedroom
5 bathroom
Sleeps 14
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
30 day rental. Please inquire. 888-799-6920 This is your perfect getaway paradise for those seeking for calm, private, and relaxing Hawaiian vacation. The sand and water are just steps away. You will love the sound of the ocean lulling you ...
Registration Number: 550010130000, TA-210-165-8624-01, GE-210-165-8624-01
4 bedroom
2 bathroom
Sleeps 14
Hawaii> Laie
LOCATION: 30 Day Rental If you’re you looking for perfect white sand beaches with clear turquoise water & close proximity to everything in the North Shore, this is the place for you. Directly across the road is one of the best kept ...
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. 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
Welcome to the charming town of Laie, Hawaii, where you'll discover a stunning 4-bedroom beachfront property that combines breathtaking ocean views, luxurious amenities, and a serene coastal lifestyle. Situated on the northeastern coast of Oahu,...
Registration Number: 350019150002, TA-001-135-5113-01, GE-119-004-2889-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Laie
Bring the whole family to this great place and enjoy a blissful rural retreat! Ideal for friends, couples or solo travelers. Equipped with queen size beds and single beds , hosted by enthusiastic owners. This is your deal alternative as your ...
Registration Number: TA-087-032-6272-01, 1550110540000, GE-087-032-6272-01
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Laie
Nestled on the picturesque shores of Laie, this exquisite 4-bedroom beachfront home offers the epitome of coastal living combined with contemporary elegance. Boasting a semi-private stretch of pristine beach, this retreat invites you to ...
Registration Number: 330070200000, GE-650-293-8791-01, TA-255-312-5959-01
4 bedroom
2 bathroom
Sleeps 14
Hawaii> Laie
A Malaekahana gem. Airy, bright and convenient, this beautiful home is a fully furnished three-bedroom, four-bathroom beachfront property, located in farming country between the two Malaekahana State Parks on the tropical island of Oahu. Sitting...
Registration Number: TA-117-816-7296-01, GE-117-816-7296-01, 560010720000
3 bedroom
4 bathroom
Sleeps 8
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
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. This beautiful 3 bedroom home is steps away from Hukilau Beach. It has ...
Registration Number: TA-018-462-4128-01, GE-018-462-4128-01, 550160200000
3 bedroom
2 bathroom
Sleeps 7
Hawaii> Laie
PRIVATE BEACH HOUSE PLEASE CONTACT US ABOUT AVAILABILITY *** OUR CALENDAR MAY BE OPEN *** *****We may not advertise to rent for a period of shorter than 30 days. We may rent for shorter durations as long as our calendar is ...
Registration Number: TA-089-697-4848-01, GE-089-697-4848-01
3 bedroom
2 bathroom
Sleeps 6
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: 550030190000, GE-142-706-2784-01, TA-142-076-2784-01
5 bedroom
6 bathroom
Sleeps 16
Hawaii> Laie
This is a beautiful 5 bed, 3 bath home that sleeps 12. It is located just steps from famous Hukilau Beach, and within walking distance from BYU Hawaii. There is a washer and dryer, two living rooms, office and much more. Enjoy a sunrise stroll...
Registration Number: TA-031-473-7664-01, GE-031-473-7664-01
5 bedroom
3 bathroom
Sleeps 12
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
The Bamboo 2 on Oahu is an incredible ocean side vacation rental. Enjoy direct views of the ocean and direct access to Laie Beach right out your back door. Decorated with 100 year old antiques from Java and China, mixed with modern comfortable ...
Registration Number: TA-064-440-0128-01, GE-064-440-0128-01, 550020820000
2 bedroom
2 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 AND TO HIGH TOUCH AREAS. This cozy newly renovated one-bedroom, now with ...
Registration Number: 550090370000, GE-018-462-4128-01, TA-018-462-4128-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Laie
CONTACT HOST - click for answers to your questions on the cancellation policy or the property ***this property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the...
Registration Number: 910070780000, GE-100-125-6960-01, TA-100-125-6960-01
4 bedroom
2 bathroom
Sleeps 10
Hawaii> Laie
CONTACT HOST - click for answers to your questions on the cancellation policy or the property ***this property may not be rented for less than 30 consecutive days. Rental prices will not be reduced or adjusted based on the number of days the ...
Registration Number: 910070780000, GE-100-125-6960-01, TA-100-125-6960-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Laie