ST_Equals,IsClosed,IsRing,IsSimple - geopaparazzi/Spatialite-Tasks-with-Sql-Scripts GitHub Wiki

Compare Geometries

--> 'List of Sql-Commands'

---

Original Documentation Spatialite-Functions SQL functions on type Curve [Linestring or Ring]

• ST_Equals( geom1 Geometry , geom2 Geometry ) : Integer

Original Documentation Spatialite-Functions SQL functions that test spatial relationships

• ST_IsClosed( c Curve ) : Integer
• ST_IsRing( c Curve ) : Integer

Original Documentation Spatialite-Functions SQL functions on type Geometry

• ST_IsSimple( geom Geometry ) : Integer

• ST_IsRing : is a combination of ST_IsClosed and ST_IsSimple

---

Scenario:

• we have a collection of LINESTRINGs that should create a valid POLYGON.
  • this however fails
• mostly this is caused by one of the LINESTRINGs not having a common Start/EndPoint with the next LINESTRING
• but it can also be caused
  • when the final LINESTRING is not:
    • closed
    • simple
      • a simple LINESTRING does NOT overlap itself
      • a complex LINESTRING does overlap itself (A self-intersecting (complex) line)

In this case shown here, I first had the Start/EndPoint problem and

• resolved this using 'ST_SetPoint'

SELECT
(SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423)) AS line_merge,
ST_IsSimple
(
 (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
) AS is_simple,
ST_IsClosed
(
 (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
) AS is_closed,
AsEWKT
(
 (
  SELECT ST_StartPoint
  (
   (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
  )
 )
) AS point_start,
AsEWKT
(
 (
  SELECT ST_EndPoint
  (
   (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
  )
 )
) AS point_end,
hex
(
 ST_AsBinary
 (
  (
   SELECT ST_StartPoint
   (
    (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
   )
  )
 )
) AS point_start_hex,
hex
(
 ST_AsBinary
 (
  (
   SELECT ST_EndPoint
   (
    (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
   )
  )
 )
) AS point_end_hex,
ST_Equals
(
 (
  SELECT ST_StartPoint
  (
   (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
  )
 ),
 (
  SELECT ST_EndPoint
  (
   (SELECT ST_LineMerge(soldner_segments) FROM berlin_geometries WHERE id_geometry IN (423))
  )
 )
) AS is_equal;

• line_merge returns the Geometry for Viewing
• is_simple returns FALSE (0)
• is_closed returns TRUE (1)
• is_equal returns TRUE (1)

BLOB sz=1536 GEOMETRY 0 1 SRID=3068;POINT(35426.75747266755 14052.76416914919) SRID=3068;POINT(35426.75747266755 14052.76416914919) 1

So the first problem was resolved and now I had to search the 35680.383315 meters long LINESTRING

• were I found this POINT about 33 cm over the LINESTRING:

  • 

• So after the needed correction (blue line), this problem was also resolved.

  • 

---

Sample based on 'using ST_LineMerge and ST_Intersection to rebuild 2 LINESTRINGs'

SELECT
ST_Equals
(
 ( -- original LINESTRING before splitting with Intersection POINT
  SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (652)
 ),
 ( -- split and merged LINESTRING with 'geometry_intersection' added as common POINT
  SELECT soldner_segment FROM berlin_ortsteil_segments WHERE id_segment IN (83)
 )
) AS is_equal;

returns FALSE (0) - as expected

• the POINT geometry_intersection has been added to the split LINESTRING

---

2014-04-08: Mark Johnson, Berlin Germany

---