如何在 Sql 2008 中执行此空间查询？

发布于 2024-07-12 22:28:12 字数 597 浏览 7 评论 0原文

我正在尝试在 sql 2008 中进行空间查询 -> 对于给定的 POI 列表（兴趣点、长/纬度 GEOGRAPHY 数据），它们存在于哪些邮政编码（多多边形 GEOGRAPHY 数据）中。

所以这是我尝试过的查询，但它在语法上不正确：-

SELECT PostCodeId, ShapeFile
FROM Postcodes a
WHERE a.ShapeFile.STIntersects(
    SELECT PointOfInterest
    FROM PointOfInterests
    WHERE PointOfInterestId IN (SELECT Item from dbo.fnSplit(@PoiIdList, ','))

所以这意味着我传递了 csv POI Id 列表并拆分它们。这不是问题..这是我在 STIntersects 中的子查询。那是无效的。

那么..大家有什么建议吗？

原文

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

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.

So .. any suggestions folks?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淑女气质 2024-07-19 22:28:12

怎么样：

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

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

回复收藏 0 原文

~没有更多了~