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
Your Island Paradise Awaits You! Feel right at home in our spacious 1700 square foot, 3 bedroom, 2 1/2 bath Villa with an attached garage. This beautifully decorated Villa is just steps away from the resort style pool featuring a lava rock ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
VILLA'S ON THE PRINCE at Princeville in Hanalei Kauai 96722 sleeps six .
LUXURIOUS WELL, APPOINTED VILLA. Welcome to Kauai's upscale gated community ~ Villas On The Prince. Located just past the main water fountain at the entrance to ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> PRINCEVILLE, KAUAI
On the Kona side, safe and away from the volcanic activity, the Cottage awaits you.
The perfect, peaceful getaway for those on a honeymoon or looking for a relaxing, romantic experience. This warm, welcoming, newly-built and finely ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Aloha,
In regard to the Covid-19 restrictions, Hawaii Governor David Ige has announced that people traveling to the state now must be in possession of the results of their COVID-19 tests before departing on the last leg of their trips. If ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Ocean View
Aloha! Come and experience your Maui dream vacation!
This newly renovated Kahana Manor condo located between Napili – Honokowai and Kaanapali, in one of the most popular areas in West Maui often referred to as Kapalua Bay. Less than ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Recently updated ocean view home on an ocean front peninsula. Enjoy the views of Laie Bay from the comforts of the home or deck of the house, or venture to the Ocean Front cliffs right in the back yard and indulge in the beauty captured only ...
Registration Number: GE-061-340-5184-01,TA-061-340-5184-01
- 4 bedroom
- 3 bathroom
- Sleeps 14
Hawaii> Laie
This third floor corner unit at Pacific Shores in Kihei is the best unit in the complex. Offers privacy and an amazing sunrise view of Haleakala. You will be so comfortable in this unit that it may be hard to leave for your beach walk! Only ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kihei
30-day rental. Message us for details. Friends and Family fun await you.
Located in Makaha Valley Resort District, perfect for 5 to 8 guests. Walk out the gate directly onto famous Makaha Surfing Beach. We have 2 Bungalows on our property. ...
Registration Number: 840080160002, GE-088-190-7712-01, TA-088-190-7712-01
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Waianae
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: TA-047-203-9424-01, 350020180000, GE-047-203-9424-01
- 4 bedroom
- 6 bathroom
- Sleeps 9
Hawaii> Honolulu
County of Maui Vacation Rental Permit Number: STWM 2017/0006, TMK: 2430020770001
Owner Operated, Transient Accommodation (TA) Registration Number: TA-155-824-1280-01. Loud parties are not permitted on property (see policies), only REGISTERED ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Lahaina
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
As seen on Kauai-TV this is an amazing 5 bedrooms, 4 Full Bathrooms. That’s 3 Master Bedrooms and 2 Guest Suites.
One full kitchen, one kitchenette, three living areas, and 5 lanais. And Aloha Glore!
From the moment you enter ...
- 5 bedroom
- 4 bathroom
- Sleeps 10
Hawaii> Princeville
West Maui Welcomes You Back October. Our communities of Kaanapali, Honokowai, Kahana, Napili, and Kapalua; we all welcome you. Please have compassion and malama (care for) the island during your visit. Please stay away from Lahaina Town while it...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Lahaina
Aloha and welcome to our home, villa #311. Our villa offers concierge service to all of our guests. We have ocean views, a private pool with spa, and gourmet kitchen with professional grade appliances. The villa is bright, airy and spacious with...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kamuela
Aloha! Welcome to the luxurious Maui Vista 1315!
This beautiful property can be your own personal paradise and has everything you need to create the memories of a lifetime.
Inside this unit, you’ll find classy décor with a ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kihei
Haliipua Villas are the newest generation of multi-family villas at Hualalai offering an array of designer finishes throughout the home. Special features include luxurious mahogany finishes, island-inspired ceiling treatments, enhanced ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
At the forefront of East Shore accommodation is the Waipouli Beach Resort. This World Class Resort provides luxury at your fingertips and offers opulent amenities for a variety of occasions.
A suite with crisp and classy Hawaiian feel,...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kapaa
Don’t miss out on the opportunity to stay at one of the few full ocean view penthouses at Kolea. This 3 bed/3.5 bath ocean view villa is very nicely furnished. The first master bedroom has a king size bed with a LCD television. The first ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Waikoloa Village
Aloha, and welcome to 9B at Kolea, your beachfront Luxury Villa. This magical vacation home within an exclusive gated community with direct beach access offers ample square footage of living space under roof (including Lanai). You will feel ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Waikoloa
Set well back from the road, within Waikoloa Beach Resort yet very much apart from it, gates ensure your privacy. You’ll be near shops, restaurants and entertainment, but with the amenities of this home you may choose to remain on the ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
Hawaii> Waikoloa
Halii Kai 11F offers a great combination of space and view. This three bedroom townhouse style villa overlooks the fairway and signature hole of the Waikoloa Beach golf course and has an amazing ocean view. Enjoy watching the surf come in and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Waikoloa
vrKCV - Country Club Villas 315, Professionally Managed by Kona Coast Vacations
Welcome to one of the more popular locations of Keauhou! Unit 315 is situated right on the 18th fairway of the Kona Country Club Ocean Golf ...
- 2 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Enjoy luxury living at its best in this exclusive and very private Kukuiula Makai Cottage #53. Situated in Poipu's newest residential neighborhood this home has all you need to enjoy fun in the sun on Kauai's south shore. With two master suites ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Hanalei Makai Hale ... TVNCU #1157
Property located in Tsunami Evacuation Zone
GE-160-126-3616-01, TA-160-126-3616-01, TMK-550010290002
No Air Conditioning
No Smoking
Wake up in a Hanalei postcard at this extraordinary...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Hanalei
One of the best kept secrets in the Poipu Beach Resort area, Waikomo Stream Villas is a lush garden resort with 60 Kauai condos located on the sunny side of Kauai. Centrally located and across the road from Kauai’s premier retail destination ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Koloa
One of the best locations in sunny Poipu Beach Kauai, Nihi Kai Villas Resort is just steps away from the Poipu shoreline and a little over 300 yards to world-famous body surfing Brennecke’s Beach and Poipu Beach Park. The plantation style ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa
Enjoy island living with friends and family while seeing the ocean views and feeling the ocean breezes from the open and spacious second level great room of Hale Aloha Kai. This home is conveniently located just a few steps away from the beach ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Koloa
An oceanfront Kauai resort in Poipu Beach featuring spacious Kauai condos and spectacular sunset views. With its classic Hawaii plantation style architecture and design, complemented by a wonderful pool and guest amenities, this is a sought ...
- 1 bedroom
- 2 bathroom
- Sleeps 2
Hawaii> Koloa
One of the most luxurious oceanfront Kauai vacation rental resorts in Poipu, Whaler’s Cove offers serenity and intimacy. Set on a point overlooking Koloa Landing, the resort features commanding 180 degree views of the coastline. The sounds of ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Koloa