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 (city = '96740' OR postalCode = '96740' OR region like '%|96740|%') 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
Guests walk in and we see wow on their faces...gourmet kitchen, ocean views, huge bedrooms.
"perfect for a family reunion"
"cool at night was just right"
"close to the best beach in Hawaii"
"your own private estate"
"amazing...
- 5 bedroom
- 6 bathroom
- Sleeps 18
Hawaii> Kailua-Kona
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
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 our beautifully decorated ocean front condo in paradise - Kona Reef A-22.
Our condo is our home away from home, so we respectfully request a maximum of 2 adult guests and no children under 18.
Whether this is your ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
This breathtaking 3 Bedroom plus bunkroom, 2 bath single level home is located just minutes from Kailua, Kona and some of the most beautiful beaches in Hawaii. Everything in this house is new! Fresh full remodel just completed. This includes a ...
- 4 bedroom
- 2 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
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
Welcome to this wonderful 3 bedroom / 2 bathroom duplex home only a 3 min walk from Magic Sands Beach. Just remodeled and professionally decorated, this charming vacation getaway has all of the comforts you desire in your home away from home, ...
- 3 bedroom
- 2 bathroom
- Sleeps 7
Hawaii> Kailua-Kona
*** For more details and more pictures, go to our website at BIGWAVEHAWAII•COM***
The Big Wave Penthouse is arguably in the best location on the Big Island. Consider this:
• Facing west, you're guaranteed a front-row seat every...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Aloha and welcome to our condo at White Sands Village. Brand new to the rental market, this wonderfully appointed 2-bedroom, 2-bathroom top floor condo offers everything you’ll need to enjoy your stay in Kailua-Kona and the Big Island Of ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Was recently completely remodeled.
Our oceanfront condo is 23ft. from the ocean and is located on the 2nd floor and offers spectacular sunsets.
Watch the surfers and the dolphins from your lanai.
A Short walk to town and over 20 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Guests are sure to treasure a memorable vacation experience in our active and friendly neighborhood. We have a 2 story home and offer our comfortably furnished lower level 2 bedrooms/1 bath, with ceiling fans throughout, and free WI-FI. The ...
- 2 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> 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 ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Cozy, private rain forest retreat cottage situated above Kona in the Kaloko Mauka Honua'ula Forest Reserve. Located on a 3-acre property, this cottage is set away in a lush forest of Ohia trees and Hapu'u tree ferns. Enjoy the sweet aroma of ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Peaceful, well appointed room with private entrance ,down a flight of stairs, overlooking our back yard garden of fruit trees and red ginger blooms. Private bath with shower, queen bed, walk in closet, kitchenette, outdoor seating; Wake up and ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
STVR-19-358846 - Relax and pamper yourself in luxury in this well-appointed condo. On the golf course with Spectacular Ocean views. This is NOT your average condo rental! This condo has had an extensive high-end renovation and interior ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona Bay Estates is a gated and patrolled oceanfront community. The Sunset Beach House offers 2700 square feet of luxurious space. It is just 500 meters from the sea. Opening walls of glass offer indoor-outdoor living with spectacular views of ...
- 3 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua-Kona
Kona Shores is a great location right on Alii Drive, a Hawaii Scenic Byway (Ala Loa Foot Trail). A lovely complex with an oceanfront pool & BBQ area for easy island living! Ground floor, end unit with easy access to Alii Drive and just one mile ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Kona Shores is a great location right on Alii Drive (Hawaii Scenic Byway). A lovely complex with oceanfront pool & BBQ area for easy island living! Ground floor with easy access to Alii Drive and just one mile to downtown Kona, shopping, ...
- 0 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Enjoy a perfect vacation in this private Hawaiian home.
Imagine arriving in Hawaii and pulling up to your own private home with ocean views from every bedroom! This spacious home is located in a beautiful upscale neighborhood and has a ...
- 4 bedroom
- 3 bathroom
- Sleeps 8
Hawaii> Kailua Kona
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
This spacious 3 bedroom, 3.5 bath single-level villa is centrally located on the Resort’s popular Waiulu Street overlooking beautiful Punawai Pond, the 5th Hole of the Member’s Ke`olu Golf Course and the ocean beyond. With 2642 square feet ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
One of the largest floor plans of any villa at Hualalai, this spacious and centrally located 3 bedroom, 3 bath villa is ideal for your Hawaii getaway. Extra bedding available with a "bonus" living room on the first level. The down-stairs suite ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
This garden level 2 bedroom, 2 bath Fairway Villa overlooks the 15th Fairway of the Hualalai Golf Course with views of the ocean beyond. Located within the master-planned resort community of Hualalai, guests have the option of amenity use ...
- 2 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Whale watch while listening to the breaking waves from the shoreline from the Master bedroom's private lanai with unobstructed ocean views! This beautifully appointed three bedroom villa has over 2,500 square feet of living area. Each of the ...
- 3 bedroom
- 4 bathroom
- Sleeps 6
Hawaii> Kailua Kona
The spacious floor plan includes a downstairs master bedroom suite with king bed and luxury spa-like bath. Just off the master is a large living room with queen sized sofa pull out that opens to the downstairs lanai, leading to the lawn and ...
- 3 bedroom
- 3 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
This luxury condo is as close to the beautiful blue waters of the Pacific Ocean as you can get at Kanaloa at Kona. Unit 1804 is a top-floor (second floor) oceanfront condo with over 1700 square feet of living space with all the amenities of ...
- 3 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
vrKCV - Hale Akoa, Professionally Managed by Kona Coast Vacations
Seamless indoor/outdoor living and an ever-changing view of the coastline make this 5-bedroom, 4.5-bath single level home at Kahakai Estates a truly special place in ...
- 5 bedroom
- 5 bathroom
- Sleeps 10
Hawaii> Kailua Kona
vrKCV - Mahi Iulani, Professionally Managed by Kona Coast Vacations
On the gentle slopes of Kona, Hawaii, Mahi Iulani is a tropical villa with tranquil breezes where you can enjoy a private retreat and relax in the glow of a tropical ...
- 4 bedroom
- 4 bathroom
- Sleeps 8
Hawaii> Kailua Kona
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
