[postgis-users] optimazing intersection

[Markus Schaber]

Hi, Toni,

toni hernández wrote:

> The thing is that I have lots of multilinestring in the database.
> I have to intersect all of these multilinestrings with other linestrings
> that are not in the database but are created on-the-air. So they are in
> the memory.
> To be more precesice I have to intersect every multilinestring in the
> database with every linestring created on-the-air.
> 
> The result of the intersection raises a multipoint geometry. The problem
> is that for every point in the multipoint geometry I need to know the
> multilinestring (in the database) and the linestring (on-the-air) that
> produce that point when intersecting.

The multipoint will lose this information.

I assume that the on-the-air linestrings are few compared to those in
the database, and that your application can loop over them. Then do
something like:

stat = connection.prepareStatement("SELECT id, intersection(geom,?) as
geom FROM mytable WHERE geom && ? AND intersects(geom, ?)"

for myline in list-of-on-the-air-geometries:
    stat.setparam(1, myline.geom)
    stat.setparam(2, myline.geom)
    stat.setparam(3, myline.geom)
    resultset = stat.execute()
    for row in resultset:
        print myline.id, "intersects", row.id, "at", row.geom


If your have much on-the-air linestrings, you should load them into the
database (preferrable using copy), into a (temporary) table:

CREATE TABLE on_the_air (id int, the_geom geometry);

COPY on_the_air(id, the_geom) FROM '/foo/bar';

SELECT on_the_air.id as airid, mytable.id as myid,
intersection(on_the_air.geom, mytable.geom) FROM on_the_air JOIN mytable
ON on_the_air.geom && mytable.geom AND intersect(on_the_air.geom,
mytable.geom);


For both queries, there's a high advantage of having GIST indices on the
geometry columns.

HTH,
Markus


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org