What could be the cause(es) of a ST_Collect(LINESTRING) not creating a valid POLYGON - geopaparazzi/Spatialite-Tasks-with-Sql-Scripts GitHub Wiki

What could be the cause(es) of a ST_Collect(LINESTRING) not creating a valid POLYGON?

--> 'List of Sql-Commands'

--> 'List of Themes'

  • to understand why the creation of a POLYGON from LINESTRINGs fail
    • it is important to fully understand what a valid POLYGON is considered to be

Some of these conditions are:

  • the ExternalRing of a POLYGON must be
    • closed
      • the Start/EndPoint MUST be the same
    • the LINESTRING of the ExternalRing
      • must NOT overlap itself
        • if double LINESTRINGs exist
          • they overlap themselves
  • for possible InternalRings of a POLYGON
    • the same rules apply
      • not only with themselves, but also with the ExternalRing

With these facts in mind, we will attempt to resolve this with existing commands

  • overlapping
  • not closed
    • this can be checked with
    • ST_LineMerge(ST_Collect(soldner_segment))
      • is here your friend

Problem 01:

In this case the Geometry looks correct, no missing links to be seen

  • the MULTILINESTRING in spatialite_gui shows 12 LINESTRINGs
SELECT name,ST_LineMerge(ST_Collect(soldner_segments)) AS merged_multilinestring
FROM berlin_geometries WHERE id_geometry IN (213);

spatialite_gui shows

SRID: 3068
Geometry type: MULTILINESTRING
#3 LINESTRINGs:
  1) 55 vertices
  2) 55 vertices
  3) 193 vertices

Here the result is:

  • MULTILINESTRING

    • with 3 LINESTRINGs

      • I used copy and paste (NOT the copy button) to copy the EWKT text into an editor
        • this returns each POINT in one line (as apposed to the copy button which returns one line only)
      • there I confirmed, that for whatever reason, the same LINESTRING was being read in twice
        • 2 x 55 POINTS

So the problem is to resolve the cause of the duplicate LINESTRING.

So with this (for this Database specific) Query, I looked at the collected linestring_segments:

  • 3000-01-01 : unknown future Date (present day borders)
SELECT *
FROM berlin_ortsteil_segments AS segments INNER JOIN
berlin_geometries AS geometries
WHERE
(
 ('213'=geometries.id_geometry) AND
 (
  ('1920-10-01' BETWEEN segments.valid_since AND segments.valid_until) AND
  (segments.belongs_to_01||','||segments.belongs_to_02 LIKE '%'||geometries.id_admin||'%')
 )
);

I quickly saw that I have forgotten to remove a LINESTRING, that had been spitted into smaller portions.

  • I invalidated the record, before deciding to delete it
UPDATE berlin_ortsteil_segments
SET
 -- save the previous value, in case needed later
 comment=valid_since||'-'||valid_until,
 -- set impossible value to valid_since and valid_until
 valid_since='3000-01-01',valid_until='3000-01-01'
WHERE id_segment = 67;

and rebuild the final table (where all possible combinations are build)

  • date-range of 3000-01-01 not being one of them

There I determined that this POLYGON (of 194) was build correctly

  • only 51 more to go ...

Problem 02:

  • this is the situation I was expecting when starting this page
    • the Problem 01 turned up instead, so I did that first
    • this is the more common situation, with the most often used resolution

2014-04-01: Mark Johnson, Berlin Germany