[postgis-users] remove duplicate line segments from table

[Murray Richardson]

Ok thanks for your help with this Brent.

There is no aspatial data of importance associated with the lines.

It is extremely rare that there will be three equivalent lines as you 
describe.  Basically I have doubly defined polygon edges (resulting from 
an alpha-shapes cgal routine) that need to be cleaned up before I run 
the polygonize query (currently takes too long to execute - actually it 
never finishes due to so many line edges).

Is there a away to use a spatial index to efficiently compare line 
segments within a  given  threshold  distance  and then snap them?  Even 
still I'm not sure which one would get snapped and which one would 
remain (maybe I could round the coordinates based on the threshold 
distance e.g to the nearest 2m interval?).  ArcGIS has a Feature to 
Polygon tool with an xytolerance value that seems to delineated polygon 
edges mid-way between segments within the tolerance distance.  Not sure 
how I could implement this myself.

Thanks again,

Murray










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