I'm trying to write a trigger that should prevent saving lines which do not end at building boundaries.

( A PostGIS-Solution for something like the topology-rule "Must be covered by boundary of" in this pdf: http://help.arcgis.com/en/arcgisdesktop/10.0/help/001t/pdf/topology_rules_poster.pdf but for the endpoints of a linelayer instead of a pointlayer. )

Therefore I check if the endpoints intersect the polygon outlines. The trigger does not allow me to save lines at all. If I deactivate the trigger and check the spatial relation in PostGIS I can see that the endpoint intersects the buildings layer:

enter image description here

I tried to check the intersection with a buildings-polygon layer as well as with a linestring layer which contained only the boundaries: enter image description here

In both cases the trigger did not allow me to save any lines. The new lines were digitized in QGIS while snapping was activated.

    CREATE TABLE public.gebaeude 
  gid serial PRIMARY KEY,
  id numeric,
  geom geometry(LineString,25832)

CREATE TABLE ha_linien (
 gid serial PRIMARY KEY,
 name varchar(50),
 geom geometry(Linestring, 25832)

-- Procedure
RETURNS trigger AS
-- also tried IF NOT EXISTS instead of IF count >0
IF (SELECT COUNT(*) FROM (SELECT gid FROM gebaeude AS g WHERE st_intersects(ST_ENDPOINT(NEW.geom), g.geom)) AS foo) <1

        RAISE Exception 'Line does not end at a building ';
        --RETURN NULL;
    END IF;

LANGUAGE 'plpgsql';

-- Trigger
CREATE TRIGGER check_ha_lines_trigger


Postgres-Version: "PostgreSQL 9.5.13 on x86_64-pc-linux-gnu (Debian 9.5.13-2.pgdg70+1), compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit"
Postgis-Version: "2.3 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
QGIS-Version: 2.18.22

Example data:

example building:
LINESTRING (399622.008752193 5285661.23504165,399622.688532861 5285665.11365552,399620.989393588 5285665.41356583,399621.429250751 5285667.90267557,399623.048431379 5285667.62276057,399623.528278345 5285670.39177304,399622.288905144 5285670.6117046,399622.668781862 5285672.74094476,399633.933086617 5285670.76156211,399631.953705164 5285659.4855414,399629.784806594 5285659.87543057,399629.614859662 5285658.88578142,399626.796290549 5285659.37564352,399626.976232006 5285660.36529143,399622.008752193 5285661.23504165)

example lines (all digitized in QGIS while snapping was activated):

LINESTRING (399621.303441253 5285653.56324476,399624.812305698 5285660.74417075)
LINESTRING (399628.915893198 5285653.38306838,399627.609614462 5285655.6352731,399628.645628632 5285657.16677231,399628.382547276 5285659.09995503)
LINESTRING (399635.942771917 5285656.85146365,399636.573389238 5285658.7883597,399640.402137258 5285662.61710772,399636.122948295 5285662.39188725,399633.6905672 5285659.32888884,399632.218495646 5285660.99398382)
LINESTRING (399640.137503205 5285666.53031342,399633.187016432 5285666.51139454)
LINESTRING (399643.808596896 5285664.16549847,399639.394275648 5285664.61593941,399638.178085101 5285664.05288823,399636.218666996 5285664.90872603,399634.777255976 5285663.87271186,399632.924807673 5285665.01765952,399632.831270152 5285664.48480062)
LINESTRING (399636.269341602 5285663.28713863,399632.610478752 5285663.22700951)

-- and as wkb:





  • Add one building outline and one line that should intersect as WKT. – user30184 Sep 6 '18 at 8:20
  • Just added some test data above – markgraeflerland Sep 6 '18 at 8:43
  • 1
    have you tried st_touches rather than intersects? – Ian Turton Sep 6 '18 at 8:48
  • I have tried st_touches but that also does not work. would not every point that touches a line also intersect? – markgraeflerland Sep 6 '18 at 9:00
  • Can you share the data as WKB? If you snap a point to a line, the endpoint will almost never fall exactly on the line unless the intersection point is also added as a point on the line. – dbaston Sep 6 '18 at 12:04

The trigger is throwing an error because the endpoints of the snapped lines do not intersect the building. A point-to-line snap operation returns the closest point to the line that can be represented as a double-precision floating point. Since the exact intersection point is often not representable, the endpoint of the snapped line will not intersect the building. The magical "magnify topology" tool in JTS TestBuilder makes this clear:

enter image description here

To resolve this, you could update the trigger to use a distance calculation instead of an intersection test. Or, if maintaining the correct topological relationships is important, you could add the snapped point to the building outline itself.

  • 1
    Not OP, but wait, you're saying that snapping in QGIS doesn't actually make lines intersect with each other... is there any way to make sure they do? Never realised this... – she_weeds Sep 6 '18 at 21:03
  • 2
    @she_weeds It can't make a point intersect with a line exactly, no. It's not a limitation of QGIS, it's a limitation of floating point numbers. – dbaston Sep 6 '18 at 21:26
  • strange that a snapping is not a "real" snapping but thanks for the explanation and pointing me to the "magnify topology" which is great – markgraeflerland Sep 11 '18 at 10:19

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.