2

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.

Improve this question
7
  • 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...
    – Inactivated Account
    Commented Aug 14, 2015 at 16:12
  • @mapBaker see the edit above. I think I am getting close but still feel far away.
    – Branco
    Commented Aug 14, 2015 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
    – Inactivated Account
    Commented Aug 14, 2015 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.
    – Branco
    Commented Aug 14, 2015 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.
    – Branco
    Commented Aug 14, 2015 at 17:10

1 Answer 1

Reset to default
9

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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.