Sql 2008 query problem - which LatLong exists in a geography range?

Question

i has the following two tables: -

Geoshapes

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

CrimeLocations

• CrimeLocationId INT IDENTITY
• LatLong GEOGRAPHY [this is lat / long point]

Now I have about 10K GeoShape results and about 500CrimeLocations.

I'm trying to figure out which GeoShapes have all 500 critical lat / long points inside.

:( I just don't get it! I tried to do STIntersects in a subquery, but that didn't work. Any suggestions?

Hurrah!

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

EDIT 2: I know how to use STContains and STIntersects . Please do not provide basic examples of this. I am more interested in making a complex query with my table structure, above.

Answer 1

As for your “rights”, it’s not often that you see a question that includes “please do not grant ...”. Does every bit help? In particular, since you have not really shown us what you know about STContains or STIntersects (or Filter() , for that matter) ...

Anyway, I had a zipcodes and storelocations database, so I renamed the tables / columns according to yours (I then have 6 535 CrimeLocatoins and 3,285 GeoShapes). I suppose you already understood this, but someone 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 

This takes a lot of time (for example, 20 minutes), because I did not configure geospatial indexes, and my ShapeFiles have a high score counter, but it works successfully. If I wanted to limit 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 can add the COUNT(*) FROM CrimeLocations subquery or a shared variable from a separate query.

Is it complicated enough?