Sql 2008 query problem - which LatLong's exists in a geography polygon?

Question

Hi folks,

i have the following two tables:-

GeoShapes

• GeoShapeId INT IDENTITY
• Name VARCHAR(100)
• ShapeFile GEOGRAPHY [ this is a closed Polygon of Lat/Longs ]

CrimeLocations

• CrimeLocationId INT IDENTITY
• LatLong GEOGRAPHY [ this is a Lat/Long Point ]

Now, i have around 10K GeoShape results and around 500CrimeLocations.

I'm trying to figure out which GeoShapes all 500 crime lat/long points exist inside of.

:( I just don't get it! I was trying to do an STIntersects on a subquery but that didn't work. Any suggestions?

cheers!

EDIT 1: I cannot use any GEOMETRY functions .. because (as stated above) these are all geography types.

EDIT 2: I know how to use STContains and STIntersects. Please don't provide basic examples of that. I'm more curious about to do a complex query with my table structure, above.

Answer 1

Regarding your 'edits', it's not often you see a question that includes "please don't provide...". Surely every little bit helps? Particularly since you haven't actually shown us what you do know about STContains or STIntersects (or Filter() for that matter)...

Anyway, I had a database of zipcodes and storelocations handy, so I renamed the tables/columns to match yours (I then have 6,535 CrimeLocatoins and 3,285 GeoShapes). I presume you've figured it out by now - but someone else might find this useful...

The following query returns the number of CrimeLocations in each GeoShapes.ShapeFile

SELECT G.Name, COUNT(CL.Id)
FROM   GeoShapes G
INNER JOIN CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY G.Name
ORDER BY 2 DESC

It takes ages (like 20 mins) because I haven't setup any geospatial indexes and my ShapeFiles have a high point-count, but it does run successfully. If I wanted to restrict the results as you suggest:

SELECT G.Name, COUNT(CL.Id)
FROM   GeoShapes G
INNER JOIN CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1
GROUP BY G.Name
HAVING COUNT(CL.Id) = 500

Of course you don't want to hardcode the number 500 - so you could add a COUNT(*) FROM CrimeLocations subquery there, or a variable with the total from a separate query.

Is that complex enough?