The SISDB PostGIS/Oracle Datastore I wrote follows the JUMP Datastore API and as such it uses the built-in feature cache. It reads in memory only the data falling inside the requested bounding box (I think it's what the a view is "seeing" at a certain moment in time). I don't know how the JUMP cache works, so I have no idea about how much features it keeps available in-memory and for how long. Anyway, pardon me, but I found this discussion about PostGIS vs Shape not well put. It's quite obvious that something working completely in-memory is faster than something working off a hard disk. But memory is not unlimited, and when it finishes the operating system may start to swap it off to hard disk, so it will get much slower than a RDBMS. It also depends about what operations you are performing on your data. Simply retrieving it, doing a single intersection, some more complex spatial operation??? Also it depends very much from the hardware used. If JUMP is running on a machine along with PostGIS, the machine may become slow. But if PostGIS is running on a separate server it may be much faster. Also consider that the operating system and the RDBMS have their own caches. So if PostGIS is running on a powerful server with a lot of memory it may well be that the entire spatial table is kept in-memory anyway. In my opinion Shapes are good for very small data sets, but for any serious use, a RDBMS is a better choice. Performances can always be enhanced using better hardware, more memory, doing a good tuning, organizing data and indexes in smart ways. But a RDBMS gives you transactions, access control, concurrent access, etc. Bye Paolo Rizzi
-----Messaggio originale----- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] conto di Larry Becker Inviato: venerdì 9 novembre 2007 23.32 A: JUMP Users Discussion Oggetto: Re: [jump-users] Performance: PostGIS vs Shape 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> > 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: <mailto:[email protected]> > [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
