Hi everybody!
I made a little performance test: shape vs. postgis.
I always thought that a select query in a postgresql database with
postgis extension
is much faster than a comparable query inside a Desktop GIS that has
loaded shape-files.
So I compared various queries with shape-files (different sizes and
geometry types) and relations in a postgis database.
The shapes I queried in the Desktop GISs have been converted to a
postgis-layer.
I used Kosmo, Openjump and ArcMap as Desktop GISs.
However the result is the following: PostGIS is always slower
than the
Desktop GISs.
I know Openjump is using Quadtrees to index a shape-file, about
Kosmo
and ArcMap I have no idea, but I think they use an index too.
And in postgis I build an GiST- index on the queried relations
and did
a VACUUM ANALYZE on them.
So why is a (spatial) query in a database slower, than a query of a
shape-file? Can anybody explain this to me?
Anyone with similar experiences?
Perhaps there is something wrong with the "select query" or with the
database's properties?
System: 2,8 Ghz, 1 GB RAM, S-ATA HDD, Windows XP Pro
Software: Postgresql 8.2.5 with PostGIS 1.3
OpenJUMP 1.2 D
ArcMap 9.1
Thanks for your comments.
greetings,
Johannes Sommer
*A. MULTIPOLYGONS and POINTS*
1.
*shapes: *lsg (678 MULTIPOLYGONS), 14,5 MB, bze_wze, 52 KB, (386
POINTS),
intersection
Results:
*
Openjump 1.2 D: 24 sec
*
ArcMap 9.1: 18 sec
*
Kosmo 1.1: 1:24 min
#
both are marked as „Layer in memory“: 0:59 min
1.
*postgis-layer: *lsg (678 MULTIPOLYGONS, GiST-Index on
geometry),
bze_wze (386 POINTS, GiST-Index on geometry)
Result:
+
Openjump 1.2 D: 24 sec
+
Kosmo 1.1: 2:44 min
#
both are marked as „Layer in memory“: 1:00 min
+
PostGIS-SQL:
#
intersection with && (bbox): 2:45 min
SELECT intersection(a.geometry, b.geometry) AS
intersection_geom,
a.gid, a.tnr, a.ist_x, a.ist_y, a.holzboden, a.eu_punkt,
b.rok_prj_nr, b.id, b.name, b.area_qm
FROM lsg b, bze_wze a
WHERE intersects(a.geometry, b.geometry);
#
intersection with && (bbox): 2:24 min
SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
a.*, b.rok_prj_nr, b.id, b.name, b.area_qm
FROM lsg b, bze_wze a
WHERE a.geometry && b.geometry
AND intersects(a.geometry, b.geometry);
*B. POLYGONS and POINTS*
1.
*shapes:** *ffh_gebiete (2670 POLYGONS), 12,7 MB, bze_wze, 52
KB,
(386 POINTS)
intersection
Results:
*
Openjump 1.2 D: 1 sec
*
ArcMap 9.1: 12 sec
*
Kosmo 1.1: 6 sec
o
Layer in memory: 1 sec
2.
*postgis-layer: *ffh_gebiete (2670 POLYGONS, GiST-Index on
geometry), bze_wze (386 POINTS, GiST-Index on geometry)
Results:
+
Openjump 1.2 D: 1 sec
+
Kosmo 1.1: 8 sec
#
Layer in memory: 2 sec
+
PostGIS-SQL:
# intersection without && (bbox): 35 sec
# intersection with && (bbox): 17 sec
*C. POLYGONS and POLYGONS*
1.
*shapes:** *ffh_gebiete (2670 POLYGONS) 12,7 MB, * *gk500_1
(16503
POLYGONS) 22,8 MB
intersection
Result:
*
Openjump 1.2 D: 1:58 min
*
ArcMap 9.1: 38 sec
*
Kosmo 1.1: 4:02 min
2.
*postgis-layer: *ffh_gebiete (2670 POLYGONS, GiST-Index on
geometry), gk500_1 (16503 POLYGONS, GiST-Index on geometry)
Result:
+
Openjump 1.2 D: 2:00 min
+
Kosmo 1.1: 3:59 min
+
PostGIS-SQL:
# intersection without && (bbox): 33:09 min
# intersection with && (bbox): 8:02 min
_______________________________________________
jump-users mailing list
[email protected]
http://lists.refractions.net/mailman/listinfo/jump-users
