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 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:

we do a lot of this with SQL Server spatial data, including tables with existing geometries, and geometry variables... we perform spatial functions on combinations of tables and variables with great success... In terms of resources, there are many examples on this forum under the tags you've used already - if you want to be more specific I'd be glad to help out...
– DPSSpatialAug 14 '15 at 16:12

@mapBaker see the edit above. I think I am getting close but still feel far away.
– BrancoAug 14 '15 at 16:18

1

I see what you're up to - I have examples of this at work but I'm off today... Let me come back on Monday and send some things your way... but basically you can select all features from your table with existing geometries, then use a where clause at the bottom using the intersection of the polygon variable... so your query would be doing something like select * from tablename where tablename.geom.STIntersects(@polygon) = 1
– DPSSpatialAug 14 '15 at 16:21

@mapBaker -- I will go along that route and see what I can find. I have only been working with a spatial server for maybe 3 hours so it is all new to me. Seems simple but I am used to dealing with .NET collections and not SQL queries, so it is pretty different.
– BrancoAug 14 '15 at 16:23

1

@mapBaker... Your quick little snippet worked. If you post it as an answer, I'll accept it. I am also editing my question to reflect the query I made from it.
– BrancoAug 14 '15 at 17:10

1 Answer
1

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: