1

I have a PostgreSQL table with few hundred linestrings. Many of them are connecting to each other, making up only 8 separate lines. I would like to join them together but with a tolerance at the ends. The attributes doesn't really matter, the goal is to get connected geometries.

My problem is that the original geometries doesn't accurately connect to each other sometimes. There's a few cm difference which doesn't really bother me.

Is there any function like ST_LineMerge that works with a tolerance? The best would be if it would take the line ends within a tolerance and would merge them in their averaged coordinates.

I've tried ST_QuantizeCoordinates and ST_SnapToGrid. The former messes up the whole geometry. The snapping would work, but I accidentaly found a place where it does not work properly for me.

Here's an example: Two endpoints of two line in EPSG:7912 are:

  • 15.329918361392119 47.16481125180434
  • 15.329917965259387 47.164811420123094

If I transform them to my goal reference system (EPSG:32633), I will get:

  • 525004.9901605401 5223532.044065407
  • 525004.9600581075 5223532.062643801

The distance between them is 3.5 cm. If I apply ST_SnapToGrid with a 5 cm tolerance I will get this:

  • 525005 5223532.050000001
  • 525004.9500000001 5223532.050000001

I don't really understand what is that 0.0000000001 in the coordinates.

Example SQL:

SELECT ST_AsText(ST_SnapToGrid(ST_Transform( ST_SetSRID(ST_GeomFromText('POINT(15.329918361392119 47.16481125180434)'),7912),32633),0.05))
--"POINT(525005 5223532.050000001)"
SELECT ST_AsText(ST_SnapToGrid(ST_Transform( ST_SetSRID(ST_GeomFromText('POINT(15.329917965259387 47.164811420123094)'),7912),32633),0.05))
--"POINT(525004.9500000001 5223532.050000001)"
SELECT ST_Distance(ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(15.329917965259387 47.164811420123094)'),7912),32633),ST_Transform(ST_SetSRID(ST_GeomFromText('POINT(15.329918361392119 47.16481125180434)'),7912),32633))
--0.03537390539852737

SELECT ST_AsText(ST_Transform( ST_SetSRID(ST_GeomFromText('POINT(15.329918361392119 47.16481125180434)'),7912),32633))
--"POINT(525004.9901605401 5223532.044065407)"
SELECT ST_AsText(ST_Transform( ST_SetSRID(ST_GeomFromText('POINT(15.329917965259387 47.164811420123094)'),7912),32633))
--"POINT(525004.9600581075 5223532.062643801)"
Improve this question
5
  • 1
    IEEE floating point representation cannot store every possible value. There will be rounding to the value it can represent.
    – Vince
    Commented Jan 19, 2023 at 12:22
  • 1
    Have a look at ST_ClusterDBSCAN with a small tolerance to regroup nearby lines. You can then union the output to a multiline, but it won't "fix" the endpoints location
    – JGH
    Commented Jan 19, 2023 at 13:45
  • I forgot to mention that I would like the output to be simple linestrings. Otherwise it would work I think.
    – 588chm
    Commented Jan 19, 2023 at 14:03
  • 1
    There are a couple of options to solve this. If you happen to have measured routes that are split into sections, and the m values are contiguous (they don't repeat), you can use st_clusterwithin to group the lines, then explode to points, then rebuild ordering by m. You can also use postgis topology for this. Here's an example using topology blog.mathieu-leplatre.info/…
    – jbalk
    Commented Jan 21, 2023 at 0:45
  • Thank you! I will try this one, but seems like a solution to me.
    – 588chm
    Commented Jan 23, 2023 at 6:29

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.