[postgis-users] remove duplicate line segments

[Sandro Santilli]

On Fri, Jun 12, 2020 at 09:18:22AM +0000, paul.malm at lfv.se wrote:
> Hi,
> If someone could help me with this, I would be grateful.
> I've converted a polygon layer to a line layer. Sometimes the lines segments are a duplicate of another line segment (from an adjacent polygon).
> I would like to delete the duplicate lines and only store the one of all duplicate line segments. It doesn't matter which one I store.
> I've tried to store the line layer as a topology and erase the edges that are the same, but I don't have enough  knowledge of topologies. To be honest not much knowledge at all.
> I've tried this:
> DELETE FROM topo1.edge_data WHERE edge_id NOT IN (SELECT MAX(edge_id) FROM topo1.edge_data GROUP BY geom)

If you're using PostGIS Topology you especially if you don't know what
you're doing ("not much knownedge of topologies") you sould avoid
running direct DELETE on those tables. Rather, use the
topology editing functions:

  https://postgis.net/docs/Topology.html#Topology_Editing

For deleting edges you may want to use ST_RemEdgeModFace or
ST_RemEdgeNewFaces, but mind you: if the topology is valid,
what you call "duplicated lines" are not really duplicated,
or they would not be stored twice.

So, first of all make sure your topology is valid:

  https://postgis.net/docs/ValidateTopology.html

Then, if it is valid, you may be looking at edges which are
very close togheter but are still different, thus creating
tiny tiny faces that you'll want to remove by removing one
of those close-by edges. Tiny faces you may find using

  ST_Area(ST_GetFaceGeometry(...))

> If someone could tell me how to delete "common" line segments I would be very happy.

You may find this article useful:

  https://strk.kbt.io/blog/2011/11/21/topology-cleaning-with-postgis/

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   https://strk.kbt.io/services.html