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

Bir cevap yazın