Partager via

SQL 2008 Spatial Samples, Part 7 of 9 - STBuffer ... Spatial starts here.

  • Article

This post covers only 2 methods: STBuffer & BufferWithTolerance. They are so handy, they deserve a post all to themselves.

Note: Unless otherwise stated. All code samples are designed to be Cut n Pasted directly into SQL Server Management Studio (SSMS) & run. Sometimes I've added extra detail than what is in the sample picture.

Method .STBuffer(distance )

Enables you to "expand" or "contract" a shape.

Works with both Geometry &  Geography objects.

.STBuffer is an extremely powerful & hence very handy method, but don’t use it “by Default”, look for alternatives & use it if there are none.

Why? It is a CPU resource intensive function.

        (Don't get me wrong, STBuffer is efficient for what it does, but what it does is not trivial.)

Example Use:

It is handy to answer questions like “Give me a list of homes within 500 metres of the railway line.” You get the path for the Rail Line. Create a 500m buffer on the path & return all points contained within the buffer.

You could also use it to answer the question “Give me the homes within 3km radius of this point”. But, mathematically speaking, that is a much easier problem to solve. So it is much more efficient to use STDistance & find all points within that Distance. This approach will use an index if one exists for a much faster result.

Alternative 1: Could create a buffer (size=X) from a Point & see what points are contained (STWithin) in that buffer.

A Better Alternative: Finds all points <= X distance from that point.

Of course everything is relative, in the examples in this blog, I'm only using a couple of shapes with a few points. Typically you may have a few thousand shapes with many hundreds of points each. That is when you start to see a difference.

 

 -- ===< Sample: Creating a buffer around a Line >===
DECLARE @h AS GEOMETRY =  geometry::STGeomFromText('LINESTRING(67 36,68 37,69 38,73 38,75 37,
                                    76 35,75 33,74 32,71 32,69 33,58 39,55 40,52 40,50 39,48 36,
                                    48 34,50 31,52 30,56 30,60 31,62 33)', 0);

SELECT @h as 'Geo', 'Line' AS 'Labels',@h.STAsText() as 'Text'
UNION ALL
SELECT @h.STBuffer(1.5), 'STBuffer(1.5)', @h.STBuffer(1.5).STAsText()

STBuffer

The purple line was the original LineString, the blue polygon is the Buffer.

 

Tip: .STBuffer values can be negative when using Polygons

  • A negative buffer removes all points within the given distance of the boundary of the geometry instance.
  • Combine with other spatial methods like STDisjoint & STIntersect to find objects within N distance inside a shape perimeter.
 -- ==< Sample: Finding a Perimeter with a negative buffer value >==
DECLARE @Zone as GEOMETRY = 'POLYGON((50 0, 60 30, 80 20, 70 40, 100 50, 70 60, 80 80, 60 70,
                             50 100, 40 70, 20 80, 30 60, 0 50, 30 40, 20 20, 40 30, 50 0 ))'
DECLARE @Perim as GEOMETRY = @Zone.STSymDifference(@Zone.STBuffer(-4))
SELECT @Perim as Perimeter    
-- Then use STIntersects against a set of Points to find which points are in the Perimeter.

Tip: Using STBuffer with SQL Server Management Studio (SSMS)

  • If you are using SSMS to look at POINT data, try putting a Buffer around them. It makes it easier to see them.
  • Do NOT use this approach in your application. Much more efficient to draw a marker (icon) at that point.
 -- ==< Sample: Using STBuffer to see Points easier in SSMS Spatial View Tab >===
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('MULTIPOINT( (1 0.5), (5 3),(2 2), (4 1), (4 5), (2 3), (1 2))',0);

SELECT @g.STBuffer(0.1) as [Geo], '@g'AS [Label], @g.ToString() as [WKT]

STBufferPoints

Easier to see points as small circles in SSMS Spatial results Tab

 

Tip: The buffer values you use for geography are typically much larger (or much smaller) than what we use for geometry.

  • Why? Typically in geometry, your scale goes from 0 to 100, maybe 1000. Whereas often in geography you map might include an entire continent, often 10,000's of Km across. So the buffer around your points may need to be as big as Scotland, just so you can see them.

  • So while .STBuffer(0.2) may work well for to display points in a Geometry result. Perhaps you may need .STBuffer(2000000) just to see a geography point on a map of the world.

  • Warning: Be aware that Latitude must remain between -90 & +90 degrees. If your point is near the poles & you draw a circle around it, a port of that circle may exceed +/-90 Latitude. If it does you will get a .NET Framework error

    Msg 6522, Level 16, State 1, Line 23

    A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":

    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.

     

Method .BufferWithTolerance(distance , tolerance , relative)

  • This lets you trade off, accuracy for overhead. Usually less CPU expensive than .STBuffer. Like STBuffer it still enables you to "expand" or "contract" a shape but you can ask it to be less precise. And thus less demanding on your CPU.

    Works with Geometry objects only. It is not a method of Geography Objects.

    While distance is the same, tolerance is how much variation between what it returns & "perfect".

BufferWithTolerance 
The Brown is the BufferwithTolerance & the Blue are what you get with STBuffer. Actually I had to set parameters to be really inaccurate in order to show you the difference.

Another example showing both Negative Buffer values & BufferWithTolerance.

 -- ==< Sample: Setting a negative Buffer value >===
DECLARE @p as GEOMETRY = 'POLYGON((50 0, 60 30, 80 20, 70 40, 100 50, 70 60, 80 80, 60 70,
                         50 100, 40 70, 20 80, 30 60, 0 50, 30 40, 20 20, 40 30, 50 0 ))'
SELECT @p as geom
UNION ALL
SELECT @p.BufferWithTolerance(-2,2,0)  /* 0, tolerance (2nd parm) is the absolute maximum variation in the
                                          ideal buffer distance for the returned linear approximation. */
UNION ALL
SELECT @p.BufferWithTolerance(-9,3,1) /* 1, tolerance is relative. Product of the tolerance parameter 
                                            & the diameter of the bounding box */
UNION ALL
SELECT @p.STBuffer(-14)
go

BufferStar

More info

For more info see SQL Server 2008 Books Online  BufferWithTolerance & STBuffer

Please give feedback. Is this series of articles useful? Did it save you time? What was good, What could be better?, Notice any errors? What would you like me to cover? All thoughts, comments, suggestions welcome.

Technorati Tags: SQL Server Spatial,SQL Server,Spatial,TSQL,.NET,Geometry,Geography,STBuffer

Comments