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 (`headline` like '%kuilima%') AND sleeps>=1 order by rating desc LIMIT 0, 50
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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
ALOHA!
***As of September 16th, 2024, we have installed two new split-unit AC (air conditioners)—one in the living room and one in the loft. Both units are of the highest quality and were professionally installed by one of the most ...
- 2 bedroom
- 3 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 ...
- 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 ...
- 3 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 course. Swaying Palms is an end unit, situated within the complex to provide lots of natural light, ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Welcome to our little piece of paradise. A perfect place to stay during your visit to Oahu. Our CLEAN and beautifully UPDATED condo can sleep 6 comfortably. With TWO king beds and a one queen sofa bed. It also includes a brand new full-size ...
- 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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
Our bright and breezy Kuilima West condo is located steps away from Turtle Bay Resort, Lei Lei's restaurant, walking trails, golf, and the beautiful beaches of Oahu's North Shore. With two Tempur-Pedic king beds and AC in all bedrooms and the ...
- 4 bedroom
- 2 bathroom
- Sleeps 10
Hawaii> Kahuku
Aloha & welcome!
If you are looking for a cozy and convenient place to stay in Turtle Bay, look no further than our 1 bedroom, 2 bathroom condo that can sleep up to 4 guests!
This property is situated in the famous Turtle Bay ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
Relax and unwind in this NEW, FAMILY FRIENDLY Airbnb! The NEWLY RENOVATED ground floor unit is in Kuilima Estates West in Turtle Bay, on Oahu’s beautiful North Shore.
From the patio walk onto the Fazio golf course, to LeiLei's ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
***WE ARE ON OAHU, AND CONTINUE TO WELCOME TOURISM***
Welcome to your spacious 2 bedroom, 2 full bath, PLUS loft comfortable deluxe condo on the North Shore of Oahu. We are in Kuilima Estates West, which is next to the Turtle Bay Resort....
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Aloha!
We are excited to offer this beautiful second floor unit, with 1 bedroom + oversized loft for your next stay in paradise. This clean and comfortable unit has everything you need to make your stay memorable! This unit is situated in...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Swimming pool, tennis courts, club house; these are just some of the community amenities that you can enjoy in Honu Kai. It is located next to the golf course and close to the beach and many attractions. The home is designed with large living ...
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Kahuku
A restful retreat on the North Shore of O’ahu awaits either couples or small families in the one bedroom, one bath ground floor condominium unit of Pulelehua Kuilima Estates West #142. Located on the grounds of the Turtle Bay Resort, the ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Find beauty at your doorstep with Kuilima Estates West by Gather, an openly designed 1-bedroom, 1-bathroom unit. Located at the entrance of the magnificent Turtle Bay, this property is just steps away from the spectacular coastline of Oahu’s ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
LICENSED VACATION HOME #90/TVU-0580
ENJOY A VACATION AWAY FROM THE CROWDS. THIS UPDATED AND TASTEFULLY FURNISHED GROUND-FLOOR 1-BEDROOM, 1-BATH CONDO IS NESTLED BETWEEN PRISTINE SANDY BEACHES AND BEAUTIFUL MOUNTAINS AT THE TURTLE BAY ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
E Komo Mai… Welcome to the Ilima West Kuilima Estates #18 at Turtle Bay by Gather, your tropical oasis on the majestic North Shore of Oahu, Hawaii. As you arrive at this tranquil getaway, the warmth of aloha spirit greets you, inviting you ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kahuku
Book with confidence! This property is professionally managed by Ali'i Beach Rentals.
***This property is new to our inventory. Reviews are coming soon. Please rest assured this property is fabulous!
Indulge in the ultimate luxury ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kahuku
End every day with a stunning Hawaiian sunset on the North Shore of O’ahu when you stay at Kuilima Estates West, unit 120. This newly remodeled 2-bedroom, 2-bathroom with loft townhome is located adjacent to the Turtle Bay Golf course, home of...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kahuku
Live the ultimate paradise experience on Oahu's renowned North Shore at Kuilima Estates East, unit 164. Experience this cozy one-bedroom, two-bathroom townhome that overlooks the stunning Turtle Bay Golf Course. Boasting an unobstructed view of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
K B M Resorts: Kuilima East Estates #117 1BDRM/1BA ; Remodeled Kitchen in 2008, Golf Course View; inside Turtle Bay; L'Occitane Amenities
null
*Special Offer - Book any KBM Resorts villa in Hawaii for 6 nights or more with an ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Special Offer- Book any KBM Resorts villa in Hawaii for 6 nights or more with an arrival date now through Dec 15, 2024 and receive a 4-door rental car from one of our major rental car agency partners. The Car is complimentary, you pay only ...
- 3 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kahuku
Welcome to your perfect getaway at the Kuilima Golf Retreat! This beautiful rental offers exceptional amenities and is just a short walk from the beach.
This inviting unit accommodates up to four guests, featuring one bedroom, a ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
Breezy View: Your Ideal Hawaiian Getaway
Welcome to Breezy View, the perfect retreat for your Hawaiian vacation. This spacious end unit features 1 bedroom, 2 bathrooms, and an additional loft space, comfortably accommodating up to four ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
