select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (city = '96731' OR postalCode = '96731' OR region like '%|96731|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Beautifully renovated, this ground floor condo is in a prime location in Oahu's premier North Shore Turtle Bay Resort destination. Situated in the gated community of Kuilima Estates East, enjoy the laid-back resort lifestyle. Interior comforts ...
Registration Number: TA-128-798-5664-01
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> 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: TA-035-125-0432-01, GE-03
- 1 bedroom
- 2 bathroom
- Sleeps 4
HI> 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: TA-128-654-7968-02, 57001
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> Kahuku
Thank you for your interest in our Villa at Turtle Bay. Please note that our calendars are updated once weekly and may not display exact availability. If you are interested in booking this Villa, please put in your preferred dates and request to...
Registration Number: TA-053-542-7072-01, GE-05
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> Kahuku
This property is privately owned and managed. Access to the resort pool and gym is not permitted, however you can visit the resort activity desk to ask about amenities that are available to everyone.
Whether you are looking for a romantic ...
Registration Number: TA-179-607-3472-01, GE-17
- 3 bedroom
- 2 bathroom
- Sleeps 10
HI> Kahuku
Experience beach house country living in comfort on a quiet beautiful bay on Oahu's northern shore with miles of sandy beach right outside your door ~ perfect for a morning or evening walk or run.
Wide unobstructed views of the ocean from the ...
Registration Number: TA-184-511-6928-01, 56001
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Kahuku
Hale Keana is a modern planation style home in the New Camp at Kahuku Village oceanside gated community. Nestled between Malaekahana State Park and Kahuku Golf Course, New Camp is one of the most desirable family-centric private communities on ...
Registration Number: TA-004-074-0864-01, GE-00
- 3 bedroom
- 3 bathroom
- Sleeps 7
HI> Kahuku
During this pandemic, 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...
Registration Number: TA-136-982-1184-01, 57001
- 3 bedroom
- 2 bathroom
- Sleeps 7
HI> Kahuku
This property is privately owned and managed. Access to the resort pool and gym is not permitted, however you can visit the resort activity desk to ask about amenities that are available to everyone.
Experience the ultimate Hawaiian getaway at...
Registration Number: TA-185-496-5760-01, GE-18
- 3 bedroom
- 2 bathroom
- Sleeps 8
HI> Kahuku
Thank you for your interest in our Villa at Turtle Bay. Please note that our calendars are updated once weekly and may not display exact availability. If you are interested in booking this Villa, please put in your preferred dates and request to...
Registration Number: TA-037-296-6400-01, GE-03
- 4 bedroom
- 3 bathroom
- Sleeps 10
HI> Kahuku
Ocean Villas G218 is a 4-bedroom, 3-bathroom luxury, ocean view condo ideally located on the North Shore of Oahu and exquisitely designed with top amenities, spacious rooms and breathtaking views of the Pacific Ocean. Villa G218 is within the ...
Registration Number: TA-208-597-4016-01, 57001
- 4 bedroom
- 3 bathroom
- Sleeps 10
HI> Kahuku
30 Day Rental
Newly built home in a gated community with beach access. 5 minute walk to a private beach. Minutes to Polynesian culture center and gorgeous beaches for surfing, swimming, and playing: Hukilau, Castles, Kahuku, Vland, sunset, ...
Registration Number: TA-204-742-9120-02, 56002
- 2 bedroom
- 1 bathroom
- Sleeps 4
HI> Kahuku
Escape to your perfect hidden getaway just steps away from beautiful Sunset beach. This luxury suite was designed to capture the colorful sunsets and tropical breezes that blow through the surrounding bamboo shoots. The peaceful setting of the...
Registration Number: TA-156-184-4224-01, GE-15
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Kahuku
The north shore takes you from the hustle of Honolulu to explore some of the most pristine beaches. Nearby beaches include Sunset Beach, Waimea Bay and Ehukai Beach.
This is your perfect getaway paradise for those seeking for calm, private, and ...
Registration Number: TA-177-900-7488-01
- 3 bedroom
- 2 bathroom
- Sleeps 9
HI> Kahuku
COVID-19 Update:
We are currently offering full credits toward a future stay, or a 50% refund of the rent and taxes, for all cancellations due to COVID-19. Please inquire for full credit offer details. For guests who have not paid in full: we ...
Registration Number: TA-110-977-4336-03, 57001
- 4 bedroom
- 3 bathroom
- Sleeps 12
HI> Kahuku
