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 and b.blocked=0 ) as vw
WHERE 1 = CASE
WHEN postalCode IN (96701,96706,96707,96712,6717,96730,96731,96734,96744,96782,96786,96789,96792,96826,96795,96797,96813,96814,96815,96816,96817,96818,96819,96821,96822,96825)
THEN
CASE
WHEN (registration_number like '%TA-%' or registration_number like '%TA %') THEN 1
ELSE 0
END
ELSE 1
END
and postalCode!=''and round(price)>0 and bedrooms>=0 AND sleeps>=1 order by rating desc LIMIT 0, 30
Punahele Penthouse Condo will fast become your favorite place to stay in Kailua-Kona! Punahele Penthouse is on the 7th (top) floor of the Kona Ali'i Condo Complex which is located in the heart of Kailua-Kona. You're within walking distance to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Paradise awaits you in this beautiful ground floor, one-bedroom, two-full-bath Lae Nani Condo, Unit #317 on the beautiful Garden Isle of Kauai!
Located on Kauai’s beautiful Coconut Coast (East side), this Condo has all the amenities you...
- 1 bedroom
- 4 bathroom
- Sleeps 4
Hawaii> Kapaa
Hale Nalu is our beautiful luxury Beach Villa in Ko Olina on the island of Oahu! Our fully equipped central AC 4th floor villa located in the Beach Tower at the gorgeous Beach Villas, unit #406. It’s situated on Lagoon 2. We offer LOCATION ...
Registration Number: GE-004-134-3488-01, TA-004-134-3488-01, 910570090195
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kapolei
Love to snorkel and want to see over 100 species of tropical fish & turtles? Then why not stay right across the street from Hawaii's best snorkeling beach?! Our modern beachfront unit offers gorgeous ocean/sunset views and the easiest access ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
SPECTACULAR VIEWS await you at Kahoa House for your Hawaiian holiday. Early-birds and mainlanders adjusting to the time difference can enjoy a cup of Ka’u coffee in the comfortable living room, and watch the sun rise out of the Pacific ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Hilo
Kauhale Makai (Village by the Sea) is paradise defined. This 2 bd/2 ba condo was stripped to the core and fully remodeled this year. The brand new kitchen has granite countertops with top of the line appliances. The bedrooms and bathrooms ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
ALOHA. Come vacation in our beautiful, newly remodeled 2BR/2BA beachfront home. Enjoy the breathtaking views of Kaho’olawe Island, Molokini Crater, the West Maui Mountains and of course the pristine Pacific Ocean and its shoreline. Sugar ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Kihei
Make your trip to Maui more memorable with a stay at our 5 star condo at Luana Kai. Luana Kai is a beautiful resort with immaculate grounds in a park like setting, ocean front in sunny Kihei with no busy roads to cross to get to the ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kihei
This is an end unit on the first floor at the back of the resort complex. You can enjoy the lush gardens and tropical breezes from the spacious lanai. The newly renovated condo has a master bedroom that includes a king size bed. The main living ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
The Artists House, Permit # STLA 2017/0002 is a home away from home with the comfortable furnishings and great amenities. The fabulous Lana'i sunsets can be enjoyed outside on the deck where there is glass dining room table with 6 chairs and bbq...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Lanai
Emmalani court is a small, quiet complex in Princeville, near the Princeville Resort. After visiting the 4 main islands in Hawaii, we decided on the North shore for it's lush setting and laid back vibe. There is truly no other place like it in ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Princeville
Enjoy sunset views every night from this beautifully updated 1br 1ba condo steps from Downtown Kailua-Kona village with AC in both the living room and bedroom (rare!).
Sunset in Paradise is your private paradise with gorgeous ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Welcome to Unit 1604 at the Fairways at Mauna Lani! This is the largest 1646 SF 2 bedroom floor plan where both bedrooms are located upstairs providing a peaceful sleeping area separate from the living area downstairs. They each have their own...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waimea
Our unit is spacious with high ceilings, ceiling fans, fabulous sunlight, and gorgeous ocean views. This elevated third story unit affords a wonderful lanai with a view overlooking manicured golf courses, tropical flowers, natural lava ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Aloha(Hello)! E Komo Mai (Welcome) to our beautiful luxury Beach Villa in Ko Olina on the island of Oahu! Our fully equipped central AC 9th floor villa, unit #922, is located in Ocean Tower on Lagoon 2, the least crowded of the four amazing ...
Registration Number: 910570090113, GE-139-917-7216-01, TA-139-917-7216-01
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kapolei
This is a an amazing 1 bedroom 1 Bath ground floor unit with plenty of space.It has been fully renovated with beautiful furnishings inside and out. It has lots of windows that lead out to a spacious patio. It has central air if the cool Hawaiian...
- 1 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> waikoloa village
Simply put, this property sits on one of the most gorgeous ocean-front locations in East Hawaii. Unlike other ocean-front homes, you will not need to walk to the end of a yard to appreciate the Pacific waves. Here, you will feel as if you are on...
- 2 bedroom
- 3 bathroom
- Sleeps 5
Hawaii> Hilo
The DREAM VACATION awaits You in this RICHLY DESIGNED Ground Floor Suite...
ELEGANTLY APPOINTED with Travertine Floors and DETAILED to Perfection. The Spacious Floor Plan includes a Private Patio and Expansive Tropical Garden Courtyard. ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Princeville
Permit STPH 2015/0002 GET: 146669772802 TAT: 14666977280
Paia Ko is a quiet, lovely, 3 bedroom 2 and a half bath home with beautiful ocean views and central air conditioning that backs up to an old sugar cane field. With the benefit of a ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Paia
Puako Pau Hana is a great place to escape from all your cares! Located in the heart of Puako, you are literally just a few steps to the most beautiful little beach in Puako!
Puako is known for it's magical old fashioned neighborhood feel and...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Waimea
Thank you for looking at my condo. Your visiting means the world to me and my Maui ohana. Come to keep Maui Strong!
See you soon. E Komo Mai!
My 1 bedroom/1 bath 2nd floor end unit condo is spacious and quiet. Located at Kihei ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Tax ID:TA-118-085-8368-01
ATTN: OUR COMPLEX IS REPLACING THE ROOF OF OUR BUILDING THE MONTH OF APRIL. PLEASE INQUIRE FOR SPECIAL APRIL RATE AND CONSTRUCTION INFORMATION.
To view a cinematic video of the property and surrounding ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Wailea
Enjoy gorgeous unobstructed ocean, marina & mountain views from our beautiful newly built guest house. Located on the ridge, in a safe, quiet and upscale neighborhood in Hawaii Kai, East Honolulu, just around the corner from famous Hanauma Bay, ...
Registration Number: GE-040-238-6944-01, TA-040-238-6944-01, 390750020000
- 2 bedroom
- 1 bathroom
- Sleeps 5
Hawaii> Honolulu
Aloha and welcome to our tropical paradise in beautiful Kauai, Hawaii!
Indulge in an extraordinary couples' getaway at our top-floor ocean view studio condo in Kapa'a. Just 160 steps from our condo's front door, the beach awaits! ...
- 0 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kapaa
Thank you for looking at my condo. Your coming to visit Maui means the world to me and my ohana. Come and keep Maui Strong!
See you soon. E Komo Mai!
This studio condo is quiet, clean and a holiday retreat with a great view. ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kihei
Capture the magic of this romantic cottage. Large picture windows, open beam ceilings and sky lights bring views of lush tropical gardens with towering Cook pines. Two covered lanais for pupus, mai-tais and outside dining. After an ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Hanalei
Lopaka's Family Lodge is a beautiful and immaculate unit in a tropical setting located above downtown Hilo. It is a spacious (840 sq. ft., plus 160 sq. ft. lanai) 1 BR, 1BA, and comes with everything you need including washer & dryer. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Hilo
Maui is outdoor living at its finest! Enjoy having breakfast lunch and dinner on your lanai staring at the ocean! Wake each morning to birds singing, swaying palm trees and the teal blue waters of the ocean.. there is no escaping the absolute ...
- 2 bedroom
- 2 bathroom
- Sleeps 5
Hawaii> Lahaina
Hale Aloha… On The Sand TVNC-4185
Anaik Martin, is the property manager, lives nearby and is available 24 hours a day, 7 days a week, (808) 652-3410. Professionally cleaned and disinfected by a licensed ...
- 6 bedroom
- 7 bathroom
- Sleeps 12
Hawaii> Anahola
Hale Aliomanu- a Private Beachfront Estate Home TVNC-4245
Anaik Matin, property manager, lives nearby and is available 24 hours a day, 7 days a week
Professionally cleaned and disinfected by a licensed company. There are at least 24 hours...
- 6 bedroom
- 6 bathroom
- Sleeps 12
Hawaii> Anahola
