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, 20
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
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
