SQL 2008查询问题-地理多边形中存在哪个LatLong? [英] Sql 2008 query problem - which LatLong's exists in a geography polygon?

问题描述

我有以下两个表:-

GeoShapes

GeoShapes

• GeoShapeId INT身份
• 名称VARCHAR(100)
• ShapeFile GEOGRAPHY [这是一个纬度/经度的闭合多边形]

犯罪地点

• CrimeLocationId INT IDENTITY
• LatLong地理[这是一个纬度/经度]

现在，我有大约1万个GeoShape结果和大约500个CrimeLocations.

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

我正在尝试找出所有500个犯罪纬度/经度点都存在于哪个GeoShapes中.

:((我只是不明白！我试图在子查询上执行STIntersects，但这没用.有什么建议吗?

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

欢呼！

我不能使用任何GEOMETRY函数..因为(如上所述)这些都是地理类型.

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

我知道如何使用STContains和STIntersects.请不要提供基本示例.我更想对上面的表格结构进行复杂的查询.

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.

推荐答案

关于您的编辑"，您很少会看到包含请不要提供..."的问题.当然一点点都可以帮助吗?尤其是因为您实际上没有向我们展示您对> STIntersects(对于此问题，或Filter())知道的 ...

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

无论如何，我都有一个邮政编码和存储位置的数据库，因此我重命名了表/列以匹配您的表/列(然后有6,535个CrimeLocatoins和3285个GeoShapes).我想您现在已经知道了-但是其他人可能会觉得这很有用...

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

以下查询返回每个 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

这花费了 age 个时间(例如20分钟)，因为我还没有设置任何地理空间索引，并且我的ShapeFiles的计数很高，但是它确实可以成功运行.如果我想按照您的建议限制结果:

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

当然，您不想对数字500进行硬编码-因此您可以在其中添加COUNT(*) FROM CrimeLocations子查询，或者在单独的查询中添加具有总计的变量.

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.

足够复杂吗?

这篇关于SQL 2008查询问题-地理多边形中存在哪个LatLong?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！