giriiş

Geospatial Indexing, konum tabanlı verileri yönetmek için zarif bir yol sağlayan bir dizin oluşturma tekniğidir. Sistemin kullanıcılarına en iyi deneyimi sunabilmesi için jeo-uzamsal verilerin verimli bir şekilde aranmasını ve alınmasını sağlar. Bu makale, gerçek dünya verilerine jeo-uzamsal bir dizin uygulayarak ve bunu yaparak performans kazancını göstererek bunun pratikte nasıl çalıştığını gösterecek. Başlayalım. (Not: Coğrafi indeksi hiç duymadıysanız veya hakkında daha fazla bilgi edinmek istiyorsanız, bu makaleye göz atın )

Veri

Bu makalede kullanılan veriler, Google Cloud Public Dataset Programının bir parçası olan Chicago Crime Data'dır . Google Cloud Platform hesabı olan herkes bu veri kümesine ücretsiz olarak erişebilir. 2001'den beri Chicago'da meydana gelen suç olaylarını kaydeden yaklaşık 8 milyon satırlık veriden (toplam 1,52 GB) oluşur ve her bir kayıt, olayın yerini gösteren coğrafi verilere sahiptir.

platformu

Google Cloud'dan alınan verileri kullanmanın yanı sıra Google Big Query'yi veri işleme platformu olarak kullanacağız. Big Query, yürütülen her sorgu için iş yürütme ayrıntılarını sağlar. Bu, kullanılan veri miktarını ve işlenen satır sayısını içerir; bu, optimizasyondan sonra performans kazancını göstermek için çok yararlı olacaktır.

uygulama

Jeo-uzamsal dizinin gücünü göstermek için yapacağımız şey, konum tabanlı sorgunun performansını optimize etmektir. Bu örnekte basitliği ve Google BigQuery'nin yerel desteği nedeniyle Geohash'ı bir dizin olarak kullanacağız . Chicago Union İstasyonu'nun
2 km yakınında meydana gelen tüm suç kayıtlarını alacağız . Optimizasyondan önce, bu sorguyu orijinal veri kümesinde çalıştırdığımızda performansın nasıl göründüğüne bakalım:

-- Chicago Union Station Coordinates = (-87.6402895591744 41.87887332682509)
SELECT 
  * 
FROM 
  `bigquery-public-data.chicago_crime.crime`
WHERE 
  ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGFROMTEXT("POINT(-87.6402895591744 41.87887332682509)")) <= 2000

İşlenen Bayt ve Okunan Kayıt sayısından , sorgunun nihai sonucu almak için tüm tabloyu taradığını ve her satırı işlediğini görebilirsiniz. Bu, ne kadar çok veriye sahip olursak, sorgunun o kadar uzun süreceği ve işlem maliyetinin o kadar pahalı olacağı anlamına gelir. Bu daha verimli olabilir mi? Tabii ki, coğrafi indeksin devreye girdiği yer burasıdır.

Yukarıdaki sorguyla ilgili sorun, birçok kaydın ilgi noktasından (Chicago Union Station) uzakta olmasına rağmen, yine de işlenmesi gerekmesidir. Bu kayıtları ortadan kaldırabilirsek, bu sorguyu çok daha verimli hale getirir.

Geohash bu sorunun çözümü olabilir. Koordinatları bir metne kodlamaya ek olarak, geohash'in bir başka gücü de hash'in jeo-uzamsal özellikler içermesidir. Hash'ler arasındaki benzerlik, temsil ettikleri konumlar arasındaki coğrafi benzerliği anlayabilir. Örneğin, wxcghve ile temsil edilen iki alan wxcgdyakındır çünkü iki karma çok benzerdir ve accghve dydghbirbirinden uzaktır çünkü iki karma çok farklıdır.

Her satırın geohash'ını önceden hesaplayarak bu özelliği kümelenmiş tablo ile avantajımıza kullanabiliriz . Ardından, Chicago Union Station'ın geohash değerini hesaplıyoruz. Bu şekilde, hashlerin Chicago Union Station'ın geohash'ına yeterince yakın olmadığına dair tüm kayıtları önceden eleyebiliriz .

Nasıl uygulanacağı aşağıda açıklanmıştır:

1. Koordinatların coğrafi karma değerini depolayan yeni bir sütunla yeni bir tablo oluşturun.

CREATE TABLE `<project_id>.<dataset>.crime_with_geohash_lv5` AS (
  SELECT *, ST_GEOHASH(ST_GEOGPOINT(longitude, latitude), 5) as geohash
  FROM `bigquery-public-data.chicago_crime.crime` 
)

