DuckDB ile Mekansal Veri Analizi

Giriş

Bu yazıda Overture Maps Foundation tarafından açık veri olarak yayınlanan point of interest(POI) verisine DuckDB ile nasıl erişileceğini ve mekansal analizi nasıl yapılacağını anlatacağım.

DuckDB nedir?

DuckDB, Çevrimiçi analitik işleme (OLAP) olarak da bilinen analitik sorgu iş yüklerini desteklemek için tasarlanmıştır. Sütunlu vektörleştirilmiş(columnar-vectorized) bir sorgu yürütme motoru içerir. Bu, her satırı sırayla işleyen PostgreSQL, MySQL veya SQLite gibi geleneksel sistemlere göre daha performanslıdır. Birçok eklenti bulunmaktadır. Amazon S3, Google Cloud Storage, postgresql gibi ortamlarda bulunan verilerinizi eklentileri kullanarak kolayca aktarabilirsiniz. Spatial eklentisini yükleyerek mekansal analizler yapabilirsiniz.

Ortam Kurulumu

pip install duckdb==0.8.1

Veritabanı oluşturma

import duckdb
db = duckdb.connect("data.db")

Verilere erişim ve mekansal analiz için eklentilerin kurulumu

Mekansal analiz yapmak için “spatial” eklentisini yüklüyoruz.
Amazon S3'te bulunan POI verilerine erişim için “httpfs” eklentisini kuruyoruz. Ardından bölgeyi “us-west-2” olarak belirliyoruz.

db.sql("""
INSTALL spatial;
INSTALL httpfs;
LOAD spatial;
LOAD httpfs;
SET s3_region='us-west-2';
""")

Veritabanında bir tablo oluşturup parquet formatındaki verileri aktarıyoruz. 59 milyon satırlık veri yaklaşık 34 dakika içinde veritabanına aktarıldı.

db.sql("""
  create table places as 
  select * from read_parquet('s3://overturemaps-us-west-2/release/2023-07-26-alpha.0/theme=places/type=*/*')
""")

db.sql("""
select count(*) as count from places
""").show()

Millisaniyeler içerisinde tablodaki veri sayısını döndürdü.

db.sql("""
    select * from places limit 5
""").show()

İlk beş satır:

POI verisinin şemasına buradan ulaşabilirsiniz. Veride ön işlem yapmamız gereken sütunlar var.

db.sql("""
    select names, categories, confidence,brand,addresses from places limit 5
""").show()

Örneğin categories sütununda hangi kategoride olduğunu çıkarmak için “struct” tipinde tutulan veriden bilgiyi almamız gerekiyor. DuckDB veri tiplerini öğrenmek için dökümanını inceleyebilirsiniz.

Örnek olarak “Adresses” sütununda hangi ülkede bulunduğunu çıkarmak için:

db.sql("""
    select replace(json_extract(CAST(addresses AS JSON), '$[0].country')::varchar,'"','') as country from places limit 5
""").show()

Ülke kısa adlarını çıkarmak için “country” diye bir sütun oluşturduktan sonra ayıkladığımız veriyi ekliyoruz.

db.sql("""ALTER TABLE places ADD COLUMN country VARCHAR;
       update places set country = replace(json_extract(CAST(places.addresses AS JSON), '$[0].country')::varchar,'"','')
    
""")

Türkiye’deki POI verilerini ayrı bir tabloya eklemek ve adres, kategori, isim, geometri bilgilerini elde etmek için aşağıdaki sorguyu çalıştırıyoruz.

db.sql("""
       create or replace table turkey_places as (
              select
                     replace(json_extract(places.addresses::json,'$[0].locality'),'"','')::varchar as locality,
                     replace(json_extract(places.addresses::json,'$[0].region'),'"','')::varchar as region,
                     replace(json_extract(places.addresses::json,'$[0].postcode'),'"','')::varchar as postcode,
                     replace(json_extract(places.addresses::json,'$[0].freeform'),'"','')::varchar as freeform,

                     categories.main as categories_main,

                     replace(json_extract(places.names::json,'$.common[0].value'),'"','')::varchar as names,
                     confidence,
                     bbox,
                     st_transform(st_point(st_y(st_geomfromwkb(geometry)),st_x(st_geomfromwkb(geometry))),'EPSG:4326','EPSG:3857') as geom
                     
       
              from places 
                     where country ='TR' 
       )
       
    
""")

Oluşturulan tablo:

db.sql("""
select * from turkey_places limit 5
""").df()

Örnek olarak İstanbul’daki POI verilerini inceleyeceğim. Park olarak belirlenen noktaların 500 m etrafında bulunan POI noktalarını elde etmek için iki tablo oluşturdum.

db.sql("""
    create or replace table park_ist as (
        select * from turkey_places where locality = 'İstanbul' and categories_main='park'   
    );

    create or replace table poi_ist as (
        select * from turkey_places where locality = 'İstanbul' and categories_main <> 'park'
    )
       
""")

İstanbul’daki POI sayısı:

db.sql(
    """
select count(*) from poi_ist

"""
)
'''
count
  181959
'''

İstanbul’daki Park olarak belirlenen POI sayısı:

db.sql(
    """
select count(*) from park_ist

""")

'''
count
  492
'''

Park kategorisine alınan noktaların 500 m etrafında kalan POI noktalarını sorgulamak için:

df = db.sql("""
              select  poi_ist.region as poi_ist_region,poi_ist.freeform as poi_ist_freeform,poi_ist.categories_main as poi_ist_categori ,
              park_ist.categories_main as park_categori , park_ist.names as park_names, park_ist.freeform as park_ist_freeform,


              st_distance(poi_ist.geom,park_ist.geom) as dist,
              ST_AsText(poi_ist.geom) as geom,
              ST_AsText(park_ist.geom) as geom2

              from poi_ist, park_ist 

              where ST_DWithin(poi_ist.geom, park_ist.geom,500) 
              

              
              
       
       """).to_df()

gdf = gpd.GeoDataFrame(df,geometry= gpd.GeoSeries.from_wkt(df['geom']),crs="EPSG:3857")
gdf.to_file("export/poi.geojson",driver="GeoJSON")

Oluşan tabloyu pandas dataframe formatına çevirdim. Daha sonra geopandas kullanarak tabloyu geojson formatı olarak kaydettim. QGIS ile kategori bilgisine göre görselleştirdim.

POI verisi daha detaylı incelenebilir. DuckDB ile mekansal verilerle nasıl çalışılacağını anlatmak istedim. Umarım faydalı olmuştur. Bir sonraki yazıda görüşmek dileğiyle.

Kaynak:

Overture Maps Foundation
POI Data
DuckDB
DuckDB Spatial extension
DuckDB HTTPFS extension