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 = 'waimanalo' OR postalCode = 'waimanalo' OR region like '%|waimanalo|%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Make unforgettable memories in this secluded, polynesian-styled beachfront estate situated on the eastern side of Oahu next door to the famous Magnum PI Robin's nest. Flanked by the majestic Ko’olau mountains on one side and a reef-protected ... Registration Number: 410020090000, GE-082-372-8128-01, TA-082-372-8128-01
3 bedroom
5 bathroom
Sleeps 8
Hawaii> Waimanalo
“Notice: In compliance with the Revised Ordinances of the City and County of Honolulu, the Property will be made available for your use and occupancy for a period of thirty (30) days.” Registration Number: GE-063-319-9104-01, TA-063-319-9104-01, 141002002000
9 bedroom
8 bathroom
Sleeps 20
Hawaii> Waimanalo
Note:Registration Number: TA-111-933-9520-01, GE-111-933-9520-01, 1410060400000
7 bedroom
4 bathroom
Sleeps 10
Hawaii> Waimanalo
Located in Waimanalo, this vacation home lets you experience it all. Fans of nature and the outdoors will love being within a 5-minute walk of Waimanalo Beach and 8 minutes by car from Bellows Field Beach Park. If you'd like to explore the ... Registration Number: 410050460000,TA-076-575-7440-01,GE-076-575-7440-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
"Hale Pohaku" - Waimanalo is a beautiful beachfront home on one of Oahu's most spectacular beaches; Waimanalo. Listed on the Register of Hawaii's Historic Homes this classic restored beach house was built of lava rock during the reign of King ... Registration Number: TA-059-395-6864-01, 410040420000, GE-059-395-6864-01
3 bedroom
3 bathroom
Sleeps 8
Hawaii> Waimanalo
WE ARE LITERALLY ON THE BEACH! Registration Number: GE-174-700-7488-01,TA-174-700-7488-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimanalo
Monthly Rental as required by Honolulu County. Please inquire before getting scared off by the rental rate or Honolulu's system. Will consider discounts as VRBO's system does not work for Honolulu. Beachfront with Hot Tub! White Sand Beach ... Registration Number: GE-062-561-8944-01, TA-062-561-8944-01, 410060010000
4 bedroom
6 bathroom
Sleeps 18
Hawaii> Waimanalo
Waimanalo Beach Cottages lets us share our aloha! Registration Number: 410070650000, W45054461-01, TA-576-480-6660-03
9 bedroom
9 bathroom
Sleeps 24
Hawaii> Waimanalo
This rental is across the street from one of the nicest beaches on the island. Don't take our word for it check out the pictures of Waimanalo Beach and Bellows Beach. Registration Number: GE-002-351-5136-01, 410070130000, TA-002-351-5136-01
1 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
The Beach Haven is a beautiful home that can make your vacation very special. Only steps from one of the best beaches in Hawaii, you will feel the ocean breeze surround you. The home is treetop level which provides views of the ocean and of the ... Registration Number: 410050460000,TA-076-575-7440-01,GE-076-575-7440-01
4 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimanalo
Minimum 30 day or longer booking only. Newly renovated with full, quiet, split air conditioning. Nestled directly across from one of the most beautiful white sand beaches on Oahu, Paradise Found Beachside Suite offers peace, tranquility and ... Registration Number: TA-203-731-1488-01, GE-203-731-1488-01, 410060130000
0 bedroom
0 bathroom
Sleeps 2
Hawaii> Waimanalo
300 feet from the Exquisite Waimanalo Beach. Fully furnished 3 bedroom, 2.5 bath home with everything you need for an ideal Hawaiian vacation! We are delighted to be able to continue to provide a truly Aloha spirit inspired vacation to new ... Registration Number: 411234567892, GE-142-816-0512-01, TA-142-816-0512-01
3 bedroom
3 bathroom
Sleeps 6
Hawaii> Waimanalo
WE ARE LITERALLY ON THE BEACH! Registration Number: GE-174-700-7488-01,TA-174-700-7488-01
1 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimanalo
This spectacular property is located beach front and has the most amazing ocean views, lots of room to enjoy this pristine beach environment and an ultimate decor with a french touch. All the bedrooms enjoy a private, en-suite bath and all have ... Registration Number: TA-199-621-0176-01,+GE-199-621-0176-01
5 bedroom
6 bathroom
Sleeps 10
Hawaii> Waimanalo
Located in the Waimanalo Beach Lots, which is only 25 minutes from Honolulu and Waikiki. Waimanalo Beach is one of the largest, cleanest, and most private beaches on Oahu. This white sand beach is very safe (referring to the ocean/surf) so you... Registration Number: 410070800000, TA-002-351-5136-01, GE-002-351-5136-01
2 bedroom
2 bathroom
Sleeps 9
Hawaii> Waimanalo
Our property includes a 4 bedroom main house as well as a 2 bedroom cottage that sleeps up to 14 guests comfortably. The property includes ocean views, a rooftop deck, a heated pool, and a hot tub. You will enjoy breathtaking views of the ... Registration Number: TA-106-722-5088-01, 410060570000, GE-106-722-5088-01
6 bedroom
4 bathroom
Sleeps 14
Hawaii> Waimanalo
This home includes ocean views, a rooftop deck, and a heated pool. Enjoy breathtaking views of the majestic lava-formed Ko'olau mountain range and only steps to the # 1 rated beach In the world (2015), Waimanalo Beach, famous for the filming of ... Registration Number: 410060570000, GE-106-722-5088-01, TA-106-722-5088-01
4 bedroom
3 bathroom
Sleeps 10
Hawaii> Waimanalo
This incredibly located and luxurious ocean-view Hawaiian beach cottage is just steps to 2015's # 1 rated beach in the USA, Waimanalo Beach. In a quiet Hawaiian village, you'll awaken to the sounds of birds, the fragrance of flowers, and waves ... Registration Number: 410060570000, GE-106-722-5088-01, TA-106-722-5088-01
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
Note:Registration Number: 1410060400000, TA-111-933-9520-01, GE-111-933-9520-01
9 bedroom
5 bathroom
Sleeps 13
Hawaii> Waimanalo
Note:Registration Number: GE-111-933-9520-01, 1410060400000, TA-111-933-9520-01
3 bedroom
2 bathroom
Sleeps 6
Hawaii> Waimanalo
Take a journey back in time at Hawaii Hobbit House. This cozy, yet abundant luxury villa is shrouded in charm as well as amenities for your whole party to enjoy. Equipped with 3 bedrooms, 3 full bathrooms and accommodating 12 people, you and ... Registration Number: TA-030-510-1312-01, 1410070640000, GE-030-510-1312-01
3 bedroom
3 bathroom
Sleeps 12
Hawaii> Waimanalo
Note:Registration Number: GE-111-933-9520-01, TA-111-933-9520-01, 1410060400000
2 bedroom
1 bathroom
Sleeps 4
Hawaii> Waimanalo
Our cottage, in the Waimānalo Beach Lots, is available starting early April.Registration Number: TA-000-786-0736-01,GE-000-786-0736-01,41006059
2 bedroom
1 bathroom
Sleeps 5
Hawaii> Waimanalo
