• Blog posts

    Bio

    By David Crowther

    faq-SQL_Server_Spatial___Creating_Buffers-1.png

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

    faq-SQL_Server_Spatial___Creating_Buffers-2.png

    faq-SQL_Server_Spatial___Creating_Buffers-3.png

    By simply updating the SQL Select statement with a create buffer into New Table statement we were able to create a Buffer Table:

    faq-SQL_Server_Spatial___Creating_Buffers-4.PNG 

    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:

    faq-SQL_Server_Spatial___Creating_Buffers-5.png

    Shown with QGIS, the new Buffer table is 100 metres either side of the original boundary line:

    faq-SQL_Server_Spatial___Creating_Buffers-6.png

    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!