Hi Martin, >Of course, data from an external database needs to be read in initially, >which should be included in the overall processing time - but I don't >think this is the scenario that Johannes tested, right?
I don't know. I've never looked at the PostGIS code myself, and the assumption that it was reading the database on-the-fly seemed to be implicit in the question. I thought the PostGIS datastore code didn't necessarily read everything into memory. I'm sure Paulo or someone can clarify this point for the other plugins. regards, Larry On Nov 9, 2007 3:34 PM, Martin Davis <[EMAIL PROTECTED]> wrote: > Larry got this right... Spatial Query uses an index to get better than > O(n*n) performance. > > As Paul said, databases have to read the data off disk, so they are much > slower than memory-resident data in a client-side tool like JUMP. > > I suspect that the spatial operations are faster in JTS than in > PostGIS/GEOS too, thanks to Java's more efficient memory handling, and > no need to go through the disk image->PostGIS binary->GEOS binary > conversion. > > @Larry: not sure what you mean by "bad performance". JUMP only works on > in-memory data (unless you guys have made some big changes), so the OJ > SpatialQuery operator is never working directly against disk. Of > course, data from an external database needs to be read in initially, > which should be included in the overall processing time - but I don't > think this is the scenario that Johannes tested, right? > > Larry Becker wrote: > > Hi Johannes, > > > > >I used: Tools -> Query -> Spatial Query > > > > So your original assumption that OpenJump was using a Quad Tree index > > is correct in this case. Spatial Query builds an index (for > > non-trivial layers) on the fly to optimize the query. Ordinary > > queries like "within view rectangle" are efficient enough using > > bounding box intersections because they are intersecting one view BB > > with all layer BB. Spatial Query, on the other hand, is a many to > > many operation and justifies the index. > > > > Now AFAIK OpenJump uses the same technique for Spatial Query on all > > layer types, so it may be that OJ has to read every record in the > > database sequentially to build the in-memory Quad Tree. That would > > explain the bad performance. > > > > regards, > > > > Larry Becker > > > > On Nov 9, 2007 6:42 AM, Johannes Sommer <[EMAIL PROTECTED] > > <mailto:[EMAIL PROTECTED]>> wrote: > > > > Hi Stefan, > > > > > Which queriy tools did you use in OJ??? > > I used: Tools -> Query -> Spatial Query > > > > > > and I guess an object-oriented datastructure in memory, that only > > > needs to be runned through ones, may be as well much faster than > > a set > > > of relational tables in a database on disks. > > Ok, thanks for your answer! > > > > > >> 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 <http://b.id>, b.name <http://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 <http://b.id>, b.name > > <http://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] > > <mailto:[email protected]> > > >>> http://lists.refractions.net/mailman/listinfo/jump-users > > >> > > >> > > >> _______________________________________________ > > >> jump-users mailing list > > >> [email protected] > > <mailto:[email protected]> > > >> http://lists.refractions.net/mailman/listinfo/jump-users > > >> > > >> > > > _______________________________________________ > > > jump-users mailing list > > > [email protected] > > <mailto:[email protected]> > > > http://lists.refractions.net/mailman/listinfo/jump-users > > > > > > > _______________________________________________ > > jump-users mailing list > > [email protected] > > <mailto:[email protected]> > > http://lists.refractions.net/mailman/listinfo/jump-users > > <http://lists.refractions.net/mailman/listinfo/jump-users> > > > > > > > > > > -- > > http://amusingprogrammer.blogspot.com/ > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > jump-users mailing list > > [email protected] > > http://lists.refractions.net/mailman/listinfo/jump-users > > > > -- > Martin Davis > Senior Technical Architect > Refractions Research, Inc. > (250) 383-3022 > > _______________________________________________ > jump-users mailing list > [email protected] > http://lists.refractions.net/mailman/listinfo/jump-users > -- http://amusingprogrammer.blogspot.com/
_______________________________________________ jump-users mailing list [email protected] http://lists.refractions.net/mailman/listinfo/jump-users
