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 (`headline` like '%Casa De Emdeko%') AND sleeps>=1 order by rating desc LIMIT 0, 30
Summary:
Newly renovated comfortable condo on ground level with garden view. Access to two pools, man made beach and bbq dining area fronting the ocean and sunsets. 920 sq ft of space, this unit has a separate entry to bathroom for guest. ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Casa De Emdeko is one of the most popular waterfront Condo complexes in Kona; visit just once and, like most of our guests, you'll keep coming back. There is just something magical about Casa de Emdeko - there is a welcoming social ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Aloha and welcome to the most special Hawaii Vacation Condo in Kona on the Big Island, Casa De Emdeko 129.
This Oceanfront Vacation Destination is located on famous Alii Drive just two miles from the village shopping and Kailua Pier. The ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Summary:
Newly renovated comfortable condo on ground level with garden view. Access to two pools, man made beach and bbq dining area fronting the ocean and sunsets. This unit has a separate entry to bathroom for guest. Large walk in shower,...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Convenient garden level 1BD/1BA unit sleeps 2-4. This condo has been completely remodeled in 2011 with tile and granite. Clean, comfortable, quiet and located just steps from both the saltwater and freshwater pools, beach-front barbeque and ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Your dream tropical vacation begins at Casa De Emdeko 336, a totally remodeled, fully-furnished oceanfront condominium. Within walking distance of Kailua-Kona Village, this ideal unit features central air conditioning, two bedrooms, two ...
- 2 bedroom
- 1 bathroom
- Sleeps 6
Hawaii> Kailua Kona
This immaculately remodeled Deluxe 1 bedroom at the Casa De Emdeko is truly a Breathtaking Property.
Located in the heart of Kailua-Kona, The Casa De Emdeko complex is a spectacular development that offers owners and guests the perfect blend ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
CASA DE EMDEKO, UNIT#230: DELUXE, REMODELED, 1/1!
Deluxe unit at Casa De Emdeko: VERY spacious, complete renovated unit, at this Ocean Front complex. Tastefully appointed 920 sq ft . apartment. Ocean views from spacious covered Lanai, view ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Our ocean view condominium is kept updated and comfortably furnished to offer you a relaxing, quiet, and enjoyable vacation. You'll love the bamboo floors, which lends to the clean, open feeling of our wonderful condo. This top floor end unit ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Casa De Emdeko 235 is a one-bedroom, one-bathroom non-smoking condominium within walking distance of downtown Kailua-Kona. This non-smoking oceanfront property features complimentary Wifi and is conveniently situated on Ali'i Drive, with miles ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa#324 is a Beautifully Remodeled Top Floor 1 bedroom, 1 bath condominium at the Oceanfront Casa De Emdeko Condominium Development.
This special property is directly across from the elevator on the 3rd floor for ease of access.
Located ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa#A105 is an excellent, affordable Ground floor property that you will certainly love. Unit#A105 has great access in the complex, only 4 doors in from the general parking area. Close to the Pool too! This unit is located on the "shadier" side...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Newly renovated condo close to downtown Kailua-Kona. Two pools, one right on the ocean (saltwater pool) and one freshwater pool. BBQ area overlooking the ocean. Beach area where you can sit right next to the ocean and enjoy beautiful Kona ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona,
Casa#323 is a Beautifully Remodeled Top Floor 1 bedroom, 1 bath condominium at the Oceanfront Casa De Emdeko Condominium Development.
This special property is close to the elevator on the 3rd floor for ease of access.
Located in the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
vrKCV - Casa de Emdeko 306, Professionally Managed by Kona Coast Vacations
Welcome to your tropical paradise at Casa de Emdeko!
This completely remodeled one-bedroom, one-bath vacation condo is located on the third (top) floor, ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua Kona
Step into your slice of paradise at Casa de Emdeko #134, a sanctuary where the ocean’s melody serenades you into serenity, positioned mere steps from a secluded beach and two inviting pools. This oceanfront complex, nestled within ...
- 1 bedroom
- 1 bathroom
- Sleeps 2
Hawaii> Kailua-Kona
Absolute oceanfront, Casa de Emdeko # 138 2 bedroom/2 bath condo. Enjoy year-round sunsets from the lanai. Dolphins play in the surf and whales are in view when in season. Inside you will find a remodeled granite kitchen with easy pass-thru to ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua Kona
Beautiful Ocean View from this wonderful Top Floor Unit at Casa De Emdeko. Unit#332 is a great property!! You can't beat this view at this price!!
Casa De Emdeko offers owners and guests the perfect blend of Hawaiian ambiance with tropical ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa 204 is an excellent, affordable 2nd floor property that you will certainly love. Unit#204 was REMODELED IN APRIL 2019 with all new flooring, furniture, kitchen, bathrooms, paint, appliances!
Central AC, WIFI, Elevators, Beach gear! Look...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
The Owners of Casa#237 have completed an incredible remodel. Top of the Line Kitchen & Bathroom with an amazing walk in shower. They have also ADDED A 2ND HALF BATHROOM! Wow! Very rare and special amenity, nearly unheard of at the Casa De ...
- 1 bedroom
- 2 bathroom
- Sleeps 4
Hawaii> Kailua Kona
At Casa De Emdeko you can have it all!
Enjoy your morning coffee from the lanai while enjoying the partial ocean views! You'll also have a direct lush garden view to the soothing fountain. Relax and bask in the breathtaking ocean views ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua-Kona
Casa De Emdeko B123 at a Galance:
* Close to beach, restaurants, grocery, gas stations and town.
*Central Air.
* Gated community with free parking.
* Free wifi.
* Coin-operated laundry mat steps from the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Need a perfect home base for your Big Island Vacation?
Perfectly located just two miles south of the Kailua-Kona pier, Casa de Emdeko is situated in close proximity to Ali'i Drive’s downtown area and nightlife - yet far enough away that...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Rarely available, 2 bedroom, ground floor, ocean view unit at Casa De Emdeko- very spacious 2 bedroom unit! Unit#A117 is 2 units away from the very end of the development, so extremely close to the Salt Water Pool, Sandy Beach area on site & BBQ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> KAILUA KONA
Owners have extensively updated the condo from top to bottom.
Flooring, Bathroom, Kitchen, Furniture, Lanai all updated!
Casa De Emdeko offers owners and guests the perfect blend of Hawaiian ambiance with tropical gardens and the ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa De Emdeko 222 by Gather is a deluxe, one-bedroom oceanfront condominium with miles of coastline views. A perfect blend of efficiency and comfort, this unit features complimentary Wifi, as well as central air conditioning and ceiling fans ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Settle into your serene Hawaiian vacation in your home-away-from-home here at Casa De Emdeko by Gather, where convenience meets comfort in this peaceful paradise. This newly remodeled 1 Bedroom, 1 Bathroom ground floor condominium sleeps up to 4...
- 1 bedroom
- 1 bathroom
- Sleeps 4
Hawaii> Kailua Kona
Casa#219 is a DIRECT oceanfront 2 bedroom 2 bath property that sits right on the water. HUGE lanai!
This unit has everything you will need to feel like you are royalty: a great big king bed and two twin sized beds. It also has a fully ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
Hawaii> Kailua-Kona
Maximum occupancy strictly enforced in accordance with local ordinances.
A small family will love this recently upgraded Kailua-Kona condominium. This condo is a unit within Casa De Emdeko. The complex resides in a gorgeous location ...
- 2 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
Casa De Emdeko is a great place to stay on the Big Island of Hawaii. This 1 bedroom/1 bathroom home is perfect for a couple or small family as it can accommodate up to 3 guests. Kona vacationers will love this quiet unit located right outside of...
- 1 bedroom
- 1 bathroom
- Sleeps 3
Hawaii> Kailua Kona