CREATE TABLE `<project_id>.<dataset>.crime_with_geohash_lv5_clustered` 
CLUSTER BY geohash
AS (
  SELECT *
  FROM `<project_id>.<dataset>.crime_with_geohash_lv5`
)

4. Geohash'i sorgu koşuluna ekleyin.

SELECT 
  * 
FROM 
  `<project_id>.<dataset>.crime_with_geohash_lv5_clustered`
WHERE 
  geohash = "dp3wj" AND 
  ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGFROMTEXT("POINT(-87.6402895591744 41.87887332682509)")) <= 2000

İş bilgisi ve yürütme ayrıntılarından, işlenen bayt sayısını ve taranan kayıtları önemli ölçüde azaltılmış olarak görebilirsiniz (1,5 GB'tan 55 MB'a ve 7M'den 260k'ya). Bir geohash sütunu tanıtarak ve onu bir küme anahtarı olarak kullanarak, yalnızca bir sütuna bakarak önceden sorguyu açıkça karşılamayan tüm kayıtları ortadan kaldırıyoruz.

Ancak, henüz bitirmedik. Çıktı satırlarının sayısına dikkatlice bakın, doğru sonucun 380k olması gereken yerde yalnızca 100k kaydı olduğunu göreceksiniz. Aldığımız sonuç hala doğru değil.

5. Komşu bölgeleri hesaplayın ve sorguya ekleyin.

Bu örnekte, tüm komşu hash'ler dp3wk, dp3wm, dp3wq, dp3wh, , , ve şeklindedir dp3wn. Bunun için çevrimiçi geohash explore kullanıyoruz , ancak yine, bu tamamen bir kod olarak yazılabilir.dp3wudp3wvdp3wy

Sorguya neden komşu bölgeleri eklememiz gerekiyor? Çünkü geohash, yalnızca bir konum tahminidir. Chicago Union İstasyonu'nun bölgede olduğunu bilmemize rağmen dp3wj, bölgede tam olarak nerede olduğunu hala bilmiyoruz. Üstte mi, altta mı, solda mı, sağda mı? hiçbir fikrimiz yok. En üstteyse, bazı veriler dp3wmona 2 km'den daha yakın olabilir. Sağdaysa, bölgedeki bazı verilerin dp3wn2 km'den daha yakın olması mümkündür. Ve benzeri. Bu nedenle, doğru sonucu elde etmek için tüm komşu hash'lerin sorguya dahil edilmesi gerekir.

Geohash seviye 5'in 5 km hassasiyete sahip olduğunu unutmayın. Bu nedenle, yukarıdaki şekildekiler dışındaki tüm bölgeler Chicago Union Station'dan çok uzakta olacaktır. Bu, büyük bir etkiye sahip olduğu için yapılması gereken bir diğer önemli tasarım seçimidir. Çok kaba olursa çok az kazanırız. Öte yandan, çok ince kesinlik düzeyi kullanmak, sorguyu karmaşık hale getirecektir.

Son sorgu şöyle görünür:

SELECT 
  * 
FROM 
  `<project_id>.<dataset>.crime_with_geohash_lv5_clustered`
WHERE 
  (
    geohash = "dp3wk" OR
    geohash = "dp3wm" OR
    geohash = "dp3wq" OR
    geohash = "dp3wh" OR
    geohash = "dp3wj" OR
    geohash = "dp3wn" OR
    geohash = "dp3tu" OR
    geohash = "dp3tv" OR
    geohash = "dp3ty"
  ) AND 
  ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGFROMTEXT("POINT(-87.6402895591744 41.87887332682509)")) <= 2000

Artık sonuç doğrudur ve sorgu toplamda 527 MB kaydı işler ve 2,5 milyon kaydı tarar. Orijinal sorgu ile karşılaştırıldığında, geohash ve kümelenmiş tablo kullanmak, işleme kaynağını yaklaşık 3 kez kaydeder. Ancak, hiçbir şey bedavaya gelmez. Geohash uygulamak, önceden seçilmesi gereken kesinlik düzeyi seçimi ve SQL sorgusunun ek mantığı gibi, verilerin önceden işlenme ve alınma biçimine karmaşıklık katar.

Çözüm

Bu makalede, jeo-uzamsal indeksin coğrafi verilerin işlenmesini iyileştirmeye nasıl yardımcı olabileceğini gördük. Ancak, önceden iyi düşünülmesi gereken bir maliyeti vardır. Günün sonunda, bedava bir öğle yemeği değil. Düzgün çalışması için hem algoritmanın hem de sistem gereksinimlerinin iyi anlaşılması gerekir.

İlk olarak https://thanakornp.com adresinde yayınlandı .