POSTGRESQL’DE MEKANSAL SORGULAR

1. POSTGRESQL’DE MEKANSAL SORGULAR KOCAELİ ÜNİVERSİTESİ Bilgisayar Mühendisliği Hazırlayan : Atalay ATAR Danışman : Yrd. Doç. Dr. Ahmet SAYAR

2. MEKANSAL VERİLER - GİRİŞ Hangi mekansal veriler üzerinden sorgular yapacağız? 1-Kendi mekansal verilerilerimizi oluşturalım. Polygon MultiLineString Point 2-Gerçek mekansal verilerileri inceleyelim. Dünya Haritası Türkiye İller Haritası Türkiya Demir Yolları Türkiya Nehirleri ve Gölleri

3. Kendi Verilerimizi Oluşturalım... Mekansal Sorgular için PosrtgreSQL’ de 3 tane table oluşturalım. Bunun için mekansal bir veritabanı oluşturmalıyız. Bölgeler table Polygon veri tipinde Nehirler table MultiLineString veri tipinde Şehirler table Point veri tipinde

4. Kendi Verilerimizi Oluşturalım... 1 - ‘‘poligonbolge’’ adında bir table in alan adları ‘name’ varchar ‘koordinat’ geometry CREATE TABLE poligonbolge (name varchar, koordinat geometry); INSERT INTO poligonbolge VALUES ('bolge1', 'POLYGON((-10 10,0 10,0 0,-10 0,-10 10))'), ('bolge2', 'POLYGON((-10 0,0 0,6 -5,0 -10,-10 -10,-16 -5,-10 0))'), ('bolge3', 'POLYGON((0 10,10 0,0 0,0 10))'), ('bolge4', 'POLYGON((0 0,10 0,10 -10,0 -10,6 -5,0 0))'), ('bolge5', 'POLYGON((0 10,15 10,15 -5,10 -5,10 0,0 10))');

5. Poligonbolge Table Sorguyu yürüttüğümüzde table oluşuyor ve 5 bölge table a ekleniyor.

6. Poligonbolge Table Görünüş

7. Kendi Verilerimizi Oluşturalım... 2- ‘‘multilinenehir’’ adında bir table in alan adları ‘name’ varchar ‘koordinat’ geometry CREATE TABLE multilinenehir (name varchar, koordinat geometry); INSERT INTO multilinenehir VALUES ('nehir1', 'MULTILINESTRING((-3 5,-6 1,-8 -4),(-6 1,0 3,3 4))'), ('nehir2', 'MULTILINESTRING((11 6,8 -1,9 7),(8 -1,0 -4,-1 -6))');

8. Multilinenehir Table

9. Kendi Verilerimizi Oluşturalım... 3- ‘‘noktasehir’’ adında bir table in alan adları ‘name’ varchar ‘isiort’ numeric ‘nufus’ numeric(10) ‘koordinat’ geometry CREATE TABLE noktasehir (name varchar,isiort numeric,nufus numeric(10), koordinat geometry); INSERT INTO noktasehir VALUES ('Sehir1' ,9,650000,'POINT(-9 8)'), ('Sehir2' ,8,530000,'POINT(-6 6)'), ('Sehir3' ,8,700000,'POINT(-3 2)'), ('Sehir4' ,6,450000,'POINT(-4 5)'), ('Sehir5' ,8,1600000,'POINT(-11 -4)'), ('Sehir6' ,9,1400000,'POINT(-9 -9)') ('Sehir7' ,9,2200000,'POINT(-3 -3)'), ('Sehir8' ,10,820000,'POINT(-2 -8)'), ('Sehir9' ,11,930000,'POINT(3 -6)'), ('Sehir10' ,12,640000,'POINT(-5 -5)'), ...........................

10. Noktasehir Table

11. Poligonbolge-Multilinenehir-Noktasehir Son Görünüm

12. Mekansal Sorgulara Başlangıç 1- Bölgelerin Alanlarını Hesaplama Poligonbolge (Polygon) 2- Nehirlerin Uzunluklarını Bulma Multilinenehir(Multistring) 3- Şehir7’in diğer şehirlere uzaklıkları Noktasehir(Point) 4- Bölgelerin Yıllık Sıcaklık Ortalaması ve Nüfus Sayısı Poligonbolge - Noktasehir 5- Nehir1 ve Nehir2 hangi bölgelerden geçiyor Poligonbolge - Multilinenehir

13. Bölgelerin Alanlarını Hesaplama Alan hesabı için ST_AREA fonksiyonunu kullanabilir. Bu fonksiyon geometry veri tipinden bir değişken alır ve geri foat tipinde bir sayı döndürür. float ST_Area(geometry g1); Geometry veri tipi bir polygon veya multipolygon ise alanını hesaplar. Bunlar dışında bir değer ise örneğin point,line gibi hata vermez. Fakat Sıfır döndürür.

14. Bölgelerin Alanlarını Hesaplama Harita Çıktı

15. Nehirlerin Uzunluklarını Bulma Nehir uzunlukları için ST_Lenght fonksiyonu kullanılır. Bu fonksiyon geometry veri tipinden bir değişken alır ve geri foat tipinde bir sayı döndürür. float ST_Lenght(geometry g1); Bu fonksiyon line, linestring, multilinestring vb. veri tipi dışında bir veri alırsa hata vermez; fakat sıfır döndürür.

