PostGIS ile Coğrafi Veri Analizleri

PostGIS, PostgreSQL veritabanı için bir mekansal veri uzantısıdır ve coğrafi veri işleme yetenekleri sunar. Bu yazıda, adityatoshniwal/postgis-sample-dataset GitHub deposundaki örnek veri setiyle PostGIS sorguları oluşturacağız.

Veri Seti Hakkında

Veri setimiz iki ana tablodan oluşmaktadır:

countries: Ülke verileri (id, isim ve geometri bilgileri)
capitals: Başkent verileri (id, isim, ülke id ve geometri bilgileri)

Örnek PostGIS Sorguları

1. Basit Veri Sorgulama

-- Tüm ülkeleri listeleyelim
SELECT id, name FROM countries ORDER BY name LIMIT 10;

Sonuç:

gis_db=# SELECT id, name FROM countries ORDER BY name LIMIT 10;
 id  |        name         
-----+---------------------
  31 | Afghanistan
 142 | Åland Islands
   4 | Albania
   2 | Algeria
   7 | American Samoa
 133 | Andorra
   6 | Angola
 128 | Anguilla
 145 | Antarctica
   1 | Antigua and Barbuda
(10 rows)

-- Tüm başkentleri listeleyelim
SELECT id, name FROM capitals ORDER BY name LIMIT 10;

Sonuç:

gis_db=# SELECT id, name FROM capitals ORDER BY name LIMIT 10;
 id  |       name       
-----+------------------
 239 | Abu Dhabi
 162 | Abuja
  85 | Accra
 178 | Adamstown
  72 | Addis Ababa
   5 | Algiers
 163 | Alofi
 113 | Amman
 156 | Amsterdam
   7 | Andorra la Vella
(10 rows)

2. Mekansal Veri Görüntüleme

-- Türkiye'nin geometrisini GeoJSON formatında görüntüleyelim
SELECT name, ST_AsGeoJSON(geom) 
FROM countries 
WHERE name = 'Turkey';

Sonuç:

Turkey | {"type":"MultiPolygon","coordinates":[[[[26.078053,39.7836],[25.974995,39.829987],[26.075829,39.839722],[26.078053,39.7836]]],[[[25.
824444,40.100266],[25.663883,40.126389],[26.013611,40.157494],[25.824444,40.100266]]],[[[27.60722,40.57222],[27.531109,40.648331],[27.735271,4
0.634438],[27.60722,40.57222]]],[[[35.096931,41.961655],[35.506386,41.638054],[36.051102,41.691933],[36.429153,41.242775],[38.361382,40.909431
],[40.149994,40.920273],[41.531559,41.523876],[42.827492,41.584991],[43.46077,41.112961],[43.751938,40.739998],[43.657494,40.108597],[44.34721
4,40.023888],[44.778862,39.706383],[44.813042,39.630814],[44.60582,39.78054],[44.4161,39.425262],[44.034157,39.384995],[44.484154,38.345543],[
44.223969,37.899151],[44.61805,37.727768],[44.787338,37.149712],[44.317215,36.970543],[44.116379,37.316376],[42.790825,37.38472],[42.355614,37
.106926],[40.770821,37.11805],[39.229996,36.665276],[36.659943,36.83371],[36.690269,36.236107],[35.92244,35.926994],[35.783875,36.312485],[36.
217766,36.654999],[36.021935,36.926384],[35.339989,36.539162],[34.659431,36.805275],[33.988602,36.277771],[32.808884,36.025551],[31.046661,36.
849152],[30.623333,36.850822],[30.40694,36.203606],[29.677216,36.118332],[28.454163,36.881386],[27.983887,36.552773],[28.118332,36.800278],[27
.37944,36.680832],[28.328606,37.039719],[27.252499,36.967499],[27.595276,37.232491],[27.193886,37.350822],[27.267773,37.955544],[26.275829,38.
264435],[26.369999,38.661942],[26.682217,38.307487],[27.161942,38.443886],[26.730827,38.645821],[27.064442,38.874435],[26.644722,39.263054],[2
6.951664,39.552773],[26.067219,39.483047],[26.707222,40.384995],[28.985271,40.356934],[28.795277,40.551384],[29.938049,40.723885],[29.12944,40
.914444],[29.166111,41.226662],[31.23111,41.088875],[33.333878,42.020264],[35.096931,41.961655]]],[[[27.394997,42.008041],[28.013054,41.982216
],[28.090549,41.631386],[29.039162,41.057213],[27.50972,40.983597],[26.181107,40.045273],[26.826939,40.594437],[26.04472,40.735825],[26.633884
,41.354439],[26.361095,41.711052],[27.394997,42.008041]]]]}
(1 row)

3. Mesafe Hesaplama

-- Ankara ve İstanbul arasındaki mesafeyi kilometre cinsinden hesaplayalım
-- (Not: İstanbul'un koordinatlarını manuel olarak ekledim)
SELECT ST_Distance(
  (SELECT geom FROM capitals WHERE name = 'Ankara'),
  ST_SetSRID(ST_MakePoint(28.9784, 41.0082), 4326)
) * 111.32 AS mesafe_km;

Sonuç:

gis_db=# SELECT ST_Distance(
  (SELECT geom FROM capitals WHERE name = 'Ankara'),
  ST_SetSRID(ST_MakePoint(28.9784, 41.0082), 4326)
) * 111.32 AS mesafe_km;
     mesafe_km      
--------------------
 448.33206674164313
(1 row)

4. Ülke Alanı Hesaplama

-- Ülkelerin alanlarını kilometre kare cinsinden hesaplama
SELECT name, 
       ST_Area(geom::geography) / 1000000 AS alan_km2
FROM countries
ORDER BY alan_km2 DESC
LIMIT 10;

Sonuç:

gis_db=# SELECT name, 
       ST_Area(geom::geography) / 1000000 AS alan_km2
FROM countries
ORDER BY alan_km2 DESC
LIMIT 10;
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
     name      |      alan_km2      
---------------+--------------------
 Russia        | 16899340.799018353
 Antarctica    | 12307608.053238433
 Canada        |   9690145.85838465
 United States |   9552731.30433088
 China         |  9360697.736226594
 Brazil        |   8449313.02169013
 Australia     | 7691060.9544034405
 India         | 3139787.7378728543
 Argentina     | 2779978.6214484978
 Kazakhstan    | 2729850.9292199323
(10 rows)

5. En Yakın Komşu Başkentleri Bulma

-- Ankara'ya en yakın 5 başkenti bulmak
SELECT c.name AS baskent, co.name AS ulke, 
       ST_Distance(c.geom::geography, 
                 (SELECT geom::geography FROM capitals WHERE name = 'Ankara')) / 1000 AS mesafe_km
FROM capitals c
JOIN countries co ON c.country_id = co.id
WHERE c.name != 'Ankara'
ORDER BY mesafe_km
LIMIT 5;

Sonuç:

gis_db=# SELECT c.name AS baskent, co.name AS ulke, 
       ST_Distance(c.geom::geography, 
                 (SELECT geom::geography FROM capitals WHERE name = 'Ankara')) / 1000 AS mesafe_km
FROM capitals c
JOIN countries co ON c.country_id = co.id
WHERE c.name != 'Ankara'
ORDER BY mesafe_km
LIMIT 5;
  baskent  |         ulke         |     mesafe_km     
-----------+----------------------+-------------------
 Nicosia   | Cyprus               |   528.96560641853
 Nicosia   | Cyprus               |   528.96560641853
 Bucharest | Romania              | 748.3642613688199
 Sukhumi   | Georgia              |   761.89993632882
 Damascus  | Syrian Arab Republic |   774.85741415781
(5 rows)

6. Bir Ülke ile Komşu Olan Ülkeleri Bulma

-- Türkiye ile sınırı olan tüm ülkeleri bulalım
SELECT b.name AS komsu_ulke
FROM countries a, countries b
WHERE a.name = 'Turkey' 
  AND a.id != b.id
  AND ST_Touches(a.geom, b.geom);

Sonuç:

gis_db=# SELECT b.name AS komsu_ulke
FROM countries a, countries b
WHERE a.name = 'Turkey' 
  AND a.id != b.id
  AND ST_Touches(a.geom, b.geom);
         komsu_ulke         
----------------------------
 Bulgaria
 Georgia
 Greece
 Iran (Islamic Republic of)
 Iraq
 Syrian Arab Republic
(6 rows)

7. Belirli Bir Noktaya Göre Ülkeleri Sıralama

-- İstanbul'a (28.9784, 41.0082) en yakın 10 ülkeyi listeleyelim
SELECT name,
       ST_Distance(geom::geography, 
                 ST_SetSRID(ST_MakePoint(28.9784, 41.0082), 4326)::geography) / 1000 AS mesafe_km
FROM countries
ORDER BY mesafe_km
LIMIT 10;

Sonuç:

gis_db=# SELECT name,
       ST_Distance(geom::geography, 
                 ST_SetSRID(ST_MakePoint(28.9784, 41.0082), 4326)::geography) / 1000 AS mesafe_km
FROM countries
ORDER BY mesafe_km
LIMIT 10;
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
NOTICE:  Coordinate values were coerced into range [-180 -90, 180 90] for GEOGRAPHY
                   name                    |     mesafe_km      
-------------------------------------------+--------------------
 Turkey                                    | 5.1535445524200005
 Bulgaria                                  |    134.90573579813
 Greece                                    |      200.430889211
 Romania                                   |    306.04678028749
 Ukraine                                   | 470.31910786490005
 Republic of Moldova                       | 497.19879177224004
 The former Yugoslav Republic of Macedonia |    505.69412096612
 Serbia                                    |    550.30396872865
 Albania                                   |    673.32075329495
 Cyprus                                    |    722.34805997938
(10 rows)

8. İki Ülke Arasındaki En Kısa Mesafeyi Bulma

-- Türkiye ve Mısır arasındaki en kısa mesafeyi bulalım
SELECT ST_Distance(
  (SELECT geom::geography FROM countries WHERE name = 'Turkey'),
  (SELECT geom::geography FROM countries WHERE name = 'Egypt')
) / 1000 AS mesafe_km;

Sonuç:

gis_db=# SELECT ST_Distance(
  (SELECT geom::geography FROM countries WHERE name = 'Turkey'),
  (SELECT geom::geography FROM countries WHERE name = 'Egypt')
) / 1000 AS mesafe_km;
    mesafe_km    
-----------------
 505.37371700004
(1 row)

9. Bir Ülkenin Çevre Uzunluğunu Hesaplama

-- Türkiye'nin çevre uzunluğunu kilometre cinsinden hesaplayalım
SELECT name, 
       ST_Perimeter(geom::geography) / 1000 AS cevre_km
FROM countries
WHERE name = 'Turkey';

Sonuç:

gis_db=# SELECT name, 
       ST_Perimeter(geom::geography) / 1000 AS cevre_km
FROM countries
WHERE name = 'Turkey';
  name  |     cevre_km      
--------+-------------------
 Turkey | 6309.942461517642
(1 row)

10. Bir Noktanın Hangi Ülke İçinde Olduğunu Bulma

-- Belirli bir koordinatın (35.243322, 38.963745) hangi ülkede olduğunu bulalım
SELECT name
FROM countries
WHERE ST_Contains(geom, ST_SetSRID(ST_MakePoint(35.243322, 38.963745), 4326));

Sonuç:

gis_db=# SELECT name
FROM countries
WHERE ST_Contains(geom, ST_SetSRID(ST_MakePoint(35.243322, 38.963745), 4326));
  name  
--------
 Turkey
(1 row)

Sonuç

PostGIS, coğrafi verilerin analizi için güçlü fonksiyonlar sunar. Bu örneklerde mesafe hesaplama, alan bulma, çevre hesaplama ve mekansal ilişkileri sorgulama gibi temel özellikleri gösterdik. Bu sorgular, harita uygulamaları, konum tabanlı hizmetler ve coğrafi analiz gerektiren diğer projelerde kullanılabilir.

Bu örnek veri seti ile daha karmaşık analizler de yapılabilir, örneğin tampon bölge oluşturma, kesişim analizleri veya jeopolitik çalışmalar için çeşitli mekansal sorgular geliştirmek mümkündür.

PostGIS ile Coğrafi Veri Analizleri