Spatial SQL -> MS-SQL 2008. Query spatial data [1]

We will define some arbitrary polygon over the city center and use it to find intersecting points. We will use STBuffer() method to show regular and overlaping points in different size:
USE milano
GO

-- define polygon located in the city center
DECLARE @geog GEOGRAPHY;
SET @geog = 'POLYGON((
9.18536598640948 45.4632486699817,
9.18781690531247 45.4626497708255,
9.19407767817776 45.4630308848709,
9.19623487935473 45.4663205889125,
9.19114893838159 45.4681850981786,
9.18482142287969 45.466816962432,
9.18536598640948 45.4632486699817
))'
;

-- select all point with buffer 10 and overlaping points with 35

select id, name, [type], g from dbo.milano_str
  UNION ALL
select id, name, [type], g.STBuffer(10) from dbo.milano_pnt
  UNION ALL
select 0, 'center polygon' as name, '' as [type], @geog.STBuffer(40)
  UNION ALL
select id, name, [type], g.STBuffer(35) from dbo.milano_pnt
where g.STIntersects(@geog)=1;


Spatial results will look like this:




sqlexamples.info