16. Şehir7’nin Diğer Şehirlere Olan Uzaklığı Burada ST_Distance fonksiyonunu kullanıyoruz. Geomtry veri tipinde değişken alır ve float bir değer döndürür. float ST_Distance(geometry g1, geometry g2); Geometry veri tipi herhangi bir tür olabilir. Point – Point, Polygon – Point, Polygon – Line , Linestring – Multi Polygon vb. Aradaki en kısa mesafeyi hesaplar.

17. Şehir7’nin Bazı Şehirlere Olan Uzaklığı Gösterim

18. Bölgelerin Yıllık Sıcaklık Ortalaması ve Nüfus Sayısı Poligonbolge tablomuzda bölgelerin nüfus bilgisi ve yıllık sıcaklık ortalaması bilgisi yoktur. Bu bilgiler noktasehir tablosunun içindedir. Yapmamız gereken şehirlerin hangi bölgede olduklarını bulup bölgeler için bu bilgileri şehirlerinden almaktır. Hangi şehrin hangi bölgede olduğunu ST_Intersects fonksiyonu ile buluyoruz. booleanST_Intersects( geometry geomA , geometry geomB); Geometry tipinde iki veri alır ve geriye true ya da false sonucu dönderirir. Geometrilerin birbirini kapsama ya da kesişme durumunu inceler.

19. Intersects Özellikleri

20. Bölgelerin Yıllık Sıcaklık Ortalaması ve Nüfus Sayısı Sorgu Analiz Planı

21. Nehir1 ve Nehir2 hangi bölgelerden geçiyor Nehirlerin hangi bolgelerden geçtiğini ST_Crosses fonksiyonunu kullanarak bulalım. booleanST_Crosses(geometryg1, geometry g2);

22. Nehir1 ve Nehir2 hangi bölgelerden geçiyor Sorgu Analiz Planı :

23. Nehir1 ve Nehir2 hangi bölgelerden geçiyor

24. Gerçek Mekansal Veriler Haritaların shape uzantılı dosya biçiminden PostgreSql’e aktarabiliriz. Postgresql’de ‘Plungins/PSQL console’ tıkladığımızda karşımıza çıkan pencereden yükleme yapabiliriz. Dünya haritası (world table) Türkiye iller haritası (adms table) Türkiye nehirleri (waterlines) Bu haritaları yükleyip sorgularımıza başlamadan görünüşlerini alalım.

25. Dünya Haritası (world)

26. Türkiye İller ve Nehirler Haritası (adms - waterlines)

27. Mekansal Sorgulara Başlangıç 1- Ankara’nın komşularını bulmak 2- İlleri Yüz Ölçümlerine Göre Sıralayalım 3- Ülkelerin Türkiye’ye Olan Uzaklıkları 4- Türkiye’nin komşularının sınır uzunlukları 5- Kızılırmak hangi illerden ne uzunluklarda geçer? 6- Koordinat değerlerini bulma

28. Ankara’nın komşularını bulmak Komşulukları bulmak için geometrilerin birbirlerine göre durumlarına bakarız. Bunun için ST_Touches fonksiyonu kullanılır.

29. Ankara’nın komşularını bulmak booleanST_Touches(geometryg1, geometry g2);

30. İlleri Yüz Ölçümlerine Göre Sıralayalım

31. Ülkelerin Türkiye’ye Olan Uzaklıkları

32. Türkiye’nin komşularının sınır uzunlukları Ülkeler polygon tipinde tanımlanmıştır. ST_Length fonksiyonu poligonların uzunluklarını sıfır döndürmektedir. Bu yüzden ST_Lenght ve ST_Boundary fonksiyonlarını kullanacağız. geometry ST_Boundary(geometry geomA); Geri geometry tipinde değişken dönderir. Değerin sınır hatlarını belirtir. LineString MulitiPoint Polygon LineString MultiLineString MultiPoint

33. Türkiye’nin komşularının sınır uzunlukları

34. Kızılırmak hangi illerden ne uzunluklarda geçer? ST_Length ST_Intersects

35. Koordinat değerlerini bulma ST_AsText(geometry) textST_AsText(geometry g1); SELECT ST_AsText('01030000000100000005000000000000000000 000000000000000000000000000000000000000000000000 F03F000000000000F03F000000000000F03F000000000000F03 F000000000000000000000000000000000000000000000000'); st_astext -------------------------------- POLYGON((0 0,0 1,1 1,1 0,0 0)) (1 row)

36. Hangi mekansal fonksiyonları gördük ST_AREA ST_LENGTH ST_DISTANCE ST_INTERSECTS ST_CROSSES ST_TOUCHES ST_BOUNDARY ST_ASTEXT

37. Kullanılan Yardımcı Programlar OpenGeo Suite Quantum Gis uDig

38. Kaynakça http://www.postgis.org/docs/ http://jaspa.forge.osor.eu/spatial_data.html http://www.mapcruzin.com/free-turkey-arcgis-maps-shapefiles.htm http://www.mapcruzin.com/download-free-arcgis-shapefiles.htm http://revenant.ca/www/postgis/workshop/index.html http://workshops.opengeo.org/postgis-intro/index.html http://workshops.opengeo.org/stack-intro/contents.html