[postgis-users] remove duplicate line segments from table

Martin Davis mbdavis at refractions.net
Mon Mar 31 10:20:47 PDT 2008

I would suggest what is needed here is the Hausdorff distance metric.  
It gives a measure of how far apart geometries are, not how close 
together they are.  I've used this successfully in JCS to implement 
exactly the kind of "approximate LineSegment dissolver" that this post 
is talking about.

It's relatively straightforward to implement the Hausdorff distance for 
line segments (but more more complex for more complex geometries).  You 
could probably do this as a PGSQL function and get reasonable performance.

Brent Wood wrote:
> --- Murray Richardson <murray.richardson at utoronto.ca> wrote:
>> Hello
>> I am trying to remove duplicate line segments from a table for segments 
>> with matching start and end coordinates.  However, I need to include a 
>> tolerance of about 2m since they are not exact matches.  Also the start 
>> and end coordinates of matching segmets may be reversed!?  Is there a 
>> canned way to do this with postGIS?
> Not that I know of, especially since you are not removing duplicates, but any
> lines which have a similar line (meeting an arbitrary definition of similar
> enough equivalency). 
> A total solution depends on your table structure & whether lines that are
> spatially equivalent given your definition may still be retained as distinct
> lines if they have different aspatial attributes. 
> Also, if one line has start/finish locations offset by 1.9m to the east of
> another line, and a third line is offset by 1.9m to the west, we have lines
> which are 3.8m apart, but are each "the same" as another line by your 2m
> restriction. If your fix drops the western one of the first pair, or generates
> a new line at a middle location before checking the western line you'll get a
> different result than if the lines are compared all together or in the reverse
> order (if you see what I mean :-)
> You can reverse the sequence of the points in a line where (for example)
> X(start ) > X(end) or Y(start) is > Y(end) to ensure the direction of all line
> segments is consistent, or your query can check the start against start &
> finish, & the finish against start & finish of other lines, so that line
> direction is irrelevant to the equivalence determining query.
> I think what you are looking at doing is certainly achievable with PostGIS, but
> you'll need to clarify your definition of equivalency, and also how you prefer
> to deal with situations where the same line has 1 equivalent, or > 1
> equivalent.
> Once you have that, an SQl, or sequence of SQL's, or script to carry it out
> should be feasible.
> HTH,
>   Brent Wood
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022

More information about the postgis-users mailing list