2

I have two tables in PostGIS: afrivs, which is a polyline of various rivers, and weirs2 which is an empty polyline layer where potential dam sites will be added.

Obviously dams have to cross a river to work so I would like to add a constraint to enforce this. Following the examples here https://spatialdbadvisor.com/postgis_tips_tricks/127/how-to-apply-spatial-constraints-to-postgis-tables I tried adding the following constraint to check that any new dam polyline crosses a river polyline:

ALTER TABLE weirs2 ADD CONSTRAINT river_intersect 
CHECK (ST_Intersects(afrivs.geom,weirs2.geom)=true);

However this gives me the following error message.

ERROR:  missing FROM-clause entry for table "afrivs"

I also tried framing it as a SELECT query to include a FROM clause then get syntax error at or near "SELECT".

Based on this https://dba.stackexchange.com/questions/149466/missing-from-clause-entry-with-constraint-on-value-from-type-in-postgresql, I tried putting the table names in () but then get:

ERROR: column "afrivs" does not exist

What am I missing?

Using POSTGIS 2.4.4 r16526 on PostgreSQL 10.6.

  • 1
    Read the whole article. There is an example about making a constraint that is using two tables. Pay attention to The only way we can check that these centroids fall inside the geometry polygons in the related, parcel, table is by constructing a function and using it instead of ST_Covers in our CHECK constraint – user30184 May 9 at 7:29
  • 1
    see e.g. here (my own answer, with reference to a more detailed one); change SELECT NOT EXISTS ( ... to SELECT EXISTS ( ... and ST_Equals to ST_Intersects, remove the id check (and parameter) and refer to your river table in <table>. – ThingumaBob May 9 at 10:34
1

It worked! Thanks ThingumaBob.

For reference the code I used:

CREATE FUNCTION check_intersect(gm GEOMETRY)
RETURNS boolean AS $$
    SELECT EXISTS (
      SELECT 1
      FROM   afrivs AS tb
      WHERE  tb.geom && gm
        AND  ST_Intersects(tb.geom, gm));
  $$ LANGUAGE sql

ALTER TABLE weirs2 
  ADD CONSTRAINT check_intersection
  CHECK (check_intersect(geom))

For bonus points:

  • It seems inelegant to have to create a new function just to get two tables into one ST function - is there no easier way of doing this?
  • Do I need SELECT EXISTS or would SELECT 1 also work?

  • is there any way of having the afrivs table as an argument rather than hard-coding it into the function?

  • what is the point of the WHERE tb.geom && gm line - is this just to check that they are both non-null?

Cheers,

  • well, there is no 'simpler' way, and compared to triggers (that do offer plenty more options) often used, the elegancy of this did make me happy when I read about custom funcs in constraints. the EXISTS is special in that it stops execution as soon as it encounters the first 1/true, SELECT 1 scans the whole filtered range of rows. you can add a REGCLASS parameter to refer a relation (e.g. table) with inherent catalog/search path checks and thus security (although others types are possible). the && is a bounding box comparator, used here to limit actual ST_Intersection operations. – ThingumaBob May 9 at 22:33

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.