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, 20
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: TA-044-199-7312-02, GE-044-199-7312-02, 570010130024
- 4 bedroom
- 3 bathroom
- Sleeps 12
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
Permit # 2212-CCH-0655
Aloha!
This legal non-smoking unit is located at Kui'lima East Condo's at Turtle Bay Resort on the famous North Shore of Oahu. Situated next to the 17th hole of the Fazio course, this spacious 820 square foot...
Registration Number: GE-067-795-3536-03, TA-067-795-3536-02, 570010270011
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
Enjoy the comforts of home in a luxury resort experience at our studio Ocean Villa at the premier Turtle Bay Resort. With an exceptional direct ocean view from your private lanai, this second floor property, unit #217, is only 60 feet from the ...
Registration Number: TA-084-774-2976-01, GE-084-774-2976-01, 570010130013
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
DO NOT use " Request to Book" ! Use "Contact Host".
We are required to post a 30 night minimum but this does not mean you must stay for 30 days.
If your dates are marked "Unavailable", please still inquire with your ...
Registration Number: TA-107-923-4560-01, GE-107-923-4560-01, 1005006001027
- 5 bedroom
- 4 bathroom
- Sleeps 22
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: 1990/NUC-2052, 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 STR NO. 2210-CCH
- 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
Welcome to our little piece of paradise perfect place to stay during your visit to the islands. Our CLEAN and updated one bedroom (with king bed/master downstairs) + loft ( 2nd king bed upstairs) condo at Turtle Bay has a full kitchen, Split Air...
Registration Number: GE-041-793-7920-01, 157001029169, TA-041-793-7920-01
- 2 bedroom
- 2 bathroom
- Sleeps 5
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
Make the most out of your tropical Hawaiian adventure by spending it here at the comfortable and heavenly Ocean Villa at Turtle Bay. This 3 bedroom, 2 bath can accommodate an astounding 8 people, making it an excellent choice for a large family ...
Registration Number: TA-208-597-4016-01, GE-208-597-4016-01, 570010130014
- 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
Our Turtle Bay Hideaway is the perfect place for your Hawaiian getaway! Enjoy 5 miles+ of secluded beaches, 2 private swimming pools, 2 private tennis courts, 2 PGA golf courses, horseback riding, surfing, snorkeling, diving, fishing, ...
Registration Number: TA-026-642-9952-01, STR #1661, 570010270084, GE-026-642-9952-01
- 1 bedroom
- 1 bathroom
- Sleeps 8
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
