STContains on Geography 列 [英] STContains on Geography column

问题描述

我在纬度/经度(地理)有一个公交车站.我想检查我的巴士点(纬度/经度)在某个地区的位置.我的区域是一组纬度/经度点.我有地理形式的区域的纬度/经度(构成区域的纬度/经度的轮廓).现在我有一点经纬度，我想确定该点是否在我的区域内.我怎样才能做到这一点?4000 点构成一个区域的轮廓，一个点是检查该区域是否存在.我将如何检查?

I have a bus point in Lat/long (geography). I want to check where my bus point (lat/long) lies in a region. My region is a set of lat/long points. I have lat/long of a region (Outline of the lat/longs which makes a region) in geography form. Now I have one point lat/long and I want to identify if that point lies inside my region. How can I do that? 4000 points make an outline of a region and one point is to check if that exist in that region or not. How will I check that?

我想我可以使用 STContains 或 STintersect 但我不知道确切的语法.

I think I can use STContains or STintersect but I don't know the exact syntax.

SELECT
    Region.BlockID, Bus_Route.geography
FROM
    Bus_Route, Region
WHERE
    Region.points.STContains(Bus_Route.geography) = 1

推荐答案

我使用了我为您编写的代码 (从 ShapeFile 中存储Point"列)作为获取点表的起点.从那里:

I used the code that I wrote for you (Storing 'Point' column from ShapeFile) as a starting point to get a table of points. From there:

select geography::STPolyFromText(
'POLYGON((' + 
    stuff((
        select ',' + cast(g.STPointN(t.i).Long as varchar(10)) + ' ' + cast(g.STPointN(t.i).Lat as varchar(10))
        from [a]
        cross join tally as [t]
        where t.i <= g.STNumPoints()
        order by i
        for xml path('')

    ), 1, 1, '') + '))'
    , 4326)

这段代码假设了几件事.第一:您的点的顺序与您在该区域的边界周围一样.这很重要.想想一个点点滴滴的拼图.为了获得正确的图片，您必须以正确的顺序进行操作.其次，与第一个相关，它们必须处于正确的方向.多边形遵循左手规则.也就是说，如果您按顺序遍历这些点，您就是在定义位于您左侧的区域.因此，如果您以相反的顺序指定点，您将获得除您所在地区之外的所有内容！您马上就会知道这一点，因为在 SQL 2012 之前，您仅限于拥有完全包含在半球中的区域.

This code assumes a couple of things. First: that your points are ordered as you would go around the boundary of the region. This matters. Think about a connect-the-dots puzzle. In order to get the right picture, you have to do them in the right order. Secondly, related to the first, they have to be in the correct orientation. Polygons follow the left-hand rule. That is, if you were walking the points in order, you're defining the region that is at your left side. So, if you specify the points in reverse order, you get everything but your region! You'll know this right away since before SQL 2012, you're limited to having regions that are fully contained in a hemisphere.

但现在我不得不问:鉴于您之前提出的问题(我在上面链接到)，您是否收到了包含多边形的 shapefile?如果是这样，请将其存储起来，以免重新构建它.

But now I have to ask: given the question that you asked before (that I linked to above), are you receiving shapefiles with polygons in them? If so, store that and save yourself the headache of reconstructing it.

这篇关于STContains on Geography 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！