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 (city = '96740' OR postalCode = '96740' OR region like '%|96740|%') AND sleeps>=1 order by rating desc LIMIT 0, 15
This Oceanfront Condo has an amazing view. It has a gorgeous Great Room and a beautiful fully equipped kitchen. With the roar of the ocean just a few yards away, you will not want to leave the Lanai after relaxing on the Tropitone furniture.
...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
A tropical Hawaiian getaway awaits your arrival at this updated Kona Coast condo perched above Keauhou Bay! Upon entering the unit, guests of this 2-bed, 2-bath property are treated to a quintessential island experience with a contemporary ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua Kona
On site for your convenience, just call Kiarah for any help you need. Her phone number can be found in our detailed manual that we provide that lists the destinations and amenities in the area, as well as resources and recommendations! This ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
HI> Volcano
Hale Iki (ha-lay ee-kee) is a centrally located private home (with A/C) at the 650' elevation, making for sunny days and cool nights. We're located in a quiet neighborhood, 3 minutes from the heart of Kailua-Kona and just about everything — ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
Relaxing is easy when listening to the sound of the waves from this cozy two-bedroom, two-bathroom ocean view suite. You’ll love how spacious this corner unit feels, with lots of room to sprawl out with family and friends.
Enjoy warm tropical...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> KAILUA-KONA
1 Bedroom 1 bathroom (Two Twin Beds or push together and make a King) 1 sofa bed in the living room. Also provided a portable Queen inflatable bed. Condo is in the Heart of Historic Kailua Kona village. Close and walkable, to all shops, ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
Experience ancient Hawaiian history over in Kaloko-Honokōhau National Historical Park, tour the royal Hulihe’e Palace and roam the rocky shoreline of Magic Sands Beach Park. All these attractions and more await within 10 miles or less of this...
- 3 bedroom
- 2 bathroom
- Sleeps 6
HI> Kailua-Kona
Tropical decor, surfboard art and views flowing “fore-ever” make this 1,850sf suite a winner! Replay the day on the lanai with a Volcano Winery Blush and fresh catch prepped in the modern kitchen. Enjoy a washer/dryer, flat screen TVs, WiFi,...
- 2 bedroom
- 2 bathroom
- Sleeps 8
HI> KAILUA-KONA
**COVID 19 Restrictions: The State of Hawaii has updated their Safe Travels program. Please refer to https://hawaiicovid19.com/travel/faqs/ for the latest information about traveling to Hawaii and how to bypass the mandatory 10 day quarantine ...
- 4 bedroom
- 5 bathroom
- Sleeps 8
HI> Kailua Kona
Ideally located on iconic Alii Drive directly where the world famous Iron Man takes place annually.
A number of palm trees and local landscaping on the front and side of the property add to the tropical appeal.
Nearby are many of Kona's ...
- 3 bedroom
- 3 bathroom
- Sleeps 8
HI> Kailua-Kona
Absolute direct oceanfront top floor, two-bedroom condo located in the heart of Kailua-Kona, Hawaii. As you walk through the door and see the blue ocean out the balcony, you have arrived in Hawaiian paradise!
Fully remodeled with high ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
HI> Kailua-Kona
Treat yourself to a delightful Hawaiian getaway when you stay at this 1-bedroom, 1-bathroom Kailua-Kona vacation rental condo that sleeps 4 guests. Set along the lush landscape of Kona Coast Resort, the corner unit condo offers access to ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Welcome to your tropical haven accommodating up to six! Perfect for families or for those looking for a little extra space to unwind after a fun day, you’ll love relaxing on the lanai with dining table, wet bar and mini-fridge. The luxe master...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> KAILUA-KONA
COVID-19 STATE LAW OF HAWAI’I: *BEGINNING OCT 15TH TRAVEL TO HAWAI'I PERMITTED WITH NEGATIVE COVID TEST 72 HRS BEFORE FLIGHT* ~Please visit the official government of Hawai'i website for more information~
*POOL & POOL DECK/BBQ PATIO AREA ...
- 0 bedroom
- 1 bathroom
- Sleeps 3
HI> Kailua-Kona
Located in the heart of Kailua Kona, this special retreat makes it easy to enjoy the day and night life of Kona town with restaurants and shops within a few steps. It is also an ideal launching point for day trips to state and national parks, ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
HI> Kailua-Kona
