2

I have a TIN record in a PostGIS database. It contains between 50'000 and 100'000 triangles:

TIN terrain with many triangles

Based on this answer: https://gis.stackexchange.com/a/209845/65370 I'm trying for the moment to dump the triangles to a MultiPolygonZ, but this query has now run for 4h 8h >24h and it is not over yet:

SELECT ST_Transform(ST_Collect(geom), 4326) AS geom 
FROM (
  SELECT
    ST_MakePolygon(
      ST_ExteriorRing(
        (ST_Dump(ST_Tesselate(tin))).geom
      )
    ) AS geom
  FROM mytable WHERE id=1
) sq;

Is there something I can do to accelerate things?
I cannot figure out if it's possible to build an index on a TIN column for example.

Info:

postgresql version();
"PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit"

postgis_full_version();
"POSTGIS=""3.1.4 ded6c34"" [EXTENSION] PGSQL=""140"" GEOS=""3.9.0-CAPI-1.16.2"" SFCGAL=""1.3.8"" PROJ=""7.2.1"" LIBXML=""2.9.10"" LIBJSON=""0.15"" LIBPROTOBUF=""1.3.3"" WAGYU=""0.5.0 (Internal)"" TOPOLOGY"

postgis_sfcgal_version();
"1.3.8"

OS: Ubuntu 22.04 64 bits.

1
  • 1
    Hi, I've never used the Tin management functions of PostGis, but it seems that the query doesn't do exactly what you want. It starts by building a TIN (ST_Tesselate) before dumping when you indicate you already have a Tin. In my opinion you just need to dump the Tin. This is explained in the examples in the PostGIS documentation (cf. last example)
    – Atm
    Apr 26, 2022 at 9:19

1 Answer 1

0

Atm was actually right in his comment. And indeed, it's actually a nonsense to tessellate a TIN!
It was an error on my side.

Here is a detailed comparison for a same query with and without the use of ST_Tesselate().

I ticked all the options within pgAdmin4 (6.8.0) before running EXPLAIN ANALYSE in both cases:

selected options of "EXPLAIN ANALYSE" within pgadmin4

The original query, which is not the one shown here as an example and which wasn't terminated after 48h with the usage of ST_Tesselate() only took ~400ms to finish without ST_Tesselate().

Query plan execution analysis (EXPLAIN ANALYZE) with ST_Tesselate()

Executed query

SELECT (ST_Transform(ST_Collect(geom), 4326)) AS geom 
FROM (
  SELECT
    ST_MakePolygon(
      ST_ExteriorRing(
        (ST_Dump(ST_Tesselate(tin))).geom
      )
    ) AS geom
  FROM mytable WHERE id=1
) sq;

Raw results

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Plain",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 49184.77,
      "Total Cost": 49209.78,
      "Plan Rows": 1,
      "Plan Width": 32,
      "Actual Startup Time": 2214.623,
      "Actual Total Time": 2214.624,
      "Actual Rows": 1,
      "Actual Loops": 1,
      "Output": [
        "st_transform(
           st_collect(
             (st_makepolygon(
               st_exteriorring(
                 ((st_dump(st_tesselate(mytable.tin)))).geom
               )
             )
           )
         ),
         4326)"
      ],
      "Shared Hit Blocks": 15617,
      "Shared Read Blocks": 29296,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Result",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Startup Cost": 0,
          "Total Cost": 49183.38,
          "Plan Rows": 1000,
          "Plan Width": 32,
          "Actual Startup Time": 2145.842,
          "Actual Total Time": 2213.024,
          "Actual Rows": 297,
          "Actual Loops": 1,
          "Output": [
            "st_makepolygon(
               st_exteriorring(
                 ((st_dump(st_tesselate(mytable.tin)))).geom
               )
             )"
          ],
          "Shared Hit Blocks": 15617,
          "Shared Read Blocks": 29296,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0,
          "Plans": [
            {
              "Node Type": "ProjectSet",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Startup Cost": 0,
              "Total Cost": 47423.38,
              "Plan Rows": 1000,
              "Plan Width": 32,
              "Actual Startup Time": 2145.835,
              "Actual Total Time": 2212.838,
              "Actual Rows": 297,
              "Actual Loops": 1,
              "Output": [
                "st_dump(st_tesselate(mytable.tin))"
              ],
              "Shared Hit Blocks": 15617,
              "Shared Read Blocks": 29296,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0,
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Relation Name": "mytable",
                  "Schema": "public",
                  "Alias": "mytable",
                  "Startup Cost": 0,
                  "Total Cost": 47416.88,
                  "Plan Rows": 1,
                  "Plan Width": 30928,
                  "Actual Startup Time": 3.761,
                  "Actual Total Time": 70.397,
                  "Actual Rows": 1,
                  "Actual Loops": 1,
                  "Output": [
                    "mytable.tin"
                  ],
                  "Filter": "(mytable.id = 1 )",
                  "Rows Removed by Filter": 200428,
                  "Shared Hit Blocks": 15612,
                  "Shared Read Blocks": 29296,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                }
              ]
            }
          ]
        }
      ]
    },
    "Settings": {
      "search_path": "\"$user\", public, topology"
    },
    "Planning": {
      "Shared Hit Blocks": 0,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 0.062,
    "Triggers": [],
    "Execution Time": 2214.655
  }
]

