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 = '96731' OR postalCode = '96731' OR region like '%|96731|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Our Villa is Located on the Turtle Bay Resort! You can use the Ocean Villas pool and Jacuzzi, park for free, and pay no resort fees! You can also use most of the Turtle Bay Resort Amenities for a fee. You have FREE access to the 12 miles of ...
Registration Number: GE-016-005-5296-01, 570010130004, TA-016-005-5296-01
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Kahuku
Our Villa is Located on the Turtle Bay Resort! You can use the Ocean Villas pool and Jacuzzi, park for free, and pay no resort fees! You can also use most of the Turtle Bay Resort Amenities for a fee. You have FREE access to the 12 miles of ...
Registration Number: GE-110-977-4336-03, TA-110-977-4336-03, 570010130003
- 4 bedroom
- 3 bathroom
- Sleeps 12
Hawaii> Kahuku
Aloha! Come enjoy your Tropical Hawaiian Vacation in our beautiful, clean, and relaxing one bedroom and two bath condo with all the conveniences of home. Our spacious condo is the perfect blend of tropical seclusion amidst all the amenities ...
Registration Number: GE-035-125-0432-01, TA-035-125-0432-01
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
ALOHA! The perfect place for a peaceful & relaxing time, this condo is stocked with all you should need for a great stay! Located in the Turtle Bay Area, it's very close to beaches (about a 5 minute walk), golf coarse, and some amazing ...
Registration Number: GE-097-928-2432-01, TA-097-928-2432-01
- 2 bedroom
- 3 bathroom
- Sleeps 4
Hawaii> Kahuku
This newly renovated, well-stocked two-bedroom, two bathrooms with loft space is fantastic getaway and a convenient, clean space for you home away from home with your family or friends. You can enjoy all that the North Shore has to offer and ...
Registration Number: 157001029160, GE-034-877-2352-01, TA-034-877-2352-01
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
**6 night stays allowed! **
In compliance with Hawaii vacation rental laws, this property is held for your exclusive use for a period of 30 days or greater regardless of your actual length of stay. Please contact owner for details or ...
Registration Number: 570010420004, GE-133-634-4576-01, TA-133-634-4576-01
- 4 bedroom
- 5 bathroom
- Sleeps 12
Hawaii> Kahuku
Aloha! Great Location! Cool & Comfortable, single level ground floor condo unit with 'no stairs'. Close to the Turtle Bay Resort, Best Beaches, Pristine Ocean, Tidal Pools, Restaurants, and Golf Club House.
*The Newly Renovated Turtle ...
Registration Number: GE-038-608-6912-01, 570010270140, TA-038-608-6912-01
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
Oct Discounts Available:
Oct 23rd/24th - 2 Nights 50% Off
Thanksgiving Special:
Nov 20th to 27th - 7 Nights 40% Off
*Note discounts are already reflected in the pricing so you can book instantly.
Enjoy the comforts ...
Registration Number: TA-084-774-2976-01, GE-084-774-2976-01, 570010130013
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
OUR FIVE STAR REVIEWS SAY IT ALL- SO PLEASE TAKE THE TIME TO READ THROUGH THEM!
If peaceful island living, ocean views, and untouched north shore Oahu beauty are what you are looking for on your Hawaii vacation, you have found the right ...
Registration Number: 571410010000, TA-200-438-5792-01, GE-200-438-5792-01
- 4 bedroom
- 4 bathroom
- Sleeps 12
Hawaii> Kahuku
Beautiful and charming describes this tranquil and peaceful ocean front cottage.
It sits on the 1st level, beach front, of a private estate home on over 3/4 acres of manicured lawn to the ocean. A view from every room!
Fall asleep to ...
Registration Number: 1910570090015, TA-003-769-9352-01, GE-006-769-3952-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Welcome to Kuilima Estates West. Location! Location! Location!
Only steps to the grounds of the famous and newly renovated Turtle Bay Hotel on the North Shore. We are a Legal Vacation Rental.
The condos are Surrounded by Two ...
Registration Number: TA-180-910-5920-01, GE-180-910-5920-01
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Surf's up on the North Shore and our condo is the perfect place to Hang Ten! You'll appreciate the attention to detail, modern amenities and the chic, decor when you escape to our Surfer's Paradise. Our location provides easy access to the ...
Registration Number: 570010290198, GE-128-654-7968-02, TA-128-654-7968-02
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Relax to swaying palms, stunning seas and a tropical breeze! You deserve it!
Step right out the lanai onto fairway one of the Fazio golf courses. Swaying Palms is situated within the complex to provide lots of natural light, privacy, and a...
Registration Number: TA-029-568-0000-01, GE-029-568-0000-01, 570010290031, STR #38
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
***This is the most luxurious stay you can get on the North Shore of Oahu! Our amazing Ocean Villa is one of the only two villas located in the whole Ocean Villa Complex that allows you to have Ocean Views from ALL BEDROOMS. Unlike most houses ...
Registration Number: GE-097-928-2432-01, TA-097-928-2432-01, 1570010130036
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
NEW LISTING! This newly renovated 2-story 3 bedroom condo is located at Turtle Bay in the gated community. This is the ONLY condo with split AC downstairs and central AC upstairs. This beautiful island condo sleeps 8 guests.
This condo ...
Registration Number: 570010290125, GE-087-788-0832-01, TA-087-788-0832-01
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
The maximum number of guests for this property is 4.
This is when QUALITY meets LOCATION!!!
Implacably maintained, built with only the best of the line in luxury, this Ocean Villa is located in arguably the best location on the ...
Registration Number: GE-156-544-8704-01, 1570010130017, TA-156-544-8704-01
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
We are all looking for a quiet, restful, safe place to de-stress and forget the troubles of the world. As a Hawaii born and raised local, there are few places that can rival this task than the North Shore on Oahu. With 24 hr security, free ...
Registration Number: 570010290098, TA-136-982-1184-01, GE-136-982-1184-01
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kahuku
Please Note: Access to resort amenities is not included, however, the condo complex has its own pool, grills and is just steps to the beach!
Experience ultimate Hawaiian getaway luxury at our 2nd-level, recently refurbished Villa 201. ...
Registration Number: 570010130022, GE-037-296-6400-01, TA-037-296-6400-01
- 4 bedroom
- 3 bathroom
- Sleeps 10
Hawaii> Kahuku
Our Villa is Located on the Turtle Bay Resort! You can use the Ocean Villas pool and Jacuzzi, park for free, and pay no resort fees! You can also use most of the Turtle Bay Resort Amenities for a fee. You have FREE access to the 12 miles of ...
Registration Number: 570010130009, TA-149-842-1248-03, GE-149-842-1248-03
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Please Note: Access to resort amenities is not included, however, the condo complex has its own pool, grills and is just steps to the beach!
Prepare to settle into island time a little easier with the unparalleled accommodations and ...
Registration Number: GE-022-528-1536-01, TA-022-528-1536-01, 570010130031
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Maximum 8 adults but can sleep up to 10 including children!
Our Villa 315 is Located on the Turtle Bay Resort! You can use the Ocean Villas pool and Jacuzzi, park for free, and pay no resort fees! You can also use most of the Turtle Bay ...
Registration Number: 570010130054, GE-133-407-6928-01, TA-133-407-6928-01
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Please Note: Access to resort amenities is not included, however, the condo complex has its own pool, grills and is just steps to the beach!
Experience the ultimate Hawaiian getaway at the Ocean Villas at Turtle Bay. This spacious ...
Registration Number: 570010130027, GE-055-657-0624-01, TA-055-657-0624-01
- 4 bedroom
- 3 bathroom
- Sleeps 12
Hawaii> Kahuku
Please note: This property is currently for sale. The property manager may contact you to make arrangements to schedule showings at a convenient time if necessary.
Welcome to old Hawai'i. Hale Ula Ula sits on a white sandy beach with ...
Registration Number: GE-128-203-0080-01, TA-128-203-0080-01, 560010300000
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Villa is Located on the Turtle Bay Resort on the picturesque North Shore! The villa is tastefully decorated with elegance and style. All the comforts of home in this three bedroom two bathroom Turtle Bay Beach Villa. Ideally situated on the ...
Registration Number: GE-186-079-0272-01, TA-186-079-0272-01, 570010130034
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Please Note: Access to resort amenities is not included, however, the condo complex has its own pool, grills and is just steps to the beach!
Prepare to settle into island time a little easier with the unparalleled accommodations and ...
Registration Number: GE-177-379-2256-01, 570010130050, TA-177-379-2256-01
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
This one bedroom, one bathroom condo was completely renovated in 2020 and has everything you need to relax and enjoy your time on the island. It is the perfect space for solo travel, couples or escaping with your best friend. All of the ...
Registration Number: TA-053-334-6816-01, 157001027035, GE-053-334-6816-01
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kawela Bay
~ You just found your Base Camp to explore the North Shore of Oahu ~
Our condo has just been completely remodeled and redecorated with two brand new bathrooms as of September 2021. We are situated alongside the Palmer Golf Course and are...
Registration Number: TA-164-934-4512-01, GE-164-934-4512-01, 570010270054
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
This lovely 1 bedroom w/loft upgraded unit has new kitchen cabinets & appliances, ceramic tile floors and granite countertops. The living room has a flat-screen TV, internet and WIFI, and ceiling fan. Window a/c in the living room and the ...
Registration Number: 570010270101, GE-030-645-5552-01, TA-030-645-5552-01
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
Head to the tropics of The North Shore and see for yourself why our guests describe our destination/vacation home as an "island paradise." Our spacious beach house, with 5 bedrooms and 3 full bathrooms, has access to one of Oahu's few remaining,...
Registration Number: TA-042-503-2912-01, 570030600000, 9O/TVU-O2I2, GE-042-503-2912-01
- 5 bedroom
- 3 bathroom
- Sleeps 14
Hawaii> Kahuku
Summary:
Welcome to our little slice of paradise on the North Shore of Oahu, Hawaii. Our beach cozy condo is nestled in a well maintained gated community at the famous Turtle Bay Resort. Whether you are looking to relax or enjoy adventurous ...
Registration Number: TA-048-668-5184-01, GE-048-668-5184-01, 570010270067
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku