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 = '96734' OR postalCode = '96734' OR region like '%|96734|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Nestled in the highly sought after wonderful Lanikai community, this beautiful executive home was newly built in 2014. This home is a perfect vantage point.Registration Number: TA-209-884-5696-01, GE-209-884-5696-01, 430050050000
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Beachfront! The White Sand of Kailua Beach at Your Door!Registration Number: TA-189-305-8560-01,+GE-189-305-8560-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Registration Number: GE-030-384-5376-01,+TA-030-384-5376-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Kailua
This gorgeous 5 bedroom, 4.5 bathroom, gated oceanfront villa was built in 1918 and has just been fully remodeled with an open, contemporary beach house feel while still honoring the original architecture of the home. In the renovation we ... Registration Number: GE-180-925-6448-01,TA-180-925-6448-01
5 bedroom
5 bathroom
Sleeps 15
Hawaii> Kailua
Only steps to beach through private street gate, and a short 1.5 mile bike to Kailua town. 2 bedrooms, family room, and kitchen on 2nd floor; laundry, 1 bedroom and bath on 1st floor. Washer/Dryer, Basic Cable, and Wifi includded. Small pets ok.... Registration Number: 430180410000,TA-199-189-0944-01,GE-199-189-0944-01
3 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
With front row seats to the ocean and the famous Mokulua Islands of Kailua, Lanikai Oceanside offers the best seats in the house. Newly renovated, this tropical chic and modern oceanfront beach home is open and spacious, yet warm and intimate. ... Registration Number: 430030630000, TA-141-510-3488-01, GE-141-510-3488-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
Hale Kea is a newly renovated Hawaiian-style home designed for casual living and family entertainment; just steps to beautiful Kailua Beach.Registration Number: GE-065-681-2032-01, TA-065-681-2032-01, 430100170000
3 bedroom
3 bathroom
Sleeps 5
Hawaii> Kailua
Tropical Paradise with your own yard... Over 800 square feet of living.Registration Number: TA-133-517-5168-01,+GE-133-517-5168-01
0 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua
Hale Kainalu is a sweet getaway at the end of Lanikai with exclusive secluded beach used by only 5 houses within a gated drive. This beautiful cottage is literally right on the beach & overlooks the crystal clear, blue and green hued water. Wake... Registration Number: GE-180-925-6448-01,TA-180-925-6448-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
TA-006-302-3104-01Registration Number: GE-006-302-3104-01,TA-006-302-3104-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
A comfortable beachfront home set in a security-gated neighborhood with central air condition. Decorated with island style rattan furnishings, ceiling fans throughout the house, cool white tile, granite counters and wooden flooring. There is a ... Registration Number: 430180100003, TA-083-865-6000-01, GE-083-865-6000-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
Just steps away from coveted Lanikai Beach, sits our newest luxury rental - Lanikai Breeze. Built in 2017, this brand new construction boasts upgraded appliances and furnishings meticulously hand picked by local designer, Lotus Home Staging. ... Registration Number: GE-135-880-0384-01, 430070810000, TA-135-880-0384-01
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Kailua
VIEWS!! Absolutely beautiful right on Kailua beach with pool and spa . Registration Number: TA-095-587-3792-01, GE-095-587-3792-01, 430830080000
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Kailua
Indulge in the tranquility of Lanikai Beach, just a stone's throw away, and let the allure of this coastal paradise wash over you. With the studio's inviting comforts and the endless ocean possibilities at your disposal, your stay promises to be... Registration Number: GE-095-917-0560-01, 143005018000, TA-095-917-0560-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
This Kailua, beachside, pool home was completely remodeled in 2019 with all new kitchen, bathrooms, pool house, pool surround, landscaping, TV's, furnishings, etc. And NO CLEANING FEE on 30 day bookings!Registration Number: GE-174-185-2672-01, TA-174-185-2672-01, 430190450000
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
ORCHID HOUSERegistration Number: GE-087-311-0016-01,TA-087-311-0016-01
3 bedroom
4 bathroom
Sleeps 7
Hawaii> Kailua
Experience luxurious coastal living at Lanikai Seashore, a tropical two-story vacation rental just steps away from the renowned Lanikai Beach. This villa’s spacious open floor plan and unobstructed ocean views makes one feel they’re almost ... Registration Number: 430050620000, TA-151-764-5824-01, GE-151-764-5824-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Kailua
30 Night Minimum Stay.Registration Number: 143024003000, GE-111-409-1520-01, TA-111-409-1520-01
0 bedroom
1 bathroom
Sleeps 2
Hawaii> Kailua
Let the relaxing atmosphere of Lokomaika’i Kailua (meaning Gracious Kailua) whisk you into vacation mode. The home includes 2 primary suites that beckon you to the outdoor areas of the pool and garden. There are also 2 additional bedrooms and ... Registration Number: TA-079-127-1936-01, GE-07
5 bedroom
3 bathroom
Sleeps 11
Hawaii> Kailua
Hale Ohana meaning Family House is just that! With multiple lounge areas, pool, hot tub, game room inclusive of a spider man pinball game, ping pong table and much more this home is perfect for a family get away! The home includes 3 bedrooms in ... Registration Number: GE-139-302-3488-01, TA-139-302-3488-01, 430830350000
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Kailua
Beautiful attached guest suite. This Private guest suite has a full kitchen, washer/dryer, stainless steel appliances and a walk in glass shower. It has its own separate private entrance. There is also plenty of street parking. The suite is a ... Registration Number: TA-195-394-1594-02,+GE-195-394-1504-01
1 bedroom
1 bathroom
Sleeps 3
Hawaii> Kailua
One of the original homes on Kailua Beach, this Renovated 1937 five bedroom Cottage is just one house from the middle of Kailua Beach, the #1 Beach in the world. Nestled between a park and a lightly used club with only one beachfront neighbor,... Registration Number: GE-045-319-8848-01, TA-045-319-8848-01, 430160220000
5 bedroom
2 bathroom
Sleeps 10
Hawaii> Kailua
Aloha and Welcome to Kolea LandingRegistration Number: 430070230000, GE-082-679-2448-01, TA-082-679-2448-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
** Calendar may not always have latest available dates or be updated. Hale Anuenue i Lanikai may be available - always reach out to us on the 'contact host' link for best and latest information **Registration Number: 430030070000, GE-067-636-2752-01, TA-067-636-2752-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Kailua
Welcome to Lanikai Hale ~ Two Hawaiian style cottages (which can be rented together or separately) on a large fenced-in property in the heart of Lanikai, just 2 miles from Kailua Town on the Windward side of Oahu.Registration Number: TA-107-927-8080-01,+GE-107-927-8080-01
5 bedroom
3 bathroom
Sleeps 12
Hawaii> Kailua
Recently updated with stylish contemporary design, this spacious Kailua home sits on Kailua Beach with nearly 100 linear feet of beach frontage. The home features direct and private access to the white sands with over 2 miles of beach walking ... Registration Number: GE-176-489-9328-01, TA-176-489-9328-01, 1430200510000
6 bedroom
7 bathroom
Sleeps 14
Hawaii> Kailua
The ultimate beach lifestyle awaits you at idyllic Villa Hui Hou on O’ahu’s scenic windward coast. Located off the main strip in the exclusive neighborhood of Lanikai, this recently remodeled five bedroom, four and a half bathroom property ... Registration Number: 430030440000, GE-091-214-0288-01, TA-091-214-0288-01
5 bedroom
5 bathroom
Sleeps 12
Hawaii> Kailua
Welcome to Holoholo Hale in the beautiful town of Kailua on the island of Oahu. Kailua is the home of two of the best beaches in the world, Kailua Beach Park and Lanikai Beach. The home is a short drive to all the island has to offer including ... Registration Number: GE-141-882-0096-01, TA-141-882-0096-01, 420560420000
7 bedroom
4 bathroom
Sleeps 14
Hawaii> Kailua
The Pool House in Kailua offers 3,200 sq ft with 6 bedrooms, 4 baths and one amazing pool. Located in a peaceful and lush neighborhood with views of the Ko’olau Mountain range, it is an easy walk to Kailua Beach, Lanikai Beach and Buzz's ... Registration Number: GE-036-329-7280-01, 420540580000, TA-036-329-7280-01
6 bedroom
4 bathroom
Sleeps 18
Hawaii> Kailua
The HomeRegistration Number: 430670160000, GE-011-101-1840-01, TA-011-101-1840-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua
