By David Crowther
Question:
How can you create Buffers using SQL?
Answer:
Recently Cadline delivered our SQL Server Spatial Training course and one of the most useful tasks we ran was to generate a New SQL Table by buffering the objects in an existing SQL Table.
For example, we started with this Leicester boundary table containing Polyline geometry where the CRS was BNG (27700).
By simply updating the SQL Select statement with a create buffer into New Table statement we were able to create a Buffer Table:
The above SQL statement buffered the original geometry (ogr_geometry) with a STBuffer command using a 100 metre buffer to create a new geometry field Ogr_geom_buffer into a New Table called - leicester_boundary_100.
Where the output was a Polygon record buffering the original polyline 100 metres either side:
Shown with QGIS, the new Buffer table is 100 metres either side of the original boundary line:
So using the STBuffer command allows you to create buffer objects, into new tables and …… if we were to use a Stored Procedure/Trigger we could also auto select features that Intersect or are Within these buffer objects and create even more new tables!