• Blog posts

    Bio

    By David Crowther

    SQL___Update_Points_using_Polygons_-_1.png

    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/9857536785437-SQL-CASE-WHEN-Update 
    CASE WHEN ON INSERT - https://www.cadlinecommunity.co.uk/hc/en-us/articles/9964559589533-SQL-CASE-WHEN-on-INSERT-Only 
    CASE WHEN MULTIPLE FIELDS - 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.

    SQL___Update_Points_using_Polygons_-_2.png

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

    SQL___Update_Points_using_Polygons_-_3.png

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

    SQL___Update_Points_using_Polygons_-_4.png

    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.

    SQL___Update_Points_using_Polygons_-_5.png

    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…

    SQL___Update_Points_using_Polygons_-_11.png

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

    SQL___Update_Points_using_Polygons_-_6.png

    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.

    SQL___Update_Points_using_Polygons_-_7.png

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

    SQL___Update_Points_using_Polygons_-_8.png

    … once the save is made…

    SQL___Update_Points_using_Polygons_-_9.png

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

    SQL___Update_Points_using_Polygons_-_10.png

    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.

Comments

0 comments

Please sign in to leave a comment.