[Croatia] Baze testiranje - nastavak

[Darko Boto]

2011/3/17 Dražen Odobašić <dodobas at geoinfo.geof.hr>:
> Pozdrav,
>
> dakle imao sam zanimljiv problem...
>
> Treba napraviti tzv. heatmapu za područja pretraživanja za cijeli svijet
> (korisnik unosi geometriju po kojoj pretražuje). U svemu tome je i vremenska
> komponenta, odnosno svako pretraživanje ima datum, pa se onda treba
> omogućiti generiranje heatmape za prošli tjedan/mjesec/...
>
> Nakon više ideja kako to riješiti odlučih složiti grid i onda brojati koliko
> poligona pretraživanja siječe pojedini grid element i te rezultate spremati
> u bazu kako bi se kasnije mogli pretraživati.
>
> Grid za cijeli svijet (WGS84), ako se uzme stupanjska podjela ima 360*180 =
> 64800 ćelija, odnosno poligona, ako se uzme četvrtina stupnja onda se broj
> ćelija povećava ma 360*180*16 = 1036800 (mnogo poligona).
>
> Iako je broj pretraživanja relativno malen (550) rezultat presjeka
> pretraživanja i grida je ~700000 zapisa u bazi.
>
> U ovom slučaju koristio sam jednostavni ST_Intersects te se upit na
> postgresql9/postgis1.5 bazi izvrti za ~8.5 sek (s indeksima) ili ~9 sek ako
> se ne koriste indeksi.
>
> Tzv. test case je - određivanje presjeka relativno velikog broja poligona s
> malim setom poligona.
>

ajd posalji podatke pa cu probati cim uhvatim vremena.
Mario, Alen i ja smo se zaigrali sa slicnim primjerom:
- 150 prilicno razvucenih polygona u intersectu sa oko 20 000 malih
polygona i query se vrtio predugo od 4 do 12 min na mssql-u (Mario
uspio spustiti na 2:26) i oko 26 sec na postgisu (u pocetku skoro
2min)
Evo copy/paste prepiske pa ako se kome da igrati neka pogleda.

---------------------------
CREATE TABLE intersect_priv AS SELECT poly_zgrade.* FROM poly_zgrade,
int_zona WHERE ST_INTERSECTS(poly_zgrade.the_geom, int_zona.the_geom)
= 't';

postgis: 26 sec
msql: preko 4min (???)

Presporo i na postgisu a o MSSQL-u da ne govorim.. potpuno neprihvatljivo.
Podaci su u attacmentu.
---------------------------
Ubacio sam podatke unutra i pokušao napraviti ove query-je:

SELECT * FROM dbo.poly_zgrade,dbo.int_zona WHERE
poly_zgrade.the_geom.STIntersects(int_zona.the_geom) = 1;

i

SELECT * FROM dbo.poly_zgrade t JOIN dbo.int_zona s ON
t.the_geom.STIntersects(s.the_geom) = 1;

i trajao je cca. 12 min. (pojedinačno). ... što mi se čini jako puno.
Treba uzeti u obzir za je riječ o virtualci s 1GB RAM-a.
Da, istina, malo je to predugo ... morati ću pogledati malo više zašto
se to dešava.

---------------------------
i zakljucak:
evo uhvatio sam malo vremena da se poigram s onim setom podataka koje
si mi poslao.
Čini mi se da je stvar bila u indeksu jer sam uspio doći do 2:52 min.
query time, na virutalci s 1GB RAM-a.
MS Sql Server može koristiti samo jedan spatial index odjednom, zato
dolje navedena queryija su toliko drugačija (2:52min vs. 4:55min.).
Logično je da je onaj query koji koristi indeks na većem skupu
podataka bolji od onoga queryija koji koristi na manjem skupu. Zato je
to drugi query sporiji od prvog.
Tweekanje indeksa nije imao velikog utjecaja. Igrao sam se s gridom i
sa CELLS_PER_OBJECTS (ostalo nema nekog utjecaja).
Ako sam dobro skužio na netu, čini mi se da su ovakvi prostorni upiti
daleko sporiji na MS SQL Server nego na PostGIS. Vidio sam dosta
primjera na netu gdje ljudi javljaju slične stvari. Stvar je u
implementaciji indeksa gdje MS SQL Server koristi multigrid indeks dok
PostGIS R-Tree sa MBR-om, što je navodno puno brže za ovako nešto nego
multigrid indeks. Eto prvog zaključak, ne raditi spatial join s MS SQL
Server-om ako nije potrebno. :)


Kreirani index:

CREATE SPATIAL INDEX [sidx_int_zona] ON [dbo].[int_zona]
(
    [the_geom]
)USING  GEOMETRY_GRID
WITH (
BOUNDING_BOX =(15.9, 43, 17.1, 43.6), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 =
HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON
[PRIMARY]
GO


CREATE SPATIAL INDEX [sidx_poly_zgrade] ON [dbo].[poly_zgrade]
(
    [the_geom]
)USING  GEOMETRY_GRID
WITH (
BOUNDING_BOX =(15.9, 43, 17.1, 44), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 =
HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON
[PRIMARY]
GO


Query 1:

Query time: 2:52 min.
SELECT a.ID, b.ID

  FROM dbo.poly_zgrade a with(index(sidx_poly_zgrade)), dbo.int_zona b

  WHERE a.the_geom.STIntersects(b.the_geom) = 1

Query 2:

Query time: 4:55 min.
SELECT a.ID, b.ID

  FROM dbo.int_zona a with(index(sidx_int_zona)),dbo.poly_zgrade b

  WHERE a.the_geom.STIntersects(b.the_geom) = 1


> E sad, upit se vrtio na laptopu i5 M450 (Linux), s nekim osnovnim 'tuningom'
> baze. Ako vas zanima mogu pripremiti podatke i funkcije pa isprobajte...
>
> Dražen
> _______________________________________________
> Croatia mailing list
> Croatia at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/croatia
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: data.zip
Type: application/zip
Size: 2081814 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/croatia/attachments/20110319/d956c6fb/data-0001.zip