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, 20
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 ...
- 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...
- 1 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
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 ...
- 3 bedroom
- 2 bathroom
- Sleeps 8
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...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kahuku
This corner unit Kuilima Condo is the perfect spot for your Hawaiian Vacation. Located on the esteemed Turtle Bay Golf course, home of the Fazio and Palmer Courses along with beautiful backdrops of the Turtle Bay Stables. This condo is an ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
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....
- 2 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 ...
- 1 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 ...
- 1 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 ...
- 1 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 ...
- 1 bedroom
- 2 bathroom
- Sleeps 5
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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kahuku
