Kategori arşivi: Veri Görselleştirme

sap design studio geomap geojson tr Türkiye

Bir Cevap Yazın

Sap Design Studio ortamında GeoMap komponentinin GeoJson ile belirlenmiş alanlarını (Türkiye’nin illerini) istenen iki renk aralığında gösterge (measure, kpi) değerlerine göre renklendirebilmek için notlar.

Öncelikle Türkiye’nin illerinin GeoJson bilgisine design studio geomap komponentinin kabul ettiği formatta ihtiyaç var. Bunun için https://github.com/cihadturhan/tr-geojson adresindeki açık kaynak (open source) proje kullanılabilir. Buradaki Json formatı Design Studio’da kullanılabilecek şekle getirmek için aşağıdaki Python kodu kullanılabilir.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import json

f = open('./tr-cities-utf8.json','r')

parsedJson = json.loads(f.read())

f.close()



parsedFeatures = parsedJson["features"]
parsedFeatures_new = []

for ipf in parsedFeatures:
    ipf_new = {}
    ipf_new["type"] = ipf["type"]
    ipf_new["properties"] = {}
    ipf_new["properties"]["city_name"] = ipf["properties"]["name"]
    ipf_new["properties"]["city_id"] = ipf["id"]
    ipf_new["geometry"] = ipf["geometry"]
    parsedFeatures_new.append(ipf_new)



f = open('./tr-cities-utf8-2.json','w')

f.write('{"type":"FeatureCollection","features":\n')
json.dump(parsedFeatures_new, f)
f.write('}\n')

f.close()

