Stefan, In jump shape files are fully loaded into memory.
Paul Stefan Steiniger wrote: > my notes: > > i am not sure that OpenJUMP uses an index structure for the queries on > shp files per default - but it may use some other nice pre-tests. (we > were talking about indexing for shape files.. but...) > Which queriy tools did you use in OJ??? > > 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. > > stefan > > Paul Austin schrieb: >> 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 >> >> > _______________________________________________ > 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
