Sap Business Objects Web Intelligence Rich Client eğitimi için hazırladığım sunum:
Kategori arşivi: Veri Görselleştirme
sap design studio geomap geojson tr Türkiye
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
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
veri görselleştirme en iyi uygulamaları
Information Dashboard Design kitabı ve Effectively Communicating Numbers dokümanından (Stephen Few perceptualedge.com) özetle çıkarımda bulunduğum veri görselleştirme en iyi uygulamaları (data visualization best practices) dokümanı.