ArcGIS Spatial SQL Query to find features within a certain distance from other table

1285
3
04-24-2017 10:53 AM
Highlighted
Occasional Contributor

I am trying to work on a SQL spatial query to duplicate what you can do on Arcmap. I am basically trying to select features from one SQL table which contains parcels data(polygons), which is within 200 feet of another layer which contains streams(lines). I am using pure SQL and not ArcSDE.. I have tried to find solutions online but have not been able to find anything that works so far.

basically need features selected from parcel layer(polygons) that are within 200 feet of streams(lines) 

I would greatly appreciate any help.

I am using SQL Server 2008 and spatial columns on both tables are geometry data type.

Tags (2)
Reply
0 Kudos
3 Replies
Highlighted
Esri Esteemed Contributor

Have you tried a non-Esri forum (more specifically, a Microsoft one) to obtain this answer?  Even a vendor-neutral site like GIS StackExchange is more likely to have this answered than here in GeoNet.

The PostGIS operator is ST_DWithin, but neither SDE.ST_Geometry nor the native Microsoft geometry/geography operators support that method, so you'd need to evaluate using an ST_Buffer with ST_Intersects (which uses a spatial index, if available) and ST_Distance (which doesn't use a spatial index at all).

I need to point out that SQL Server 2008 is very old software (released in beta nearly a decade ago), and support for it among GIS vendors (and Microsoft) is waning. You should consider upgrading to at least SQL Server 2012 or 2014, which were at least released this decade.

- V

Highlighted
MVP Frequent Contributor

Something like STDistance (geography Data Type) | Microsoft Docs  but wrapped with a where clause, to capture your 200 ft requirement. 

Reply
0 Kudos
Highlighted
Occasional Contributor

Thanks Vince and Thomas,

I was able to make the query work a while back using STIntersects() and STBuffer() methods but with the number of features we had it took 4 plus hours to run the query in SQL Server vs 10 minutes in Arcmap despite using spatial index. We are thinking about scripting out the Arcmap process and pushing the results to SQL instead of natively doing it in SQL.