[postgis-users] Performance: PostGIS vs Shape
Johannes Sommer
johann.online at gmx.de
Fri Nov 9 05:52:49 PST 2007
Hi Paul!
Thank you for your comments.
Paul Ramsey schrieb:
> There should not be significant differences between the JUMP/Kosmo
> geometry processing and PostGIS, since they are the same algorithmic
> base. Probably the different lies in your combination of Intersects()
> and Intersection(), that guarantees a full topological calculation
> TWICE for each candidate row. The algorithm in the desktop systems is
> probably smarter.
>
> SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
> ...
> FROM lsg b, bze_wze a
> WHERE a.geometry && b.geometry;
>
> Try removing the Intersects() and just letting the && winnow things
> down. I would hope for comparable results, but there is no reason the
> database should be any faster, given what you are testing.
I tested the query (serveral times) and it seems that the speed
hierachy (from fastest to slow) is the following:
1. 5:49 min (without intersection AS...)
SELECT
...
FROM lsg b, bze_wze a
WHERE a.geometry && b.geometry
AND intersects (a.geometry, b.geometry);
2. 8:12 min
SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
...
FROM lsg b, bze_wze a
WHERE a.geometry && b.geometry
AND intersects (a.geometry, b.geometry);
3. 11:55 min
SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
...
FROM lsg b, bze_wze a
WHERE a.geometry && b.geometry;
In the first case I obviously get no geometry column.
How can I change the query to get a intersection geometry column without
using "SELECT intersection (...) AS ?"
greetings,
Johannes Sommer
>
> On 8-Nov-07, at 5:16 AM, Johannes Sommer wrote:
>
>> 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
>
> _______________________________________________
> 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