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 (`headline` like '%Casa De Emdeko%') AND sleeps>=1 order by rating desc LIMIT 0, 15
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 from ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Newly renovated condo close to downtown Kailua-Kona. Condo with two pools. One right on the ocean (salt water pool) and one freshwater pool. BBQ area overlooking the ocean. Beach area where you can sit right next to the ocean and enjoy ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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
HI> 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. 920 sq ft of space, this unit has a separate entry to bathroom for guest. Wheel ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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 Condo ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Enjoy this beautifully renovated 1 bedroom condo in the convenient Casa De Emdeko community. About 1.5 miles from Kona town, and also near La Aloha "Magic Sands" Beach Park, Honl's Beach at the edge of the village, and others. Lovely tropical...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua Kona
This Classic Hawaiian condominium Complex has style and charm. Located on the Oceanside, it offers beautiful views, barbecue area, a lovely sandy beach with lounge chairs. At night they light Tiki Torches and blow a conch shell as the sun goes ...
- 1 bedroom
- 1 bathroom
- Sleeps 3
HI> 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
HI> 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
HI> Kailua Kona
Casa#317 is an amazing addition to our Vacation Rental Inventory that Knutson & Associates is proud to be newly representing. **HARD WOOD FLOORS AND KITCHEN REMODEL COMPLETED IN MAY 2018! Living room & Bedrooms have gorgeous brand new hard wood ...
- 2 bedroom
- 2 bathroom
- Sleeps 6
HI> 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
HI> 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
HI> Kailua-Kona
Casa 204 is an excellent, affordable 2nd floor property that you will certainly love. Unit#204 has been NEWLY REMODELED IN APRIL 2019! All new flooring, furniture, kitchen, bathrooms, paint, appliances!
Central AC, WIFI, Elevators, Beach gear! ...
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> Kailua-Kona
Casa De Emdeko 222 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 airconditioning and ceiling fans throughout....
- 1 bedroom
- 1 bathroom
- Sleeps 4
HI> 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
HI> Kailua Kona