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. It boasts 3 bedrooms en suite that are generous in size and more than ample in ...
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! This 4 bedroom, 4 bath, en suite home is the Perfect Tropical Haven. Tucked away and private, this home embodies the true island lifestyle. Walk or jog the long stretch of beach...
Registration Number: TA-189-305-8560-01,+GE-189-305-8560-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Kailua
Our beautiful home has been featured in Hawaii Home Magazine and has received a best in class award from the Hawaii Builders Association. The home is within a 2 minute walk to Kailua Beach Park, the Kalapawai Market and Buzz's Steak ...
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. There are two master bedrooms with in suite bathrooms and a guest bedroom with a double bed. All...
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. Our newly renovated cottage is the perfect getaway located in the heart of Kailua. Light-filled and breezy this is the perfect space for one or a couple, just ...
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-01 Beautiful ocean front home at the beginning of Oahu's famous Lanikai Beach!. 2 bedroom, 1 bathroom main house and 2 bedroom, 2 bathroom guest cottage. Featuring breathtaking views, a brand new courtyard pool with ...
Registration 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 . Six bedrooms of beach-front paradise in Kailua. Swimming pool, hot tub, game and TV room make this property an ideal vacation recreation ...
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! Each room has an individually ...
Registration Number: GE-174-185-2672-01, TA-174-185-2672-01, 430190450000
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kailua
ORCHID HOUSE TA # 087-311-0016-01 With magical views of the Mokuluas from most rooms, this 3 bedroom, 4 bath custom home was just renovated using exotic hardwoods and stone from around the world. Features include solid hardwood floors,...
Registration 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. Turn key ready. Well appointed, private and serene studio apartment within walking distance to beach. Parking on site. Ideal for a single person or a couple. Upon entry, you will be welcomed into a ...
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 Landing A short walk (less than 400 steps to be exact) and you will be relaxing at Lanikai Beach! Our 2,200 sf guest house is the perfect blend of island and modern living. Our guest house has a master bedroom ...
Registration 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 ** A warm ALOHA and E KOMO MAI (welcome)! ...
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. There is plenty of parking ...
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 Home Our home is your home. With a fenced back yard, and plenty of spaces to relax including our covered lanai, you can sip a cocktail, read your novel and enjoy the Hawaiian sunshine in total relaxation. Your family will be close ...
Registration Number: 430670160000, GE-011-101-1840-01, TA-011-101-1840-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kailua