Bunun dışında tercihsel olarak Türkçe karakterlerin gösterimleri değiştirilmek istenirse aşağıdaki dönüşüm kullanılabilir:
\u0131 > ı, \u011f > ğ, \u00f6 > ö, \u00c7 > Ç, \u00fc > ü, \u015f > ş, \u0130 > İ, \u015e > Ş
‘ {“geometry”:’ > ‘\n{“geometry”:’

tr-cities-utf8.json dosyasının yukarıdaki Python kodu ile işlenmeden önceki (tr-cities-utf8.json) ve işlendikten sonraki (tr-cities-utf8-2.json) hallerini aşağıdaki bağlantıdan indirebilirsiniz.
python_geojson

Örnek çıktıda tr-cities-utf8-2.json dosyası GeoMap komponentinde kullanılmış ve değerler (gösterge, measure, kpi) beyazdan siyaha değerin büyüklüğüne göre ifade edilmiştir.

Türkiye gps il koordinatları (enlem ve boylam) oracle sql

Bir Cevap Yazın

Mobil uygulamanın Türkiye illerine göre kullanım istatistiğinin dashboard’unu hazırlarken kullandığımız il gps koordinat bilgileri ve bir koordinatın ile çevrilmesini sağlayan fonksiyon. Oracle veritabanı üzerinde.

Kodu dosya olarak indir: Türkiye Gps İl_Koordinat Sql


create table sample_schema.t_city_gps (
  il_id number, -- plaka kodu,
  il_adi varchar2(255),
  lat number, -- il merkezi enlem,
  lng number, -- il merkezi boylam,
  northeast_lat number, -- kuzeydoğu sınırı enlem
  northeast_lng number, -- kuzeydoğu sınırı boylam
  southwest_lat number, -- güneybatı sınırı enlem
  southwest_lng number, -- güneybatı sınırı boylam
);

insert into sample_schema.t_city_gps values (1,  'ADANA', 37.00000000, 35.32133330, 37.07200400, 35.46199500, 36.93552300, 35.17470600);
insert into sample_schema.t_city_gps values (2,  'ADIYAMAN', 37.76416670, 38.27616670, 37.82566700, 38.33546500, 37.71708600, 38.18818800);
insert into sample_schema.t_city_gps values (3,  'AFYONKARAHİSAR', 38.76376000, 30.54034000, 38.80210500, 30.61116700, 38.71428900, 30.44232000);
insert into sample_schema.t_city_gps values (4,  'AĞRI', 39.72166670, 43.05666670, 39.74860500, 43.08524100, 39.68814400, 43.00177800);
insert into sample_schema.t_city_gps values (5,  'AMASYA', 40.65000000, 35.83333330, 40.67283400, 35.85632100, 40.63691100, 35.78909100);
insert into sample_schema.t_city_gps values (6,  'ANKARA', 39.92077000, 32.85411000, 40.10098100, 33.02486600, 39.72282100, 32.49909700);
insert into sample_schema.t_city_gps values (7,  'ANTALYA', 36.88414000, 30.70563000, 36.97517800, 30.84095300, 36.78586600, 30.51609500);
insert into sample_schema.t_city_gps values (8,  'ARTVİN', 41.18333330, 41.81666670, 41.20707800, 41.85479900, 41.15541500, 41.77736100);
insert into sample_schema.t_city_gps values (9,  'AYDIN', 37.84440000, 27.84580000, 37.87099700, 27.88535500, 37.81957300, 27.79052200);
insert into sample_schema.t_city_gps values (10, 'BALIKESİR', 39.64836900, 27.88261000, 39.69366600, 27.95241000, 39.61241100, 27.83831300);
insert into sample_schema.t_city_gps values (11, 'BİLECİK', 40.15013100, 29.98306100, 40.19761700, 30.01359200, 40.10624300, 29.95665700);
insert into sample_schema.t_city_gps values (12, 'BİNGÖL', 38.88534900, 40.49829100, 38.89796300, 40.52592900, 38.87298700, 40.47725800);
insert into sample_schema.t_city_gps values (13, 'BİTLİS', 38.40000000, 42.11666670, 38.45363800, 42.14887900, 38.38531200, 42.09384000);
insert into sample_schema.t_city_gps values (14, 'BOLU', 40.73947900, 31.61156100, 40.75800400, 31.65841800, 40.71680200, 31.57332400);
insert into sample_schema.t_city_gps values (15, 'BURDUR', 37.72690900, 30.28887600, 37.76375000, 30.33666200, 37.69143300, 30.19175700);
insert into sample_schema.t_city_gps values (16, 'BURSA', 40.18257000, 29.06687000, 40.29719400, 29.21123700, 40.16299700, 28.85963000);
insert into sample_schema.t_city_gps values (17, 'ÇANAKKALE', 40.15531200, 26.41416000, 40.19946200, 26.45710600, 40.08808600, 26.37802600);
insert into sample_schema.t_city_gps values (18, 'ÇANKIRI', 40.60000000, 33.61666670, 40.65515900, 33.68485300, 40.55770900, 33.59657100);
insert into sample_schema.t_city_gps values (19, 'ÇORUM', 40.55055560, 34.95555560, 40.60931700, 35.00643800, 40.49521400, 34.88763400);
insert into sample_schema.t_city_gps values (20, 'DENİZLİ', 37.77652000, 29.08639000, 37.84736600, 29.18891500, 37.71617100, 28.98137300);
insert into sample_schema.t_city_gps values (21, 'DİYARBAKIR', 37.91441000, 40.23062900, 37.97617400, 40.24580300, 37.87962400, 40.10615900);
insert into sample_schema.t_city_gps values (22, 'EDİRNE', 41.66666670, 26.56666670, 41.69793900, 26.60309300, 41.64993500, 26.51405900);
insert into sample_schema.t_city_gps values (23, 'ELAZIĞ', 38.68096900, 39.22639800, 38.71356800, 39.27731400, 38.63056400, 39.13340500);
insert into sample_schema.t_city_gps values (24, 'ERZİNCAN', 39.75000000, 39.50000000, 39.76916400, 39.53174300, 39.72988400, 39.46025300);
insert into sample_schema.t_city_gps values (25, 'ERZURUM', 39.90431890, 41.26788530, 39.95453300, 41.30359400, 39.86671500, 41.17709300);
insert into sample_schema.t_city_gps values (26, 'ESKİŞEHİR', 39.78430200, 30.51922000, 39.81679300, 30.66769000, 39.72475200, 30.41408400);
insert into sample_schema.t_city_gps values (27, 'GAZİANTEP', 37.06622000, 37.38332000, 37.11372200, 37.46695800, 37.00594700, 37.30370900);
insert into sample_schema.t_city_gps values (28, 'GİRESUN', 40.91281100, 38.38953000, 40.92799500, 38.45004400, 40.87787600, 38.30901800);
insert into sample_schema.t_city_gps values (29, 'GÜMÜŞHANE', 40.46027780, 39.48138890, 40.47135900, 39.52064100, 40.43020100, 39.44691400);
insert into sample_schema.t_city_gps values (30, 'HAKKARİ', 37.58333330, 43.73333330, 37.60165600, 43.76225700, 37.55154900, 43.69809000);
insert into sample_schema.t_city_gps values (31, 'HATAY', 36.40184880, 36.34980970, 37.01492100, 36.69753500, 35.81269200, 35.77937500);
insert into sample_schema.t_city_gps values (32, 'ISPARTA', 37.76666670, 30.55000000, 37.83071900, 30.60919700, 37.73988000, 30.50367300);
insert into sample_schema.t_city_gps values (33, 'MERSİN(İÇEL)', 36.80000000, 34.63333330, 36.87827200, 34.71670200, 36.69950300, 34.45765500);
insert into sample_schema.t_city_gps values (34, 'İSTANBUL', 41.00527000, 28.97696000, 41.32078600, 29.45645600, 40.80275000, 27.97130700);
insert into sample_schema.t_city_gps values (35, 'İZMİR', 38.41885000, 27.12872000, 38.50677300, 27.30441500, 38.34315700, 27.01719200);
insert into sample_schema.t_city_gps values (36, 'KARS', 40.59267000, 43.07783100, 40.62484300, 43.13390000, 40.57748400, 43.05643200);
insert into sample_schema.t_city_gps values (37, 'KASTAMONU', 41.38871000, 33.78273000, 41.43605900, 33.81498900, 41.35066800, 33.75290100);
insert into sample_schema.t_city_gps values (38, 'KAYSERİ', 38.73333330, 35.48333330, 38.81879700, 35.62767500, 38.62200200, 35.30432400);
insert into sample_schema.t_city_gps values (39, 'KIRKLARELİ', 41.73333330, 27.21666670, 41.76005400, 27.24222000, 41.71061500, 27.18284300);
insert into sample_schema.t_city_gps values (40, 'KIRŞEHİR', 39.15000000, 34.16666670, 39.20393900, 34.21774300, 39.08129300, 34.12553200);
insert into sample_schema.t_city_gps values (41, 'KOCAELİ', 40.85327040, 29.88152030, 41.20976000, 30.35968400, 40.53278200, 29.33346200);
insert into sample_schema.t_city_gps values (42, 'KONYA', 37.86666670, 32.48333330, 38.05063000, 32.68011600, 37.71903100, 32.33873700);
insert into sample_schema.t_city_gps values (43, 'KÜTAHYA', 39.41666670, 29.98333330, 39.44271800, 30.06650300, 39.37947300, 29.91919900);
insert into sample_schema.t_city_gps values (44, 'MALATYA', 38.35519000, 38.30946000, 38.39755900, 38.45324400, 38.30512300, 38.23262200);
insert into sample_schema.t_city_gps values (45, 'MANİSA', 38.61909900, 27.42892100, 38.64228900, 27.48797600, 38.60237600, 27.32608600);
insert into sample_schema.t_city_gps values (46, 'KAHRAMANMARAŞ', 37.58333330, 36.93333330, 37.60526100, 36.99767800, 37.53030900, 36.82970300);
insert into sample_schema.t_city_gps values (47, 'MARDİN', 37.31223610, 40.73511200, 37.33055400, 40.76448600, 37.30510900, 40.71697100);
insert into sample_schema.t_city_gps values (48, 'MUĞLA', 37.21527780, 28.36361110, 37.22581000, 28.41449800, 37.19465100, 28.34127900);
insert into sample_schema.t_city_gps values (49, 'MUŞ', 38.74329260, 41.50648230, 38.78350300, 41.54062500, 38.71822400, 41.47230600);
insert into sample_schema.t_city_gps values (50, 'NEVŞEHİR', 38.62442000, 34.72396900, 38.68410700, 34.76475900, 38.58904700, 34.66975200);
insert into sample_schema.t_city_gps values (51, 'NİĞDE', 37.96666670, 34.68333330, 37.97991300, 34.69158500, 37.95699200, 34.66190600);
insert into sample_schema.t_city_gps values (52, 'ORDU', 40.98333330, 37.88333330, 41.02034000, 37.97897600, 40.93163100, 37.82599200);
insert into sample_schema.t_city_gps values (53, 'RİZE', 41.02005000, 40.52344900, 41.04649200, 40.58680200, 41.01078200, 40.47857800);
insert into sample_schema.t_city_gps values (54, 'SAKARYA', 40.75687930, 30.37813800, 40.82551800, 30.43983200, 40.68619900, 30.32213800);
insert into sample_schema.t_city_gps values (55, 'SAMSUN', 41.29278200, 36.33128000, 41.34413400, 36.38120500, 41.23500200, 36.24951900);
insert into sample_schema.t_city_gps values (56, 'SİİRT', 37.94429000, 41.93288000, 37.95594000, 41.96365800, 37.90967100, 41.87254700);
insert into sample_schema.t_city_gps values (57, 'SİNOP', 42.02642220, 35.15507450, 42.03213500, 35.19494700, 42.01267500, 35.11595700);
insert into sample_schema.t_city_gps values (58, 'SİVAS', 39.74766200, 37.01787900, 39.78366900, 37.06167300, 39.69902400, 36.91985800);
insert into sample_schema.t_city_gps values (59, 'TEKİRDAĞ', 40.98333330, 27.51666670, 40.99672300, 27.60025200, 40.93770400, 27.46899300);
insert into sample_schema.t_city_gps values (60, 'TOKAT', 40.31666670, 36.55000000, 40.35893200, 36.59321900, 40.26786800, 36.46966200);
insert into sample_schema.t_city_gps values (61, 'TRABZON', 41.00000000, 39.73333330, 41.01237000, 39.82051000, 40.95573600, 39.65240700);
insert into sample_schema.t_city_gps values (62, 'TUNCELİ', 39.10798680, 39.54016720, 39.11347700, 39.55185000, 39.09453400, 39.53001500);
insert into sample_schema.t_city_gps values (63, 'ŞANLIURFA', 37.15000000, 38.80000000, 37.18963900, 38.84087600, 37.09827400, 38.76786800);
insert into sample_schema.t_city_gps values (64, 'UŞAK', 38.68230100, 29.40819000, 38.71588000, 29.46983800, 38.63067900, 29.35593600);
insert into sample_schema.t_city_gps values (65, 'VAN', 38.49416670, 43.38000000, 38.58031400, 43.45535600, 38.42188000, 43.26952400);
insert into sample_schema.t_city_gps values (66, 'YOZGAT', 39.82000000, 34.80444440, 39.83842300, 34.85823600, 39.79896800, 34.77109400);
insert into sample_schema.t_city_gps values (67, 'ZONGULDAK', 41.45640900, 31.79873100, 41.48078400, 31.87442500, 41.42529600, 31.75705200);
insert into sample_schema.t_city_gps values (68, 'AKSARAY', 38.36869000, 34.03698000, 38.44672400, 34.08869800, 38.29701200, 33.93251500);
insert into sample_schema.t_city_gps values (69, 'BAYBURT', 40.25516900, 40.22488000, 40.27263600, 40.23826100, 40.24288100, 40.20876200);
insert into sample_schema.t_city_gps values (70, 'KARAMAN', 37.17593000, 33.22874800, 37.22937300, 33.33424400, 37.14825700, 33.16600300);
insert into sample_schema.t_city_gps values (71, 'KIRIKKALE', 39.84682100, 33.51525100, 39.87284300, 33.59796800, 39.81380200, 33.46828900);
insert into sample_schema.t_city_gps values (72, 'BATMAN', 37.88116800, 41.13509000, 37.94727400, 41.17603000, 37.85657000, 41.08059100);
insert into sample_schema.t_city_gps values (73, 'ŞIRNAK', 37.51638890, 42.46111110, 37.53357200, 42.47112700, 37.50009300, 42.44463100);
insert into sample_schema.t_city_gps values (74, 'BARTIN', 41.63444440, 32.33750000, 41.65654200, 32.37105000, 41.61351300, 32.29745700);
insert into sample_schema.t_city_gps values (75, 'ARDAHAN', 41.11048100, 42.70217100, 41.12146700, 42.71712900, 41.09891000, 42.68641800);
insert into sample_schema.t_city_gps values (76, 'IĞDIR', 39.91666670, 44.03333330, 39.95016300, 44.09282500, 39.88410900, 43.98072900);
insert into sample_schema.t_city_gps values (77, 'YALOVA', 40.65000000, 29.26666670, 40.66806000, 29.31653600, 40.63413800, 29.21023300);
insert into sample_schema.t_city_gps values (78, 'KARABÜK', 41.20000000, 32.63333330, 41.23812900, 32.67551800, 41.16301400, 32.59258200);
insert into sample_schema.t_city_gps values (79, 'KİLİS', 36.71839900, 37.12122000, 36.74781900, 37.15954100, 36.70267300, 37.08518300);
insert into sample_schema.t_city_gps values (80, 'OSMANİYE', 37.06805000, 36.26158900, 37.10406100, 36.28794100, 37.04694000, 36.20803400);
insert into sample_schema.t_city_gps values (81, 'DÜZCE', 40.84384900, 31.15654000, 40.89176200, 31.19580700, 40.82471700, 31.10666200);

commit;

-- illerin dikdörtgen olarak düşünüldüğündeki fonksiyon

create or replace function sample_schema.f_gps_to_city(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where p_lat between southwest_lat and northeast_lat
and p_lng between southwest_lng and northeast_lng;

return v_il_id;

exception when others then return 0;

end;
/

-- koordinatın il merkezlerine olan yakınlığına göre ile çevrilmesi

create or replace function sample_schema.f_gps_to_city_2(p_lat in number , p_lng in number )
return number
is
v_il_id number;
begin

select nvl(max(il_id),0) into v_il_id
from sample_schema.t_city_gps
where sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) ) = (
  select min(  sqrt( abs(p_lat-lat)*abs(p_lat-lat) + abs(p_lng-lng)*abs(p_lng-lng) )  )
  from sample_schema.t_city_gps
);

return v_il_id;

exception when others then return 0;

end;
/

Kaynak: Türkiye’nin il ilçe koordinatları (enlem ve boylamları) MySql sql