SQL Server – Spatial Querying in SQL Server

intersectionquerysql serversql-server-spatial

I am looking for ways to learn querying for SQL Server Spatial (2008/2012). I have a table created from Shape2SQL and have been trying to run a simple intersection query to get results from that table that fall within a polygon I am creating.

I got as far as:

declare @poly geometry = 'POLYGON((-9372539.231 4862367.224, -9370110.628 4925510.907, -9457540.343 4926118.058, -945571889 4840509.795, -9372539.231 4862367.224))'

I haven't figured out how to take the geometry from my imported shapefile table and get the points that fall within the polygon geometry. The options I have found online seem to take WKT from a field in the table to run the intersection.

I am looking for resources that apply to this kind of querying, or any other suggestions that may help me out along the way. I picked up a copy of Beginning Spatial with SQL Server 2008 but haven't read through it yet.

Eventually, my goal is to be able to generate a query based off a map's extent and query features that fall within that extent.

Edit

I am looking for a query to allow me to do the above intersection. Any additional resources would be great but not an answer for me.

So far, I have found how to grab the geom records and convert them to WKT.

SELECT [geom].STAsText() FROM [Table] WHERE [geom] IS NOT NULL

What I am trying to do is take that data set, currently I am putting it into a temporary table, but I don't think that is necessary. What I want to do is find out which records from that data set fall within the generic polygon (@poly) I am setting up.

I am thinking if I take the WKT result and put it into a GEOMETRYCOLLECTION, I can pass that into something like SELECT @myWKTResults.STIntersection(@poly).ToString(). I just don't know/can't find a good example of the next step.

Result

Going from mapBaker's comments, I was able to set this up to work. My query ended up being:

declare @poly geometry = 'POLYGON((-9486683.581 4810152.256, -9282073.762 4821688.121, -9262037.786 4625578.413, -9477576.319 4628614.167, -9486683.581 4810152.256))'

SELECT * FROM [TABLE]
WHERE @poly.STIntersects([geom].STAsText()) <> 0;

The only thing I really adjusted as adding in the .STAsText() into the query since running without didn't crash, but I also got 0 rows back.

Best Answer

If you've got a polygon you want to use as a declared variable and intersect it with a table containing existing geometry, your query (including your polygon variable declaration) would look something like this:

(MSSQL Server syntax)

declare @polygon geometry = 'POLYGON((-9486683.581 4810152.256, -9282073.762 4821688.121, -9262037.786 4625578.413, -9477576.319 4628614.167, -9486683.581 4810152.256))'

select * from tablename 

where tablename.geom.STIntersects(@polygon) = 1
Related Question