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 = '96730' OR postalCode = '96730' OR region like '%|96730|%') AND sleeps>=1 order by rating desc LIMIT 0, 20
*30 day rental. Please inquire. 888-799-6920Registration Number: GE-152-452-7104-78, TA-152-452-7104-78, 260100112245
4 bedroom
3 bathroom
Sleeps 8
Hawaii> Kaaawa
Leave the city behind, escape the crowds! Breath the fresh ocean air from our large ocean view lanai! The home was renovated just a couple years ago and has been well maintained every since. Everything is like new, including furniture and ... Registration Number: GE-035-956-3264-01, 510110100000, TA-035-956-3264-01
2 bedroom
2 bathroom
Sleeps 4
Hawaii> Ka'a'awa
This country studio is close to family-friendly activities, public transport, the beach, 7-11, hikes, Uncle Bobo's, waterfalls, and mountains.. You’ll love my place because of the people, the neighborhood, the tranquility, the stars, the sound... Registration Number: GE-191-572-9920-01, 510140320000, TA-191-572-9920-01
1 bedroom
1 bathroom
Sleeps 2
Hawaii> Kaaawa
This Hawaiian home gives you the best of both worlds, nestled up close to the Koolau mountains with an ocean view. The house will accommodate 6 guests. Incl 3 bd/1ba, a kitchen & plenty of living space. We aim to make this space feel like your ... Registration Number: GE-191-572-9920-01, 51014032, TA-191-572-9920-01
3 bedroom
1 bathroom
Sleeps 6
Hawaii> Kaaawa
Cool ocean and mountain breezes welcome you to 'The Ko'olau Plantation Estate' your perfect Hawaiian Hideaway with over 60,000 square feet of outdoor space. With breathtaking ocean views and 25 feet from edge of property to the ocean. The ... Registration Number: GE-079-352-4736-01, TA-079-352-4736-01
9 bedroom
9 bathroom
Sleeps 14
Hawaii> Kaaawa
Property is currently for sale. The property manager will contact you to make arrangements to schedule showings at a convenient time. Registration Number: 510090250000, TA-212-043-5200-01, GE-212-043-5200-01
5 bedroom
4 bathroom
Sleeps 10
Hawaii> Kaaawa
From the moment that you walk through the cozy wooden gates, you can settle into the pleasantries of your Hawai’i vacation home at Pali Kai. Nestled into the quiet side streets off of the Kamehameha Highway in the small town of Ka’a’awa on... Registration Number: 510030410000, GE-083-429-3760-01, TA-083-429-3760-01
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Kaaawa
Located on the pristine coastline and turquoise waters of Kaaawa, Windward Oahu, this 2 bedroom 1 bath condominium provides guests with a luxery experience for a fair price. Escape the hustle and bustle of Waikiki and enjoy the more authentic ... Registration Number: GE-182-140-3136-01, TA-182-140-3136-01, 1170050110393
3 bedroom
1 bathroom
Sleeps 5
Hawaii> Kaaawa
Relax with the whole family at this peaceful country home that is within walking distance to beautiful Ka’a’awa Beach Park. Located just 7 minutes away from Kualoa Ranch- home of Jurassic Park, 17 minutes from the Polynesian Cultural ... Registration Number: GE-040-902-4512-03, TA-040-902-4512-01, 510110070001
3 bedroom
1 bathroom
Sleeps 8
Hawaii> Kaaawa
Welcome to the cozy Ka’a’awa Hale by Gather, a quaint three-bedroom, two-bathroom home between the lush Ko’olau Mountains and the serene Pacific Ocean. Located conveniently across from Ka’a’awa Beach Park and a short drive from Kualoa ... Registration Number: 510020210000, GE-144-378-4192-01, TA-144-378-4192-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Kaaawa
