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
INNER JOIN b ON st_touches(a.geometry,b.geometry)
  • What if you apply ST_NumPoints() with CASE-Statement, e.g. CASE WHEN ST_NumPoints(ST_GeometryN(st_difference(a.geometry,b.geometry),1)) = 0 THEN 1 ELSE 0 END. Can it suit your solution?
    – Taras
    Aug 20 '20 at 14:17
  • 2
    Alternatively you may try ST_IsValid(), i.e. ST_IsValid(ST_GeometryN(st_difference(a.geometry,b.geometry),1))
    – Taras
    Aug 20 '20 at 14:20
  • 1
    @Taras, ST_IsValid indeed provides the correct results. Do you mind posting your comment as an answer so that I can upvote it? And do you know why st_isempty doesn't work as expected?
    – pkExec
    Aug 20 '20 at 14:41
  • Hard to say what's going on without seeing the data. Can you post a polygon and two lines with different results?
    – dr_jts
    Aug 20 '20 at 18:32
  • @dr_jts I don't think data is the issue. st_difference returns empty geometry on the proper rows (as per the documentation), but st_isempty doesn't report "true" as it should (as per the documentation)
    – pkExec
    Aug 20 '20 at 19:30

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
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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