[postgis-users] remove duplicate line segments from table

[Brent Wood]

--- 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