[postgis-users] remove duplicate line segments from table

[Murray Richardson]

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?

Thanks for any help.

Murray Richardson
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Brent Wood]

--- Murray Richardson <murray.r...@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

[Martin Davis]

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.

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

[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