[postgis-users] compute "difference" between two roads

[c. m.]

hi michael,
thanks a lot for your solution - it works like this.

cheers,
cesare


>From: Michael Fuhr <mike at fuhr.org>
>Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>Subject: Re: [postgis-users] compute "difference" between two roads
>Date: Fri, 8 Sep 2006 21:57:32 -0600
>
>On Fri, Sep 08, 2006 at 03:38:09PM +0200, c. m. wrote:
> > thanks a lot again. i'have attached the two source table's and the 
>result
> > table (diff). i have restricted the test data set to a street I know 
>that
> > leads partially into a forest.
> >
> > here the query again:
> >
> > select s.*, difference(s.wkb_geometry, f.intersection_geom) as diff_geom
> > into test_diff
> > from test_sidestreets as s, test_foreststreets as f
> > where s.feature_id = f.feature_id
>
>I think the problem is that the tables have multiple rows that match
>a given feature_id so you're effectively doing a cross join (cartesian
>product) of the matching rows.  The example data has 6 rows in
>test_foreststreets and 12 rows in test_sidestreets, so the resulting
>(cross) join has 72 rows and you're calculating the difference of
>each of the 72 pairs of geometries.
>
>Something like this might do what you want:
>
>SELECT s.feature_id, Difference(s.geom, f.geom) AS diff_geom
>FROM (SELECT feature_id, GeomUnion(wkb_geometry) AS geom
>       FROM test_sidestreets
>       GROUP BY feature_id) AS s
>JOIN (SELECT feature_id, GeomUnion(intersection_geom) AS geom
>       FROM test_foreststreets
>       GROUP BY feature_id) AS f ON f.feature_id = s.feature_id;
>
>The subqueries use the GeomUnion() aggregate to build a single
>geometry for each feature_id; joining on feature_id then returns
>one row per feature_id, so Difference() should compute the difference
>between the two aggregate geometries for each feature_id.
>
>--
>Michael Fuhr
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users