[postgis-users] Performance: PostGIS vs Shape
Milo van der Linden
mlinden at zeelandnet.nl
Thu Nov 8 06:05:58 PST 2007
Whit postGIS you always have the overhead of making a connection to the
database, authentication whilst with a shapefile it is just disk IO access.
For my own business case(s): being able to have users edit
simultanously, restrict user access to certain tables, data validation
with triggers, restricted user access and so on is a major PLUS to
select postGIS above shape. Our customers have no problem with a minute
wait time to get the data in their openJUMP or quantumGIS. In openJUMP
they work "cached local" giving them all the functions they need and
then when they decide to upload the dataset, validation takes place.
For view only; we have a mapserver implementation that other users can
access as a WMS layer from their GIS or Google Earth.
So it is all a matter of favour and the way you want to use it.
My general criteria?
If it needs to be multi-user, many changes to the dataset; postGIS
If it is static, single user, large amount of data, little changes; shape
kind regards,
Johannes Sommer schreef:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
--
Milo van der Linden
skype: milovanderlinden <skype:milovanderlinden?add>
mlinden at zeelandnet.nl <mailto:mlinden at zeelandnet.nl>
milovanderlinden at gmail.com <mailto:milovanderlinden at gmail.com>
milo at 3dsite.nl <mailto:milo at 3dsite.nl>
http://www.3dsite.nl
De informatie in dit bericht reflecteert mijn persoonlijke mening en
niet die van een bedrijf of instantie. Aan de informatie kunnen geen
rechten worden ontleend. Indien dit bericht onderdeel is van een forum,
mailing-list of community dan gelden automatisch de bij het betreffende
medium behorende voorwaarden. The information in this message reflects
my personal opinion and not that of a company or public body. All rights
reserved.If this message is contained in a mailing-list or community,
the rights on the medium are automatically adapted.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071108/46e691b7/attachment.html>
More information about the postgis-users
mailing list