Stop SQL Server selecting outside buffer

Question

I am trying to select all the parks within 200m or a railway. I can easily create and visualise the 200m buffer. However when i try and find only the parks that intersect it i get all parks returned, not just those in the intersection area. This is the code i have so far:

SELECT 
P.[geogtab] as parks, 
P.[id] as park_id 
FROM [dbo].[railway] S 
inner JOIN
[dbo].[parks] P 
ON
S.geogtab.STBuffer(200).STIntersects(P.geogtab) = 1

geogtab is my geography column and it is indexed. Is there an error in my syntax that causes all items from Table P (the parks) ?

• Ian Turton: is your data in a meters projection or have you asked for a 200 degree buffer?
• AK9: its geography so always meters i assume? But as a check i viewed the buffer on a map and that is the right size. I also just did a check IsValid() is true for the object being buffered

Answer 1

You likely have a projection error, or a unit error between two projections (ie, meters vs. decimal degrees). Make sure both your files are in a common projection, using meters, and try again. Try using epsg:5070, USA_Contiguous_Albers_Equal_Area_Conic_USGS_version