i'm trying to do a spatial query in sql 2008 -> for a given list of POI's (point of interest, long/lat GEOGRAPHY
data), which postcodes do they exist in (multipolygon GEOGRAPHY
data).
我正在尝试在sql 2008中进行空间查询 - >对于给定的POI列表(兴趣点,长/纬度地理数据),它们存在于(多字形地理数据)中。
So this is the query i tried, but it's syntactically incorrect:-
所以这是我试过的查询,但它在语法上是不正确的: -
SELECT PostCodeId, ShapeFile
FROM Postcodes a
WHERE a.ShapeFile.STIntersects(
SELECT PointOfInterest
FROM PointOfInterests
WHERE PointOfInterestId IN (SELECT Item from dbo.fnSplit(@PoiIdList, ','))
So this means i pass in a csv list of POI Id's and split them. That's not a problem .. it's my subquery in the STIntersects
. That's invalid.
所以这意味着我传递了一个POI Id的csv列表并将它们分开。这不是问题..这是我在STIntersects中的子查询。那是无效的。
So .. any suggestions folks?
那么..有什么建议吗?
1 个解决方案
#1
How about:
SELECT a.PostCodeId, a.ShapeFile
FROM (SELECT Item from dbo.fnSplit(@PoiIdList, ',')) AS POI_IDs
INNER JOIN PointOfInterests
ON PointOfInterests.PointOfInterestId = POI_IDs.Item
INNER JOIN Postcodes a
ON a.ShapeFile.STIntersects(PointOfInterests.PointOfInterest) = 1
#1
How about:
SELECT a.PostCodeId, a.ShapeFile
FROM (SELECT Item from dbo.fnSplit(@PoiIdList, ',')) AS POI_IDs
INNER JOIN PointOfInterests
ON PointOfInterests.PointOfInterestId = POI_IDs.Item
INNER JOIN Postcodes a
ON a.ShapeFile.STIntersects(PointOfInterests.PointOfInterest) = 1