Johannes, When doing a comparison between two formats for speed tests I would recommend using a very large data set. If the tests are taking only a few hundred seconds to run it's difficult to come to any conclusions. Try doing some tests with 1,000,000 records to see how they compare. For large data sets shape files are not feasible.
The other thing to consider is the tuning of the database server. By default postgis comes with really low settings for the amount of memory used by a server and a low maximum for the memory used by each connection. Recently I was running a process against 4,000,000 records with the default settings it did not return a result even after 4 days and I wasn't even using spatial queries! By setting the correct memory it reduces the amount of temporary disk needed which thus improves the speed. No when you're comparing the speed of having data loaded in memory with doing the same process in a database I would expect the one in memory to run faster. As the database is having to load the data from the disk, execute the algorithm and then ship the data across the network. One thing you might want to do is use EXPLAIN with your SQL at the psql prompt to make sue it is actually using the indexes. Paul 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 > > > _______________________________________________ > jump-users mailing list > [email protected] > http://lists.refractions.net/mailman/listinfo/jump-users _______________________________________________ jump-users mailing list [email protected] http://lists.refractions.net/mailman/listinfo/jump-users