Visual results

visual results with st_tesselate

Query plan execution analysis (EXPLAIN ANALYZE) without ST_Tesselate():

Executed query

SELECT (ST_Transform(ST_Collect(geom), 4326)) AS geom 
FROM (
  SELECT
    ST_MakePolygon(
      ST_ExteriorRing(
        (ST_Dump((tin))).geom
      )
    ) AS geom
  FROM mytable WHERE id=1
) sq;

Raw results

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Plain",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 48934.52,
      "Total Cost": 48959.53,
      "Plan Rows": 1,
      "Plan Width": 32,
      "Actual Startup Time": 60.759,
      "Actual Total Time": 60.76,
      "Actual Rows": 1,
      "Actual Loops": 1,
      "Output": [
        "st_transform(
           st_collect(
             (st_makepolygon(
               st_exteriorring(
                 ((st_dump(mytable.tin))).geom
               )
             ))
           ),
           4326)"
      ],
      "Shared Hit Blocks": 16160,
      "Shared Read Blocks": 28753,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Result",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Startup Cost": 0,
          "Total Cost": 48933.13,
          "Plan Rows": 1000,
          "Plan Width": 32,
          "Actual Startup Time": 4.005,
          "Actual Total Time": 59.691,
          "Actual Rows": 297,
          "Actual Loops": 1,
          "Output": [
            "st_makepolygon(
               st_exteriorring(
                 ((st_dump(mytable.tin))).geom
               )
            )"
          ],
          "Shared Hit Blocks": 16160,
          "Shared Read Blocks": 28753,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0,
          "Plans": [
            {
              "Node Type": "ProjectSet",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Startup Cost": 0,
              "Total Cost": 47423.13,
              "Plan Rows": 1000,
              "Plan Width": 32,
              "Actual Startup Time": 4,
              "Actual Total Time": 59.553,
              "Actual Rows": 297,
              "Actual Loops": 1,
              "Output": [
                "st_dump(mytable.tin)"
              ],
              "Shared Hit Blocks": 16160,
              "Shared Read Blocks": 28753,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0,
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Async Capable": false,
                  "Relation Name": "mytable",
                  "Schema": "public",
                  "Alias": "mytable",
                  "Startup Cost": 0,
                  "Total Cost": 47416.88,
                  "Plan Rows": 1,
                  "Plan Width": 30928,
                  "Actual Startup Time": 3.919,
                  "Actual Total Time": 59.222,
                  "Actual Rows": 1,
                  "Actual Loops": 1,
                  "Output": [
                    "mytable.tin"
                  ],
                  "Filter": "(mytable.id = 1 )",
                  "Rows Removed by Filter": 200428,
                  "Shared Hit Blocks": 16155,
                  "Shared Read Blocks": 28753,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                }
              ]
            }
          ]
        }
      ]
    },
    "Settings": {
      "search_path": "\"$user\", public, topology"
    },
    "Planning": {
      "Shared Hit Blocks": 0,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 0.055,
    "Triggers": [],
    "Execution Time": 60.785
  }
]

Visual results

visual results without st_tesselate

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.