0

Context

I initially had a PostGIS problem when it comes to closing a globally V-shaped multi-line (having some local concavities) into a polygon. See here: Closing a concave V-shaped MultiLineString in PostGIS to form a Polygon

The current post is a follow-up in order to keep things clearly separated.

Case when the line segments are mathematically perfectly connected

If each of the line segments are mathematically perfectly connected (i.e. sharing the exact same nodes), everything works fine by simply using ST_MakeLine() and ST_MakePolygon() on top, as suggested.

So with the help of the following code, I can transform my line into a polygon:

WITH cte AS (
SELECT
   ST_LineMerge(
        ST_GeomFromText(
           'MultiLineStringZ (
            (2684034.0710075371 1247842.7629618330 461,
             2684025.6499116043 1247832.1257880242 463),
            (2684025.6499116043 1247832.1257880242 463,
             2684006.9715409675 1247823.8946416254 465),
            (2684006.9715409675 1247823.8946416254 465,
             2684035.2107047677 1247820.9187656295 464),
            (2684035.2107047677 1247820.9187656295 464,
             2684051.0398324989 1247815.2202796528 462)
            )', 2056)
  ) AS line
)
SELECT 
  ST_MakePolygon( ST_MakeLine(line, ST_PointN(line, 1)) ) AS geom
FROM cte;

from multiline to polygon

Case when the line segments are not mathematically perfectly connected

But if, for some reason, some of the nodes shared between two line segments are not exactly the same (due to numerical inaccuracies), this solution naturally fails, returning a Null geometry. (At that point we are at least two people hoping for a tolerance parameter in such function, but for the moment it doesn't exist):

WITH cte AS (
SELECT
   ST_LineMerge(
        ST_GeomFromText(
           'MultiLineStringZ (
            (2684034.0710075371 1247842.7629618330 461,
             2684025.6499116043 1247832.1257880242 463),
            (2684025.6499116053 1247832.1257880242 463,
             2684006.9715409675 1247823.8946416254 465),
            (2684006.9715409675 1247823.8946416254 465,
             2684035.2107047677 1247820.9187656295 464),
            (2684035.2107047677 1247820.9187656295 464,
             2684051.0398324989 1247815.2202796528 462)
            )', 2056)
  ) AS line
)
SELECT 
  ST_MakePolygon( ST_MakeLine(line, ST_PointN(line, 1)) ) AS geom
FROM cte;

I only replaced the two ending digit 43 of the first coordinate of the third line, by 53 and the result is Null.

In order to fix this issue, a proposed idea was to sew the lines segments, by adding extra addition microscopic lines in order to be able to call ST_MakeLine() without issue:

WITH cte AS (
SELECT
    (ST_Dump(
        ST_GeomFromText(
            'MultiLineStringZ (
                (2684034.0710075371 1247842.7629618330 461,
                 2684025.6499116043 1247832.1257880242 463),
                (2684025.6499116053 1247832.1257880242 463,
                 2684006.9715409675 1247823.8946416254 465),
                (2684006.9715409675 1247823.8946416254 465,
                 2684035.2107047677 1247820.9187656295 464),
                (2684035.2107047677 1247820.9187656295 464,
                 2684051.0398324989 1247815.2202796528 462)
            )', 2056)
    )).geom AS line
)
SELECT
  St_MakePolygon(
    ST_MakeLine(
      ST_MakeLine(line),
      ST_PointN( ST_MakeLine(line), 1 )
    )
  ) AS geom FROM cte;

But I don't really like the triple call to ST_MakeLine in the last SELECT statement.

Question

Is there a way to simplify this query?

What I tried and noticed

I was hoping to directly call ST_MakeLine() on top of (ST_Dump(...)).geom in the CTE, but it's not possible:

ERROR:  aggregate function calls cannot contain set-returning function calls
LINE 4:     (ST_Dump(
             ^
HINT:  You might be able to move the set-returning function into a LATERAL FROM item.
SQL state: 0A000
Character: 41

I also notice a few things if I dump the actual polygon to text:

A. in the case where the line segments are perfectly connected:

"POLYGON Z ((
2684034.0710075370 1247842.7629618330 461,
2684025.6499116044 1247832.1257880242 463,
2684006.9715409675 1247823.8946416255 465,
2684035.2107047676 1247820.9187656294 464,
2684051.0398324990 1247815.2202796529 462,
2684034.0710075370 1247842.7629618330 461
))"

B. in the case where the line segments are not perfectly connected:

"POLYGON Z ((
2684034.0710075370 1247842.7629618330 461,
2684025.6499116044 1247832.1257880242 463,
2684025.6499116053 1247832.1257880242 463,
2684006.9715409675 1247823.8946416255 465,
2684035.2107047676 1247820.9187656294 464,
2684051.0398324990 1247815.2202796529 462,
2684034.0710075370 1247842.7629618330 461
))"

(I appended trailing zeroes to keep things visually aligned in both cases).

  • Coordinates of each points are not exactly the same as the ones of the input feature, but we are speaking of the 10th decimal so I guess it's OK. But still, I noticed that.
  • In the case B, it effectively worked as expected by adding a microscopic line segment defined by these two points:
2684025.6499116044 1247832.1257880242 463,
2684025.6499116053 1247832.1257880242 463,

(where 2684025.6499116044 was 2684025.6499116043 in the input feature)

This is great. But as the altitude is naturally kept as is, having some perfectly horizontal lines may be the cause of some troubles in some applications (e.g. hydraulics).

Version info

 PostgreSQL 15rc1 (Debian 15~rc1-1.pgdg110+1) on x86_64-pc-linux-gnu,
    compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

POSTGIS="3.4.0dev 3.3.0rc2-148-gb8d78a0dc" [EXTENSION]
   PGSQL="150"
   GEOS="3.12.0dev-CAPI-1.18.0"
   PROJ="9.2.0"
   LIBXML="2.9.10"
   LIBJSON="0.15"
   LIBPROTOBUF="1.3.3"
   WAGYU="0.5.0 (Internal)" TOPOLOGY

Using (at the time of writing):

docker pull postgis/postgis:15rc1-master
swiss_knight
  • 10,309
  • 9
  • 45
  • 117

1 Answers1

0

You can move st_dump to a subquery:

WITH cte AS (   
    SELECT
    st_makeline(geom) as line
    FROM (
    select
    (ST_Dump(
        ST_GeomFromText(
            'MultiLineStringZ (
                (2684034.0710075371 1247842.7629618330 461,
                 2684025.6499116043 1247832.1257880242 463),
                (2684025.6499116053 1247832.1257880242 463,
                 2684006.9715409675 1247823.8946416254 465),
                (2684006.9715409675 1247823.8946416254 465,
                 2684035.2107047677 1247820.9187656295 464),
                (2684035.2107047677 1247820.9187656295 464,
                 2684051.0398324989 1247815.2202796528 462)
            )', 2056)
    )).geom) AS line
    )
    SELECT 
  ST_MakePolygon( ST_MakeLine(line, ST_PointN(line, 1)) ) AS geom
FROM cte;

enter image description here

JGH
  • 41,794
  • 3
  • 43
  • 89