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

[Michael Fuhr]

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