select * from (Select a.`postalCode`,a.`uniturl`, `bathrooms`, `bedrooms`,`registration_number`, `sleeps`,en_US_VRBO as href, `thumbnailUrl`, `headline`, description, `state`, `city`, `country`, `region`, `source`, `property_type`, `location_type`, `suitability`, `features`, `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) as vw where 1 and round(price)>0 and bedrooms>=0 and (registration_number like 'TA-%') AND (city = '96816' OR postalCode = '96816' OR region like '%|96816|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
Our Kahala Beach Apartment sits on one of the most valuable strips of beachfront in Hawaii. This Elegant apartment is Beachfront resort-style Living at its best. The Apartment is steps away from the 4 star Kahala Hotel with its Elegant dining ... Registration Number: TA-143-627-7248-01, GE-14
3 bedroom
3 bathroom
Sleeps 6
HI> Honolulu
The Historic Gingerbread House is a secure and gated residence directly across the street from Kahala beach. The neighborhood is between Diamond Head, the Kahala Hotel and the Waialae Country Club where the world renowned Sony Open is held. Our ... Registration Number: TA-019-756-4416-01
4 bedroom
4 bathroom
Sleeps 10
HI> Honolulu
Imagine staying 10 steps from a sandy beach with access to camping, fishing, surfing, and more...
Quiet, private, peaceful. Walk along the beach with your morning coffee. Relax to the sound of the waves as you lounge day and night next to the ... Registration Number: TA-127-322-3608-01, GE-12
2 bedroom
1 bathroom
Sleeps 6
HI> Honolulu
Please read all the descriptions. This listing is for the main floor apartment.
with 5 bed rooms and 2.5 bathrooms. There is another apartment with 3 and a attic bedrooms and 2.5 bathrooms which is on the top floor and we offer an option so ... Registration Number: TA-002-804-3264-01, GE-00
5 bedroom
2 bathroom
Sleeps 5
HI> Honolulu
** PLEASE NOTE:
- The tennis court is temporally closed until further notice -
POOL CLOSE for Maintenance FROM May 8th to May 18th, 2022
**POOL OPENS May 19th at 8am
SPAS CLOSE for Maintenance May 9th to May 27th, 2022
** SPAS OPEN May 28th ... Registration Number: TA-198-263-8592-01
1 bedroom
1 bathroom
Sleeps 5
HI> 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: TA-165-278-3616-01, GE-16
1 bedroom
1 bathroom
Sleeps 4
HI> Honolulu
2 Queen Sized Beds, 1 Bath, Waikiki Banyan, Free WiFi and Parking
Enjoy a fully-equipped kitchen and comfortable seating in the living room. Kitchen includes refrigerator, range, microwave, rice maker, coffee maker, and other standard kitchen ... Registration Number: TA-121-578-0864-01
1 bedroom
1 bathroom
Sleeps 6
HI> Honolulu
2 Queen Sized Beds, Waikiki Banyan Condo, Free Parking & WiFi
Enjoy a fully-equipped kitchen and comfortable seating in the living room which opens up to your private lanai. Kitchen includes refrigerator, range, microwave, rice maker, coffee ... Registration Number: TA-121-578-0864-01
1 bedroom
1 bathroom
Sleeps 6
HI> 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: TA-096-950-5280-01, 13205
6 bedroom
4 bathroom
Sleeps 13
HI> 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-13
5 bedroom
5 bathroom
Sleeps 10
HI> Honolulu
STUNNING new home with views of Diamond Head, the ocean and Waikiki. Super spacious of 1980 sq. ft., immaculate 3 bedrooms (4 BEDS! King bed & 3 queen beds) and 2 full bathrooms, open floor plan, huge and well equipped kitchen with cookware, ... Registration Number: TA-048-102-1952-01, GE-04
3 bedroom
2 bathroom
Sleeps 4
HI> Honolulu
Stunning brand new home with 3 bedrooms and 3 full bathrooms with beautiful views and absolute serenity. All new stainless appliances & new furniture, beautiful kitchen with granite and spectacular view and WIFI.!
Wake up to the best sunrise on... Registration Number: TA-048-102-1952-01, GE-04
3 bedroom
3 bathroom
Sleeps 3
HI> Honolulu
Nestled in beautiful Palolo valley just inland from Diamond Head, Honolulu, our family and PET-friendly fully furnished home is a spacious 3 bedroom retreat with mountain views near fantastic hiking trails, parks, beaches, surfing spots, and ... Registration Number: TA-155-402-0864-01, GE-15
3 bedroom
2 bathroom
Sleeps 4
HI> Honolulu
