TransWikia.com

Checking if result of st_difference is empty

Geographic Information Systems Asked by pkExec on June 16, 2021

I am trying to select lines that completely touch a polygon (not at just a single point).

I would assume it would be a simple case of st_isempty(st_difference(geom_a,geom_b)).

However, this is not the case as you can see in the following picture.

enter image description here

I would like to only have the highlight yellow lines returned. What am I doing wrong?

(I could solve it with a string comparison, but I would like to avoid the extra "st_astext" invocation).

The query:

SELECT a.geometry, 
       a.id as a_id, 
       b.FID as b_id,
       st_astext(ST_GeometryN(st_difference(a.geometry,b.geometry),1)) as st_difference,
       st_isempty(ST_GeometryN(st_difference(a.geometry,b.geometry),1)) as st_isempty
FROM a
INNER JOIN b ON st_touches(a.geometry,b.geometry)

One Answer

For your issue I can suggest trying another approach with using the ST_IsValid() that is a boolean and works as T(1)/F(0). So, you query will look as

SELECT a.geometry, 
       a.id as a_id, 
       b.FID as b_id,
       st_astext(ST_GeometryN(st_difference(a.geometry, b.geometry), 1)) as st_difference,
       ST_IsValid(ST_GeometryN(st_difference(a.geometry, b.geometry), 1)) as st_isvalid
FROM a 
INNER JOIN b ON st_touches(a.geometry, b.geometry)

I can be wrong but your issue has probably something to do with a warning described in ST_IsEmpty()

Changed: 2.0.0 In prior versions of PostGIS ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed. This is now illegal in PostGIS 2.0.0 to better conform with SQL/MM standards

Correct answer by Taras on June 16, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP