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