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 = 'Kailua-Kona' OR postalCode = 'Kailua-Kona' OR region like '%|Kailua-Kona|%') 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
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
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
Centrally located in North Kona, this house is 15 minutes from the airport, downtown and sandy beaches. It is situated at a cool 1000 foot elevation, and is located in a traditional Hawaiian neighborhood with large lots, mature trees and no ...
2 bedroom
2 bathroom
Sleeps 4
HI> Kailua-Kona
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 crafted ...
1 bedroom
1 bathroom
Sleeps 2
HI> Kailua-Kona
Enhance your Big Island experience with a stay at this beautiful, custom designed home on Mt. Hualalai. Located on a secluded park-like setting only 15 minutes from the Kona International Airport, at a very comfortable elevation of 1600 feet. ...
2 bedroom
3 bathroom
Sleeps 2
HI> Kailua-Kona
