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, rewritten_description,
(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 = 'kapolei' OR postalCode = 'kapolei' OR region like '%|kapolei|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
This large corner villa is located inside the Beach Tower, the closest building to the beach (20ft walk from your condo to the beach lagoon).Registration Number: TA-134-204-4160-01, GE-134-204-4160-01, 1910570090191
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Aloha! E Komo Mai (Welcome) to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu and conveniently located just 25 minutes from Honolulu! We would love to have you come stay with us!!Registration Number: 915070090149, TA-135-643-9552-01, GE-135-643-9552-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Military & DOD, We support our service members and have special rates for active military & DOD utilizing TLA. If you are coming to Oahu for work in the Navy, Army, Marines, Air Force, Space Force, Coast Guard, National Guard or Department of Registration Number: TA-062-230-7328-01 NUC no
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
COVID Free Quaint 3 bedroom 2 1/2 Bathroom condo located in Ko' olina Fairways. Perfect place to unwind and relax. Two story with 3 bedrooms and two full bathrooms upstairs and kitchen, living room dining room and half bath downstairs. All Registration Number: 910560140086, TA-133-018-6240-01, GE-133-018-6240-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Dream-like comfort awaits you and your family in this beautifully appointed 2-story, 1,700-square foot, 3 bedrooms, 2.5 bath townhouse vacation rental condominium in an award-winning residential resort community that offers privacy, relaxation, Registration Number: 1910560040124, TA-200-903-8848-01, GE-200-903-8848-01
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Imagine yourself in a world-class Oahu, Hawaii oceanfront resort in Ko Olina beach rental bliss with exotic woods, natural stone and an expansive lanai. Registration Number: TA-200-903-8848-01, GE-200-903-8848-01, 1910570090098
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Our unit in the Beach Villas at Ko Olina is located on the top 11th floor of the Beach Tower. The Beach Tower is one of two towers in the Beach Villas and is the one located closest to the water). The panoramic views of the water, sand beach, Registration Number: TA-059-295-5392-01, GE-059-295-5392-01, 910570090251
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Effective October 23, 2022 we will not accept rental for less than 91 days because we have to comply with the new law of city and county of Honolulu. Registration Number: 920260560000, TA-131-073-0240-01, GE-131-073-0240-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
6th Floor Beach Tower, 2 Bedroom 2 Bath Ko Olina Resort Beach Villa, with Gorgeous Sunset Ocean Views. 2 Units from Ocean-Front! Breath-taking Panoramic Views!Registration Number: 910570090215, GE-057-702-4000-01, TA-057-702-4000-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kapolei
A Wonderful 3-Bedroom Villa to Enjoy the Beauty of Ko OlinaRegistration Number: 910570090003, GE-209-602-3552-01, TA-209-602-3552-01
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Premium Beach Villa very close to the beach. B-208 is the closest villa to the beach on the sunset side. Enjoy direct views of the beach and ocean without looking past another lanai. Enjoy added privacy and ocean views on the desired sunset Registration Number: TA-102-559-5392-01, GE-102-559-5392-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
2nd most reviews on VRBO!!! See other unit we have available: VRBO #311592Registration Number: GE-203-453-6448-01, TA-203-453-6448-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Summary:Registration Number: 910570090056, GE-071-028-5824-01, TA-071-028-5824-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
If you are not familiar with the Ko Olina Resort, please Google 'Ko Olina', there are just too many fun things to do, too many beautiful places to see, too many great restaurants and shops to do them justice in this little ad.Registration Number: GE-176-514-2528-01, TA-176-514-2528-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Please choose a Friday through Sunday check-in for a 7 night stay. Registration Number: TA-000-785-4592-01, GE-000-785-4592-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Penthouse Beach Tower 9th Fl (B907) - Steps from the Ocean. Escape to a Hawaiian Paradise at the Beach Villas Palekaiko on Oahu! Sleeps up to 8.Registration Number: 910570090244, GE-128-828-2112-01, TA-128-828-2112-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Penthouse Beach Tower 10th Fl (1003) - Steps from the Ocean. Escape to a Hawaiian Paradise at the Beach Villas Palekaiko on Oahu! Sleeps up to 6.Registration Number: TA-018-470-0928-01, GE-018-470-0928-01, 910570090248
2 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
The Nalu Collection is a premier group of boutique, luxury villas within Ko Olina Resort. Our properties are consistently top rated, and upgraded with custom-made furniture, natural materials, appliances, and amenities. We personalize each Registration Number: GE-181-819-9552-01, TA-181-819-9552-01, 2022/ELOG-2340(ZS), 910570090175
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to our luxurious paradise retreat at Ko Olina Beach Villas, where relaxation meets first-class amenities. This impeccably maintained penthouse in the Beach Tower offers breathtaking views of the ocean, beach, and lagoon pools—a true Registration Number: TA-186-286-0800-01, GE-186-286-0800-01, 910570090242
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
“This is a timeshare. While the amenities and features will be the same, you may not get the exact unit shown in the photos.”Registration Number: GE-196-354-7136-01, TA-070-692-2496-03, 191057012000
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
THIS IS THE BEST VALUE ONE CAN GET AT THE 5 STAR ACCOMODATIONS AT THE BEACH VILLAS AT KO OLINA! Located in the Beach Villas at Ko Olina, within the Ko Olina resort and Marina, this relaxing 2 bedroom 1200 sq ft totally upscale villa has Registration Number: TA-101-831-0656-01, GE-101-831-0656-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Welcome to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu! The complex of the Beach Villas is an Oceanfront property. Our villa is spacious with 3 Bedrooms and 3 Baths in over 1500 square feet of living space. Our villa Registration Number: 0190570090034, GE-030-995-3536-01, TA-030-995-3536-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
NOTE: Disregard displayed "response metrics" numbers. They are not accurate. Actual response time is prompt and I respond to all inquires. -Owner, Jeff JohnsonRegistration Number: 910570090124, TA-075-893-1456-01, GE-075-893-1456-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kapolei
Welcome to South Sea Bungalows:Registration Number: TA-100-787-2512-01, GE-100-787-2512-01, 910560040194
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Length of stay: MINIMUM 30 NIGHTS. Monthly rental. Property damage insurance required.Registration Number: TA-084-645-5296-01, GE-084-645-5296-01, 910560020080
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Currently we have this reserved for a one-week reservation sleeping up to 8. Check in on December 14 and out on December 21, 2019. Two-week option and flexible dates are available upon request. Located on one of four picturesque lagoons, Registration Number: 123456789012, GE-049-590-1184-01, TA-049-590-1184-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Registration Number: TA-028-516-7616-03, GE-028-516-7616-03
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Your ultimate paradise vacation awaits you at the Ko Olina Beach Villas Resort, Oahu's premier Oceanfront resort. Stay in this beautiful two bedroom/two bath luxury suite and enjoy sunset views from the expansive lanai. This spectacular Registration Number: 910570090026, TA-149-162-3936-01, GE-149-162-3936-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Aloha, and thank you for your interest in the Beach Villa's at Ko Olina. This property is one of the newest and most exclusive available for rent on the island of Oahu. But more importantly, this is our island home for much of the year. We Registration Number: GE-012-649-0624-01, TA-012-649-0624-01, 191057009023
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Ko Olina Beach Villas.This property is one of the newest and most exclusive available for rent on the island of Oahu. But more importantly, this is our island home for much of the year. We love it here, and we know you will too.Registration Number: 910570090202, TA-041-058-4064-01, GE-041-058-4064-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
This penthouse villa has 3 bedrooms and 3 baths and is located on the top/4th floor in the Ocean Tower (O426). The villa has an unobstructed full front ocean view and the private lanai overlooks the peaceful and tranquil koi pond area of the Registration Number: TA-163-118-4896-01, GE-163-118-4896-01, 910570090055
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Please inquire first before making reservation to ensure dates are available. This is a Marriott Vacation Club property. Multiple rooms can be booked for same dates. If your dates aren’t available in same room layout and view for entire Registration Number: 91057013, TA-157-864-6016-01, GE-157-864-6016-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Looking to enjoying your vacation up close and personal with Hawaii's beautiful beaches and ocean? This oceanfront villa is the one for you! You'll be impressed with the spectacular unobstructed ocean view. It's one of the rare villas that is Registration Number: 910570090237, TA-163-118-4896-01, GE-163-118-4896-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Please choose a Friday through Sunday check-in for a seven night stay. Quotes are based on resort/Island views. Please ask you agent if you are requesting an alternate view. Registration Number: TA-000-785-4592-01, GE-000-785-4592-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
This gorgeous 3-bedroom, 2-bath villa is located in Hawaii's premier KoOlina Beach Resort in the gated community. Registration Number: GE-193-152-8192-03, TA-193-152-8192-03, 910560050299
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Registration Number: TA-072-401-7152-01, GE-072-401-7152-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
Ko Olina Kai is a peaceful gated community that offers all the tranquil privacy you could desire! The interior is spacious and homey with 2 bedrooms, 2 baths, a living room with an adjacent dining space, and a kitchen. This outstanding villa Registration Number: GE-073-913-2416-01, TA-073-913-2416-01, 910560050204
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Please inquire first before making reservation request to ensure dates are available . Oceanview rates are higher about 20-30% more. Registration Number: 91057013, GE-157-864-6016-01, TA-157-864-6016-01
0 bedroom
1 bathroom
Sleeps 4
Hawaii> Kapolei
Our spacious 1,500 square foot suite is the only beach-front penthouse available for rent at the prestigious Beach Villas Resort in Ko Olina and the only privately rentable condo with panoramic ocean views from every room.Registration Number: 910570090228, TA-137-160-2944-01, GE-137-160-2944-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
* Book with confidence! This vacation rental is a LEGAL Oahu vacation Rental Operating in a Resort Zoned District and is not affected by passage of Bill 89. This Luxury Condo is a spacious and beautiful 3 bedroom, 3 bath, professionally Registration Number: 1910570090015, GE-003-769-3952-01, TA-003-769-3952-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Kapolei
*** Concerned about COVID-19? Vacation with Confidence at Marriott Vacation Clubs. Learn about Our Next Level of Clean at: https://clean.vacationclub.com/ Registration Number: GE-196-354-7136-01, 91057013, TA-070-692-2496-03
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Spacious 3-bd Villa at Ko Olina Resort. Beach Tower 5th floor on private wing with large floor plan (sleeps 6!)Registration Number: 910570090200, GE-057-702-4000-01, TA-057-702-4000-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
Dreaming of a memorable yet affordable Hawaiian vacation? Our Villa is calling you! It is located on the sunny Western shore of Oahu, and was built as a paradise for family-oriented beach lovers. It is landscaped with beautiful palm trees, koi Registration Number: GE-004-925-6960-01, TA-004-925-6960-01, 91057009
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Marriott’s Ko Olina Beach Club is situated on the beautiful Western shore of Oahu, where waterfalls and fountains welcome you as you drive under the port- cochere. Just 30 minutes from Honolulu, this beach front oasis is secluded and peaceful. Registration Number: TA-072-698-1120-01, GE-072-698-1120-01
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Kapolei
Luxurious three bedroom Ko Olina Resort villa located in west Oahu. Enjoy the calm ocean lagoons. In short walking distance to Four Seasons, Marriott Beach Club, Ko Olina Golf and Ko Olina center (restaurants & shops).Registration Number: GE-108-964-9152-01, 910560050113, TA-108-964-9152-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Beautiful & spacious yet cozy and comfy Ko Olina Kai Welcome to your tropical haven in the coveted Ko Olina Kai community. This delightful 3-bedroom, 3-bathroom house rental offers a prime location just a 10-minute walk from the stunning Ko Registration Number: GE-202-926-2336-01, TA-202-926-2336-01, 910560050214
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Kapolei
This property is a 30-day minimum rental and is professionally managed by Ali'i Beach Rentals.Registration Number: GE-113-732-4032-01, TA-113-732-4032-01, 910560050083
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Luxurious 3 bedroom Villa located in West Oahu. Enjoy all 4 of Ko Olina's ocean lagoons. Walking distance to Four Seasons, Disney Aulani, Marriott Beach Club, Ko Olina Center (shops and restaurants), Ko Olina Golf Club, Roy's Restaurant, and Registration Number: GE-170-773-5552-01, 910560050122, TA-170-773-5552-01
4 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
This newly renovated, luxurious Villa is nestled in the quiet resort of Ko Olina. This Villa has 3 bedrooms and can sleep up to 6 guests. Choose to spend your days on the Ko Olina Championship golf course, one of the four lagoons on the beach, Registration Number: 910560050164, GE-170-773-5552-01, TA-170-773-5552-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kapolei
Located just 10 min from the West Side Resorts & Ko'olina Lagoons, you will have easy beach access while still enjoying the privacy offered by this quiet neighborhood home.Registration Number: GE-001-276-5696-01, TA-001-276-5696-01, 911600520084
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Kapolei
