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
