Sıra | DOSYA ADI | Format | Bağlantı |
---|---|---|---|
01. | Mekansal Veritabanlarında Hızlı Sorgulama | ppt | Sunumu İndir |
Transkript
Mekansal Veritabanlarında Hızlı Sorgulama Arzu KÜTÜKCÜ Osman ABUL TOBB Ekonomi ve Teknoloji Üniversitesi
Giriş 1. Çalışmanın Tanımı ve Amacı 2. Coğrafi Veriler İçin Kullanılan Teknolojiler 3. Oracle Spatial Teknoloji 4. Çalışmada Kullanılan Veriler ve İzlenen Metodoloji 5. Performans Değerlendirilmesinde Kullanılan Araçlar 6. Veritabanı Performans Testleri 7. Sonuçlar
Çalışmanın Tanımı ve Amacı Hazırlanan coğrafi veri katmanları (havza, akarsu, Agi, göl vb…) kullanılacak ara yazılımlar ile Oracle veritabanına aktarılarak, kullanıcı tarafından herhangi bir program yüklenilmesine gerek kalmadan internet tarayıcısı üzerinden çalışacak bir uygulama yazılımı hazırlanmıştır. Hazırlanan uygulama yazılımı ile, veritabanına aktarılan coğrafi veriler kullanılarak değişik coğrafi sorguların geliştirilmesine ve bu sorgular üzerinde çeşitli performans değerlendirilmesi yapılarak iyileştirme, optimizasyon çalışmalarının yapılması amaçlanmıştır.
Coğrafi Veriler İçin Kullanılan Teknolojiler Spatial Veritabanı Avantajları Dezavantajları ArcSDE Lider CBS sağlayıcı tarafından oluşturulmuş (ESRI) En iyi CBS işlevselliği Fiyatları (tüm sistem için fiyatı 60.000 ABD $’na kadar çıkabilmektedir.) Oracle Spatial Şirketlerin ihtiyaçları için yeterli Tam olarak aradığımız şey Önde gelen veritabanı sağlayıcısı Yeterli destek alınabilir Fiyatlar (tüm sistem için fiyatı 50.000 ABD $’na kadar çıkabilmektedir.) Microsoft SQL Server 2008 Spatial Vector veriler için gelişmiş özellikler Kolay Anlaşılır Yeterli destek alınabilir Geocoding Network Data Model Topology Network
IBM DB2 Spatial Extender Şirketlerin ihtiyaçları için yeterli Tam olarak aradığımız şey Önde gelen veritabanı sağlayıcısı Yeterli destek alınabilir ArcExplorer’la çalışır Fiyatları (free with $30,000 purchase of IBM DB2) ($20,000 < ArcSDE sistem $10,000 < Oracle sistem) ArcSDE ile karşılaştırıldığında kısıtlı CBS yetenekleri PostGIS Ücretsiz Kolaylıkla Download Edilebilir Ödenilen Kadar alınır Son derece küçük bir pazar Az bilgi sahibi kişiler Sınırlı Mali Destek Uzun ömürlü olması risk altında Coğrafi Veriler İçin Kullanılan Teknolojiler
Spatial Teknoloji Oracle Spatial, Spatial olarak da adlandırılan konumsal verilerin sorgulanması, güncellenmesi ve depolanmasına yardımcı olan SQL şema ve fonksiyonları içeren teknolojidir. Spatial teknoloji; 1. MDSYS Şeması, 2. Spatial index mekanizması, 3. Spatial analiz işlemleri için fonksiyonlar, prosedürler ve operatörler, 4. Tuning işlemleri için operatörler, 5. Topoloji veri modeli, 6. Network data model oluşturulması, 7. GeoRaster veriler üzerinde analizlerin yapılabilmesi, gibi özellikleri içerir.
R-tree Index R-tree indexler B-tree index yapısından türetilmiş, spatial veriler için yapılan performans testlerinde büyük oranda başarılı olmuş, en yaygın kullanılan algoritmadır. Spatial veriler için K-D-B ve Quad Tree algoritmalarının pek de verimli olmadığı gözlenmiş, K-D-B Tree algoritmasının daha çok Point veriler için daha uygun olduğu görülmüştür. Spatial Teknoloji
R-tree Index CREATE INDEX agi_spatial_idx ON AGI(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX; Spatial Teknoloji
R-tree Index Spatial Teknoloji
Q-Tree Index Quadtree Ayrıştırma ve Morton Kodlaması Küçük ve Büyük boyutlu Tile ile Geometri Gösterimi Spatial Teknoloji
Q-tree Index Görünümü CREATE INDEX HAVZALAR_Q_SIDX ON HAVZALAR_Q(GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('geodetic=FALSE SDO_LEVEL=8'); Spatial Teknoloji
Spatial index sorgu değerlendirme mekanizması
Spatial indeks sorgu değerlendirme mekanizması SELECT distinct(C.objectid) FROM akarsu R, iller C WHERE R.Akarsu_ad='Çoruh N.' AND SDO_WITHIN_DISTANCE(C.geom, R.geom, 'querytype=FILTER DISTANCE=100 UNIT=KM')='TRUE' ;
Yaygın Saptial Operatörler sdo_filtersdo_nnsdo_within_distance
Tablo Adı Satır Sayısı SGO_GEOMETR Y veri tipi Havzalar 28 (Bir havza alt katmanları olması sebebiyle) Polygon AGI 2495 Point Akarsu 23 797 (Nehir, Dere, Çay…)- Bir Akarsu yaklaşık 140 Line Segmentten oluşmaktadır. LineString İller 81 Polygon Göller 132 Polygon Baraj 1289 Polygon Nehirler 1902 Linestring Nehir_Link 118 Linestring Nehir_Node 121 Point Kullanılan Veriler ve Özellikleri
Çalışmada kullanılan Stored Prosedürler
Sistemin Genel Yapısı
Coğrafi Veri Altyapısı Mimarisi
Halihazırda Kullanılan Harita WEB Servisleri • Google Earth • Google Maps • Virtual Earth • Yahoo Maps
Google Maps API Google Maps JavaScript API, web sayfalarına Google Maps’in gömülmesini sağlar. API kullanmak için, ilk olarak “http://code.google.com/intl/tr/apis/maps/ signup.html” adresinden kayıt olmak suretiyle bir API anahtarı edinmek gerekir. Bir API anahtarı alındığında harita uygulaması geliştirebilinir. Google maps üzerinden haritalara açık bir şekilde ulaşmak mümkün olsa da, Api açık kaynak kodlu değildir. http://maps.google.com/maps? file=api&v=2&key=ABQIAAAAzr2EBOX UKnm_jVnk0OJI7xSosDVG8KKPE1- m51RBrvYughuyMxQ- i1QfUnH94QxWIa6N4U6MouMmBA
Openlayers API OpenLayers API herhangi bir web sayfasına kolaylıkla dinamik bir harita koyma imkanı sağlar. OpenLayers, dünya çapında birçok organizasyon tarafından geliştirilmiş ve desteklenmiştir. Web tarayıcılarında harita verisi görüntülemek için kullanılan tamamen açık kaynak kodlu bir JavaScript kütüphanesidir. OpenLayers coğrafi veri erişimi için OpenGIS Consortium standartlarından Web Mapping Service (WMS) and Web Feature Service (WFS) protokollerini destekler. Openlayers Api’si kullanarak Google maps haritalarını görüntülemek mümkün olmaktadır.
Çalışma Kapsamında Kullanılan Sorgu Çeşitleri Akarsulara Yakın AGI’lerin Bulunması Havzalar, Havza İçinde Kalan Agi’ler ve Nehirlerin Bulunması İller içinde kalan Agi’ler ve Havzalar İçinde Kalan İller Göl içinde kalan Agi’ler ve Havzalar İçinde Kalan Göller Barajlar içinde kalan Agi’ler ve Havzalar İçinde Kalan Barajlar
Havza sınırları içinde kalan AGİ’lerin bulunması için tasarlanmış web sayfasından görünüm
Havza sınırları içinde kalan Baraj’ların bulunması için tasarlanmış web sayfası görünümü
Shp2Sdo.exe Çalıştırılması shp2sdo.exe agi agi -g geom -d -x (-180,180) -y (-90,90) -t 0.5 -vspatial sql>sqlplus spatialndx/123@tumay @agi.sql cmd>sqlldr spatialndx/123@tumay control=agi.ctl CREATE INDEX agi_sidx ON agi(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_LEVEL=8'); INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('AGI', 'GEOM', MDSYS.SDO_DIM_ARRAY (MDSYS.SDO_DIM_ELEMENT('X', -180.000000000, 180.000000000, 0.500000000), MDSYS.SDO_DIM_ELEMENT('Y', -90.000000000, 90.000000000, 0.500000000) ), );
Veritabanı Performans Değerlendirme
SQL Trace and TKPROF SQL trace çalıştırılan SQL’lerin izlenmesini sağlamaktadır. Üretilen trace file’larının daha okunaklı olması içinse, TKPROF hizmetinden yararlanarak trace file’lar anlaşılır bir formatta alınabilmektedir. call count cpu elapsed query current rows ------------- ------- ---------- ---------- ---------- -------- Parse 1 0.00 0.05 831 0 0 Execute 1 0.00 0.00 0 0 0 Fetch 2 0.40 0.37 131 70523 5 ------- ------ ------- ---------- ---------- ---------- ------- total 4 0.40 0.43 962 70523 5
Belirli Bir mesafe içinde kalan geometrilerin tesbiti Sorg No. Test edilen SQL cümlesi Açıklama 1. SELECT a.objectid FROM river_node rn,agi_b a WHERE rn.node_id=173 AND SDO_WITHIN_DISTANCE(a.geom, rn.geometry, ‘querytype=FILTER DISTANCE=30 UNIT=KM' )='TRUE' ORDER BY a.OBJECTID; Fırat nehri node’ları üzerinde seçilen bir node’a 30km uzaklıkta bulunan Agi’lerin bulunması 2. SELECT a.objectid FROM river_node rn,agi_b a WHERE rn.node_id=173 AND SDO_WITHIN_DISTANCE (a.geom, rn.geometry, 'DISTANCE=30 UNIT=KM' )='TRUE' ORDER BY a.OBJECTID; Fırat nehri node’ları üzerinde seçilen bir node’a 30km uzaklıkta bulunan Agi’lerin bulunması 3. SELECT distinct(C.objectid) FROM akarsu R, iller C WHERE R.Akarsu_ad='Çoruh N.' AND SDO_WITHIN_DISTANCE(C.geom, R.geom, 'querytype=FILTER DISTANCE=100 UNIT=KM')='TRUE' ; Çoruh Nehri’ne 100 km. uzaklıkta bulunan illerin tesbiti 4. SELECT distinct(C.objectid) FROM nehirler R ,iller C WHERE R.Akarsu_ad='Çoruh N.' AND SDO_WITHIN_DISTANCE(C.geom, R.geom, 'DISTANCE=100 UNIT=KM')='TRUE' ; Çoruh Nehri’ne 100 km. uzaklıkta bulunan illerin tesbiti
Belirli Bir mesafe içinde kalan geometrilerin tesbiti
Akarsulara 200 metre mesafede bulunan Agi’lerin tesbiti Sorgu No Test edilen SQL cümlesi Açıklama 5. SELECT /*+ ORDERED */ a.objectid FROM akarsu rn, agi a WHERE SDO_WITHIN_DISTANCE (a.geom, rn.geom, 'DISTANCE=200 UNIT=METER ' )='TRUE'; Akarsulara 200 metre mesafede bulunan Agi’lerin tesbiti ordered hint’i kullanımı 6. SELECT a.objectid FROM akarsu rn, agi a WHERE SDO_WITHIN_DISTANCE (a.geom, rn.geom, 'DISTANCE=200 UNIT=METER ' )='TRUE'; Akarsulara 200 metre mesafede bulunan Agi’lerin tesbiti
Tablolardaki Tüm Geometriler İçin Belirli Bir mesafe içinde kalan geometrilerin tesbiti Sorgu No Test edilen SQL cümlesi Açıklama 7. SELECT a.objectid FROM akarsular rn, agi a WHERE SDO_WITHIN_DISTANCE(a.geom,rn.geom, 'DISTANCE=200 UNIT=METER ' )='TRUE'; Akarsulara 200 metre mesafede bulunan Agi’lerin tesbiti 8. SELECT a.objectid FROM akarsular aks, agi a , TABLE(SDO_JOIN('agi', ‘geom','akarsular','geom','DISTANCE=200 UNIT=METER')) jn WHERE jn.rowid1 = a.rowid AND jn.rowid2 =aks. rowid; Akarsulara 200 metre mesafede bulunan Agi’lerin tesbiti 9. SELECT a.objectid FROM akarsular aks, agi a ,TABLE ( SDO_JOIN ('akarsular','geom','agi','geom', 'DISTANCE=200 UNIT=METER')) jn WHERE aks.AKARSU_AD='Coruh N.' jn.rowid1 = a.rowid jn.rowid2=aks.rowid ; Havzalara 200 metre mesafede bulunan Agi’lerin tesbiti 10. SELECT a.objectid FROM akarsular rn, agi a WHERE rn.AKARSU_AD='Coruh N.' and SDO_WITHIN_DISTANCE(a.geom,rn.geom, 'DISTANCE=200 UNIT=METER ' )='TRUE'; Çoruh Nehrine 200 metre mesafede bulunan Agi’lerin tesbiti
3 3 ,3 7 1 ,1 2 0 ,8 2 0 ,6 8 3 3 ,3 3 0 ,7 7 0 ,8 7 1 ,1 3 0 5 10 15 20 25 30 35 40 Sorgu-7 Sorgu-8 Sorgu-9 Sorgu-10 (s ) CPU Elapsed Time 2 0 ,2 3 0 ,6 0 ,2 0 ,1 2 0 ,3 4 0 ,8 0 ,2 0 ,4 0 5 10 15 20 25 Sorgu-7 Sorgu-8 Sorgu-9 Sorgu- 10 (s ) CPU Elapsed Time R-tree Q-tree Tablolardaki Tüm Geometriler İçin Belirli Bir mesafe içinde kalan geometrilerin tesbiti
Bir Geometri İçinde Kalan Geometrilerin Tespit Edilmesi Sorgu No. Test edilen SQL cümlesi Açıklama 11. SELECT C.objectid FROM akarsular C, havzalar P, TABLE(SDO_JOIN('akarsular', 'geom', 'havzalar', 'geom','mask=inside')) T WHERE T.rowid1 = C.rowid AND T.rowid2 = P.rowid ; Havzalar içerisinde kalan akarsularun tesbiti 12. SELECT /*+ ORDERED */ a.objectid FROM havzalar b, akarsular a WHERE b.havzano=21 and SDO_RELATE(a.geom, b.geom,'mask=inside') = 'TRUE'; Seçilen Havza içerisinde kalan akarsularun tesbiti 13. SELECT /*+ ORDERED */ a.objectid FROM havzalar b, akarsular a WHERE b.havzano=21 and SDO_INSIDE(a.geom, b.geom) = 'TRUE'; Seçilen Havza içerisinde kalan akarsularun tesbiti
20 ,1 2 18 ,0 9 20 ,1 2 17 ,9 2 16,5 17 17,5 18 18,5 19 19,5 20 20,5 Sorgu-12 Sorgu-13 (s ) CPU Elapsed Time Bir Geometri İçinde Kalan Geometrilerin Tespit Edilmesi
Sorgu No. Test edilen SQL cümlesi Açıklama 15. SELECT /*+NO_INDEX*/ ct.objectid FROM river_node comp, AGI ct WHERE comp.node_id=173 and ct.havzano=21 AND SDO_NN(ct.geom, comp.geometry)='TRUE' AND ROWNUM<=5 ORDER BY ct.objectid; Seçilen Bir Geometriye En yakın Geometrilerin Bulunması 16. SELECT ct.objectid FROM river _node comp, AGI ct WHERE comp.node_id=173 and ct.havzano=21 AND SDO_NN(ct.geom, comp.geometry ,'SDO_BATCH_SIZE=100')='T RUE' AND ROWNUM<=5 ORDER BY ct.objectid ; Seçilen Bir Geometriye En yakın Geometrilerin Bulunması 17. SELECT /*+ FIRST_ROWS*/ a.objectid FROM river_node comp, agi a WHERE comp.node_id=173 and a.havzano=21 AND SDO_NN(a.geom, comp.geometry ,'SDO_BATCH_SIZE=100')='T RUE' AND ROWNUM<=5 ORDER BY a.objectid; Seçilen Bir Geometriye En yakın Geometrilerin Bulunması Seçilen Bir Geometriye En yakın Geometrilerin Bulunması
0 ,4 0 ,3 5 0 ,3 1 0 ,4 3 0 ,3 8 0 ,4 0 0,05 0,1 0,15 0,2 0,25 0,3 0,35 0,4 0,45 0,5 Sorgu- 15 Sorgu- 16 Sorgu- 17 (s ) CPU Elapsed Time Seçilen Bir Geometriye En yakın Geometrilerin Bulunması
Sorgu No. Test edilen SQL cümlesi Açıklama 1. SELECT distinct C.objectid FROM akarsu_q R, agi_q C WHERE R.Akarsu_ad='Firat N.' AND SDO_WITHIN_DISTANCE(C.geom,R.geo m, 'DISTANCE=0.05')='TRUE' order by C.objectid ; Q-tree index kullanılarak Fırat Nehrine seçilen node’a 0.05 birim mesafede bulunan Agi’lerin bulunması 2. SELECT distinct C.objectid FROM akarsu R,agi C WHERE R.Akarsu_ad='Firat N.' AND SDO_WITHIN_DISTANCE(C.geom, R.geom, ' DISTANCE=5 UNIT=KM')='TRUE' order by C.objectid ; R-tree index kullanılarak Fırat Nehrine 5 km. mesafede bulunan Agi’lerin bulunması Q-tree Index denemeleri
4, 39 0, 81 4, 51 0, 81 0 0,5 1 1,5 2 2,5 3 3,5 4 4,5 5 Sorgu-1 Sorgu-2 (s ) CPU Elapsed Time Q-tree Index denemeleri
Sorgu No. Test edilen SQL cümlesi Açıklama 3. SELECT /*+NO_INDEX*/ ct.objectid FROM akarsu_q comp, AGI_q ct WHERE ct.havza_no='08' and SDO_NN(ct.geom,comp.geom, 'SDO_LEVEL=9')='TRUE' AND ROWNUM<=5 ; Q-tree index kullanılarak 8. havzaya ait nehirlere en yakın 5 Agi noktasının tesbiti 4. SELECT /*+NO_INDEX*/ ct.objectid FROM akarsular comp, AGI ct WHERE ct.havza_no='08' and SDO_NN(ct.geom, comp.geom)='TRUE' AND ROWNUM<=5 ORDER BY ct.objectid; R-tree index kullanılarak 8. havzaya ait nehirlere en yakın 5 Agi noktasının tesbiti Bir geometriye en yakın geometrilerin tesbiti
8, 68 0, 18 8, 92 0, 14 0 1 2 3 4 5 6 7 8 9 10 Sorgu-3 Sorgu-4 (s ) CPU Elapsed Time Bir geometriye en yakın geometrilerin tesbiti
1.Çalışmada kullanılan verilerin coğrafi koordinat sisteminde tanımlı olması, 2.İhtiyaç duyulan sorguların gerektirdiği operatörlerin R-tree indeks ile kullanılabilir olması, 3.Q-tree indekslerin tuning işleminin pahalı işlemler olması 4.Q-tree indekslerle tüm yerkürenin modellenememesi gibi sebeplerden dolayı R-tree indeks seçilmiştir. Sonuçlar ve Değerlendirme
5.Mekansal indeks kullanımının performans üzerinde çok olumlu etkileri bulunmaktadır. 6.Yapılacak analize göre İndeks ve operatör seçimi de performans için önem taşımaktadır. Sonuçlar ve Değerlendirme
Teşekkürler…