SQL – Update Points using Polygons

By David Crowther

Question:

Is it possible to update a Point table with a value from the boundary that the points fall within?

Answer:

Yes, this can be done using a Database Trigger. If you are new to Triggers, then here are some previous FAQs where we have used triggers to update values in our SQL tables:

CASE WHEN - https://www.cadlinecommunity.co.uk/hc/en-us/articles/9965328743069-SQL-CASE-WHEN-on-Multiple-Fields 

In this example we are looking to update a Schools Point table (edubase) with the Ward Name where the School point is geographically within the Ward Boundary Polygon table (Wards).

In our Schools table (edubase) we have a new field ready to insert the values into called New_ward – and it currently has the name of the Ward that each existing School falls within.

The boundary Polygon table is called Wards and the field to retrieve the Ward Name values from is called ward.

In our GIS, the Schools Points table (edubase) has point objects that fall inside the Ward Boundary Polygon table (wards).

When we insert a new or move an existing School point, we want the New_ward field to auto update with the Ward Value from the Wards boundary table…. And we can do this using a Database Trigger.

Note – this trigger uses a Spatial Join (STIntersects) between the Point and Polygon table.

If you run the Create Trigger script, the School Points table (edubase) now has an associated Trigger…

…. and with the Trigger enabled, if we now insert a new School using the Drawing tools – Create Point.

Once the change is saved in your GIS, the new School record will be inserted into the source table (edubase), and the trigger will auto update the New_ward field retrieving the value from the Ward field in the Wards polygon table (Wards) – in this case inserting the value YEW TREE.

If we move the newly inserted School so that it falls in another Ward boundary…

… once the save is made…

…the School Points table (edubase) will again activate the trigger to auto update the New_Ward field with the new value – and here the New School features is now in Croxteth Ward.

Database Triggers are a great way to keep your data updated automatically. And in this instance, we have utilised the spatial JOIN between the Point and Polygon table, to retrieve our information.