• Blog posts

    Bio

    By David Crowther

    SQL___Update_SRID_-_1.JPG

    Question:

    How can you update the projection (SRID) of the data in my SQL Table?

    Answer:

    If you store your spatial data in an SQL database, it will be good to learn several common SQL queries to manage that data correctly.

    If your source GIS data doesn’t have a Projection file this can cause issues for future analysis and display. For example, in QGIS if we open a layer that doesn’t have a projection, we can see the QUESTION MARK symbol in the Layers panel – this denotes the layer has an invalid projection.

    SQL___Update_SRID_-_2.JPG

    If we now import the data layer into a SQL database without assigning a correct Projection…

    SQL___Update_SRID_-_3.JPG

    …. in SQL we can now check the projection (SRID) of the imported table,  and we will find the records all return a ZERO Srid.

    select geom.STSrid from LBI_wards_2002_region

    SQL___Update_SRID_-_4.JPG

    To resolve this issue, you can run another SQL script that will update each record in the SQL table to set the SRID to be the required value.

    UPDATE tablename

    SET geometryfield = geometry::STGeomFromWKB(geometryfield.STAsBinary(), SRIDVALUE);

    ….for our data table this will be –

    UPDATE LBI_wards_2002_region

    SET geom = geometry::STGeomFromWKB(geom.STAsBinary(), 27700);

    Once the query has been run, the records in the table will now have their projection updated to be 27700 - which is British National Grid. Running an sql command we can check if this worked.

    select geom.STSrid from dbo.LBI_wards_2002_region

    SQL___Update_SRID_-_5.JPG

    If we now open the same layer into QGIS, the QUESTION MARK will no longer be there as the SQL table now has a valid projection (SRID).

    SQL___Update_SRID_-_6.JPG

    Tip! – ensure your Geometry_Columns metadata table in SQL has been updated with the correct SRID before opening the SQL table in QGIS.

    SQL___Update_SRID_-_7.JPG