[postgis-users] Performance: PostGIS vs Shape
Johannes Sommer
johann.online at gmx.de
Fri Nov 9 04:16:47 PST 2007
Hi Milo,
> 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.
I noticed this and did the queries directly in pgadmin3 - without
connecting through a postgresql driver from a GIS app.
>
> 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,
Thanks for your comments. I see the positive features (multi-user, user
restrictions, contraints etc...) of postGIS - no doubt about it.
I was just wondering about the speed because I always thought, that
access to stored data in a database is faster due to indices.
>
> 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.
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list