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 = '96816' OR postalCode = '96816' OR region like '%|96816|%') AND sleeps>=1 order by rating desc LIMIT 0, 50
Treat yourself to ultimate island luxury in this architecturally designed $5.5M home on 15,000 sq ft lot in one of the most desired locations on Oahu's south shore. Nestled on the slopes of Diamond Head just outside of Waikiki, this exquisite ... Registration Number: 350020180000, GE-047-203-9424-01, TA-047-203-9424-01
4 bedroom
6 bathroom
Sleeps 9
Hawaii> Honolulu
**Minimum 30 days required for reservation**Registration Number: 230360390448, TA-008-444-7232-01, GE-008-444-7232-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
Enjoy magnificent unobstructed views of the ocean, Diamondhead and Waikiki from this historic vacation home while living in a spacious, updated house with loads of amenities. Plan your day, drinking a mellow cup of Kona coffee on the lanai, ... Registration Number: GE-107-728-0768-01, TA-107-728-0768-01, 330310160000
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
This home-away-from-home exudes island style and comfort! Open, airy Kahala Cottage features two charming living spaces, a nicely-appointed kitchen, and a dedicated dining area that allow guests to enjoy quality time together at home. Flexible ... Registration Number: TA-038-170-4192-01, GE-038-170-4192-01, 350150260000
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
For discounted rates on Main house only, please inquire. 12 Night Minimum over the Christmas/New Years Holiday Season.Registration Number: GE-005-323-3664-01, TA-005-323-3664-01, 90/TVU-0376, 350060160000
7 bedroom
7 bathroom
Sleeps 14
Hawaii> Honolulu
Kahala Oceanside is a private paradise retreat that comfortably sleeps 14. This luxurious ocean front hideaway on the South Shore of Oahu, known for its spectacular sunsets, is just minutes away from fine eateries, world-famous hikes, and the ... Registration Number: 1350030350000, GE-053-250-7136-01, TA-053-250-7136-01
6 bedroom
7 bathroom
Sleeps 15
Hawaii> Honolulu
Let us welcome you to Ohana Paradise, a sophisticated, tropical retreat located in one of the most affluent and prestigious areas of Diamond Head-Kahala, world renowned for its beauty, beaches, parks, restaurants, and family fun. Leave the ... Registration Number: GE-203-504-0256-01, TA-203-504-0256-01
6 bedroom
5 bathroom
Sleeps 12
Hawaii> Honolulu
This spectacular French Tudor exudes a casual elegance and the comfort of the islands. Inside you will find gleaming wood floors, an elegant fireplace, crown molding and spacious bright rooms. The kitchen is fully equipped with a new ... Registration Number: TA-112-501-9648-01, GE-112-501-9648-01
4 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Prices start at $150,000/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.Registration Number: TA-199-064-7808-01, GE-199-064-7808-01, 350020500000
8 bedroom
10 bathroom
Sleeps 16
Hawaii> Honolulu
Kahala Luxury LivingRegistration Number: TA-088-184-4224-01, 350100100000, GE-088-184-4224-01
5 bedroom
4 bathroom
Sleeps 12
Hawaii> Honolulu
Newly renovated 3 bedroom, 2.5 bath home on Maunalani Hts. Tastefully furnished with high quality bedroom, living room and dining room furniture. HD Smart T.V.s in living room, kitchen and bedroom. Fully functional kitchen with new dishes, ... Registration Number: TA-187-879-4240-02, 330180700000, GE-186-879-4240-01
0 bedroom
0 bathroom
Sleeps 6
Hawaii> Honolulu
Experience the laidback luxury and authentic island living of Kahala Breeze, a spacious four-bedroom home that features elegant decor and design cohesive with the natural beauty that surrounds it. Registration Number: 350030310000, TA-065-534-9760-01, GE-065-534-9760-01
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
30 night minimum Registration Number: TA-108-852-8384-01, GE-108-852-8384-01, 13526023
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Honolulu
Welcome to Kahala Seaside, a spacious two-story garden view home featuring four bedrooms, three-and-a-half bathrooms, and lush garden views. Nestled in the upscale seaside burg of Kahala, you’ll have access to all the fun and excitement of ... Registration Number: 350040510000, TA-188-470-4768-01, GE-188-470-4768-01
4 bedroom
4 bathroom
Sleeps 7
Hawaii> Honolulu
FORGET YOUR WORRIES IN THIS SPACIOUS AND SERENE SPACE. THIS MODERN RESIDENCE IS NEWLY RENOVATED AND EXTREMELY CLEAN. LOCATED ON THE BEAUTIFUL KAHALA BEACH. UNIT OFFERS PRIVATE OUTDOOR PATIO WITH SEATING AND DINING TABLE TO HELP YOU ENJOY THE ... Registration Number: TA-058-184-0896-01, 350230020118, GE-058-184-0896-02
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience unparalleled luxury in this Oahu gem, perfectly positioned between the esteemed Waialae Country Club and the illustrious Kahala Hotel. Just a whisper away from the beach, this condo offers an intimate escape from Waikiki's fervor, yet... Registration Number: TA-130-276-7104-01, GE-130-276-7104-01, 350230020018
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Welcome to our newly renovated slice of paradise at Kahala Beach! Nestled in one of the most sought-after neighborhoods in Honolulu, this spacious 3-bedroom, 2-bathroom property promises a luxurious escape for your Hawaiian getaway.Registration Number: TA-052-736-1536-01, GE-052-736-1536-01, 1350230020025
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Steps from beautiful Kahala Beach, you will find your Estate. As soon as you open the front gate, you will be welcomed with lush tropical landscape.Registration Number: GE-046-812-9792-01, TA-046-812-9792-01, 350070020000
3 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
A 10-minute walk from Waikiki, discover your own private paradise nestled at the base of Diamond Head in Honolulu’s renowned Gold Coast. Built in 1926 and beautifully remodeled with modern luxuries, Hale Mahie is notable as an example of the ... Registration Number: TA-044-602-3680-01, 310330200000, GE-044-602-3680-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
~Breezy Spacious and Welcoming Vacation Home~Registration Number: GE-122-615-2448-01, TA-122-615-2448-01, 330310320000
4 bedroom
3 bathroom
Sleeps 7
Hawaii> Honolulu
Let your dream tropical vacation begin when you step into the olden days of Hawai’i here at Hale Le’ahi, an illustrious and expansive 3 story plantation board and baton wood-framed estate located on the lower slopes of the world famous ... Registration Number: TA-021-803-2128-01, GE-021-803-2128-01, 1350020240000
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
This 2 bedroom sleeps up to 6 in the valley above Diamond Head known as Kahala. Its the perfect balance of close to Waikiki and the highway but away from the hustle and bustle of things. Less than a $15 Uber/Lyft ride to Waikiki if you want to ... Registration Number: TA-157-641-9328-01, GE-157-641-9328-01, 1350550410000
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Honolulu
Waikiki Sunset #2503 – 229 Paoakalani Ave.Registration Number: 260280110249, GE-159-961-1904-01, TA-159-961-1904-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
On the beach, in a coveted luxury resort location are the Kahala Beach condos, adjacent to the Kahala Hotel, the only 5-star Hawaiian destination. Secure, with 24/7 guards, keyed building access, and a private underground parking garage. Sited ... Registration Number: GE-165-278-3616-01, TA-165-278-3616-01, 1350230020146
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
THANKSGIVING - STILL AVAILABLE - please send a message to inquire. GREAT DEALS!Registration Number: 310470430000, GE-151-467-4688-01, TA-151-467-4688-01
4 bedroom
4 bathroom
Sleeps 12
Hawaii> Honolulu
($99- Clean Fee special for Spring bookings, please inquiry. Car/ Suv Available at discounted rate- pls inquire)Registration Number: TA-100-574-6688-01, 350230020154, GE-100-574-6688-01
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
Begin your Hawaii vacation by stepping into a Balinese-Style Oasis located just mauka (the mountain side) of Diamond Head and minutes from the beach. This 5 bedroom, 4 bath home combines pleasingly aesthetic features, plentiful space and an ... Registration Number: 132053001000, Ge-096-362-7890-01, Ta-096-362-7890-01
6 bedroom
4 bathroom
Sleeps 13
Hawaii> Honolulu
Gated custom designed immaculate contemporary ocean view home with outdoor deck, covered lanai, unheated pool and heated jet spa in quiet Diamond Head Kahala cul-de-sac on Diamond Head Crater slope. Ocean and Koolau mountain views from every ... Registration Number: 131048062000, GE-098-016-0512-01, TA-098-016-0512-01
4 bedroom
4 bathroom
Sleeps 8
Hawaii> Honolulu
The Kahala Beach Condo Is Fully Renovated with Koa Wood Flooring Throughout. Registration Number: GE-177-124-3520-01, TA-177-124-3520-01, 350230020072
2 bedroom
2 bathroom
Sleeps 6
Hawaii> Honolulu
NEWLY RENOVATED!Registration Number: 310410210000, TA-096-182-0672-01, GE-096-182-0672-01
4 bedroom
4 bathroom
Sleeps 10
Hawaii> Honolulu
This lower level beautifully 2 bedroom, 2 bath home, 980 Sq. ft is perfect for enjoying your Hawaii vacation. This is an attached 2 bedroom, 2 bath fully equipped home with its own separate entrance. This home comfortably sleeps 4 adults. There... Registration Number: 1280300620000, TA-189-110-4768-01, GE-189-110-4768-01
2 bedroom
2 bathroom
Sleeps 5
Hawaii> Honolulu
Waikiki Banyan Ocean View Corner Condo comes with a fully equipped kitchen, which includes a refrigerator, stove-range, dishwasher, microwave, coffee maker, and all the kitchen supplies you need to have an at home cooking experience while on ... Registration Number: 260250050092, TA-121-578-0864-01, GE-121-578-0864-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Honolulu
This highly desirable top-floor unit overlooks the lovely koi pond and provides the best views of the sky and stars. The lanai is oriented to the afternoon sun and sunset. Importantly, its top floor position makes it one of the most quiet ... Registration Number: GE-003-036-7744-01, TA-003-036-7744-01, 1350230020145
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Honolulu
FEATURED IN ARCHITECTURAL DIGEST MAGAZINE! Registration Number: GE-092-683-8784-01, TA-180-161-9456-01, 31040027
4 bedroom
5 bathroom
Sleeps 8
Hawaii> Honolulu
Admiration starts at first glance with this contemporary vacation residence, located in a luxury residential area in Kahala, right at the slopes of Diamond Head. The luxury residence was completed in 2015, boasting a huge pool, luxurious rooms ... Registration Number: TA-139-412-1216-01, GE-139-412-1216-01, 350130320000
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Honolulu
This stunning property is now accepting longer-term stays at reduced rates. Please inquire for details.Registration Number: TA-104-487-9872-01, 350060190000, GE-104-487-9872-01
7 bedroom
8 bathroom
Sleeps 14
Hawaii> Honolulu
Beautiful house located two blocks from the beach with pool.Registration Number: TA-059-011-7376-01, GE-059-011-7376-01, 350410060000
5 bedroom
5 bathroom
Sleeps 5
Hawaii> Honolulu
Prime location on the elite side of the island (Kahala), next door to a 5 star resort and golf course with direct walking access to being on the beach (150 yards) from the condo. Condo is nicely furnished with high end furniture to make your ... Registration Number: TA-183-385-9584-01, GE-183-385-9584-01, 1350230020107
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Delightful breezes with up mountain coolness, quiet, upscale neighborhood with ocean views. Off street parking stall, yard, and laundry room included. Separate entrance in private home. Wk/Mo NOW 5**... Registration Number: 13324003, W00868236-01, TA-053-890-8672-01
2 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Prices start at $129,990/month and will not be pro-rated based on length of stay. Please inquire to get an accurate quote.Registration Number: TA-199-064-7808-01, GE-199-064-7808-01, 350020500000
4 bedroom
6 bathroom
Sleeps 8
Hawaii> Honolulu
A quiet peace within a paradise, located conveniently 5-minute drive from the heart of Waikiki is this luxurious mansion appropriately named Diamond Head Palace located on the skirt of Diamond Head National Park.Registration Number: GE-175-093-1968-01, 310480260000, TA-175-093-1968-01
4 bedroom
6 bathroom
Sleeps 8
Hawaii> Honolulu
A relaxing escape in Kahala high end condo. 2 Bedrooms with oversized master Bedroom and 2 Bathrooms. Full kitchen, dinning area, large living room with TV and high speed internet, amazing lanai with partial ocean view.Registration Number: TA-141-127-0144-01, GE-141-127-0144-01, 1350230020121
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Fabulous Kahala home close to Waialae Country Club, Kahala Beaches, Shopping, and Dining. 5 minutes from Waikiki and downtown! Great home offers privacy that opens up to the huge lanai with pool, spa, and cabana. True indoor/outdoor Hawaiiana ... Registration Number: GE-138-741-3504-01, TA-138-741-3504-01, 350300170000
5 bedroom
5 bathroom
Sleeps 10
Hawaii> Honolulu
Find peace and tranquility in your very own 5-bedroom, 5.5-bathroom villa here at Diamond Head Grandeur in the centralized neighborhood of Kahala on Oahu. Around the corner from the Diamond Head State Monument, this stunning property is just 15 ... Registration Number: 310400040001, TA-076-121-8560-01, GE-076-121-8560-01
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Honolulu
This beautiful studio is only a few steps from the beach. It is attached to a main house but it has its own entrance and private Lanai. Kahala Beach is only a 5 minute walk. Very quiet and safe location walk distance to Kahala Mall.Registration Number: TA-156-524-7488-01, GE-156-524-7488-01, 35008019
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
Experience a brand new home just 5 mins away from Waikiki and Diamond Head. This home has fully furnished 2 bedrooms, and 2 bathrooms and parking. A perfect place for family vacations, travel nurses, or group trips, enjoy restaurants and ... Registration Number: GE-140-425-7792-01, TA-140-425-7792-01, 320200060000
2 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Experience a brand new home just 5 mins away from Waikiki and Diamond Head. This home has fully furnished 3 bedrooms, and 2 bathrooms, and parking. A perfect place for family vacations, travel nurses, or group trips, enjoy restaurants and ... Registration Number: GE-140-425-7792-01,320200060000,TA-140-425-7792-01
3 bedroom
2 bathroom
Sleeps 10
Hawaii> Honolulu
Experience a brand new home just 5 mins away from Waikiki and Diamond Head. This home has fully furnished 5 bedrooms, 4 bathrooms, and parking areas. A perfect place for family vacations, travel nurses, or group trips, enjoy restaurants and ... Registration Number: GE-140-425-7792-01,320200060000,TA-140-425-7792-01
5 bedroom
4 bathroom
Sleeps 14
Hawaii> Honolulu
Immerse yourself in the calm of island living at this refreshed 3-bedroom, 3-bath retreat, where the peaceful sound of ocean waves welcomes you daily. Fully furnished for your convenience, this tranquil escape is ideal for relocating ... Registration Number: GE-050-497-2800-01, TA-050-497-2800-01, 1330180570000
3 bedroom
2 bathroom
Sleeps 8
Hawaii> Honolulu
Quiet town studio, very close to Waikiki, but far enough to get a good night's sleep.Registration Number: TA-157-800-8576-01, GE-157-800-8576-01, 132053020000
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Honolulu
