By David Crowther
Question:
How can you copy geometry from one record to another in SQL?
Answer:
Copying geometry from one record in your SQL database to another can be very useful, as it allows you to create geometry into existing records where you wish to keep the attributes, without having to create a completely new record.
This is the SQL to copy geometry from one record to another using an ID:
update YOURTABLE set GEOMETRY = (select geometry.STAsText() from YOURTABLE where ID = '1') where ID = '2'
In this example, we are copying the geometry from record ID 1 into record ID 2.
- YOURTABLE = your tablename
- GEOMETRY = the geometry field in your table
- ID = the unique ID of each record
This could also be very useful, if a user has incorrectly drawn geometry for a record and using this script the new records geometry could be corrected by simply copying the geometry of another existing record.
In this example, our Planning Team are drawing Planning Applications,.. and the new Polygon (shown in blue) has been badly drawn.
Instead of trying to manually correct the new Planning App feature, let’s use the same SQL update script to copy the geometry from the Planning App below (shown in green) that has been digitised correctly. In your GIS, identify the ID of the correctly drawn feature,.. here in our webGIS - MapThat - we have used the Edit Attributes tool to see the Plot Ref of the correct feature is 4222.
Now in SQL Management Studio (SSMS) we can use the following SQL to copy the geometry from the correct record (4222) into the incorrectly drawn feature (3256).
UPDATE lcc_planning_apps set ogr_geometry = (select ogr_geometry.STAsText() from lcc_planning_apps where Plot_Ref = '4222') where Plot_Ref = '3256'
Note – we are simply copying the geometry using Well Known Text (WKT)…. So, the new records geometry may not have a SRID set. So run the following SQL to update the new records geometry to define the SRID to be BNG (27700).
UPDATE lcc_planning_apps
SET ogr_geometry = geometry::STGeomFromWKB(ogr_geometry.STAsBinary(), 27700)
where Plot_Ref = '3256'
Finally, you may wish to check the geometry values from the copied and new record, to see that they are the same:
Select Plot_Ref, ogr_geometry.STAsText() from lcc_planning_apps where Plot_Ref IN ('4222','3256')
As per the above results we can see that the geometry from the new Plot record has been updated with the same geometry of the copied record.
Tip – I would suggest that the above steps are ran in Transaction mode…. This allows you to being a transaction, run the updates,… check the results…. And then if the results appear correct you can commit the changes and SQL saves the update!
Here is the SQL in one go…..
begin transaction
UPDATE lcc_planning_apps set ogr_geometry = (select ogr_geometry.STAsText() from lcc_planning_apps where Plot_Ref = '4222') where Plot_Ref = '3256'
UPDATE lcc_planning_apps
SET ogr_geometry = geometry::STGeomFromWKB(ogr_geometry.STAsBinary(), 27700)
where Plot_Ref = '3256'
select Plot_Ref, ogr_geometry.STAsText() from lcc_planning_apps where Plot_Ref IN ('4222','3256')
commit
If we now view the same data in our GIS, we can see that the new Plot/Planning App has been auto updated to the correct shape.