SQL:如何检查一个区域是否在纬度/经度位置？

Question

基本上，如果以上数据位于西雅图市中心的以下坐标位置:A点：-122.341518 47.578047 (经纬度)点B：-122.284355 47.578047 (经纬度)点C：-122.278004 47.630362 (经纬度)点：-122.371559 47.630362点(经纬度)，我基本上希望使用函数检查和更新上表中的新列(函数)。

如果在市中心找到一个家庭(hhid)，将新列“in_downtown”的值设置为1；否则，将其设为null。

Answer 1

BEGIN
FOR a IN (SELECT * FROM clean.households)
LOOP
UPDATE clean.households set geom=ST_GeomFromText('POINT(' || a.x_coord || ' ' || a.y_coord || ')',2285) where hhid ='a.hhid';
UPDATE clean.households set wkt = ST_AsText(ST_Transform(ST_GeomFromText('POINT(' || a.x_coord || ' ' || a.y_coord || ')',2285),4269))where hhid='a.hhid';
RETURN NEXT a;
END LOOP;

这才是有效的答案。谢谢!

Answer 2

BEGIN -- (IN wkt TEXT)
DECLARE pos TEXT;
DECLARE X FLOAT;
DECLARE Y FLOAT;
DECLARE spa INT;

DECLARE TOP_EDGE FLOAT;
DECLARE BOTTOM_EDGE FLOAT;
DECLARE LEFT_EDGE FLOAT;
DECLARE RIGHT_EDGE FLOAT;

-- You'll need to set the above variables here
TOP_EDGE = 47.630362;
BOTTOM_EDGE = 47.578047;
LEFT_EDGE = -122.341518;
RIGHT_EDGE = -122.371559;
-- End bounds setup

pos = LEFT(wkt, LENGTH(wkt) - 1);
pos = RIGHT(wkt, LENGTH(wkt) - 6);
spa = INSTR(pos, ' ');

X = FLOAT(LEFT(pos, spa));
Y = FLOAT(RIGHT(pos, LENGTH(pos) - spa - 1); -- May be off by one here.

RETURN CASE WHEN (Y < TOP_EDGE AND Y > BOTTOM_EDGE 
    AND X < RIGHT_EDGE AND X > LEFT_EDGE) THEN 1 ELSE NULL END;
END

这应该很近。如果不将坐标存储为文本，它可能会大大简化。

Answer 3

你可以使用多边形的几何对象。你可以这样做：

Declare @point geometry
Declare @polygon geometry
SET @point = geometry::STGeomFromText('POINT (-88.22 41.50000001)', 4326)
SET @polygon = geometry::STGeomFromText('POLYGON ((-88.2 41.5, -88.2 41.6, -88.3 41.6, -88.3 41.5, -88.2  41.5))', 4326)

Select case when @point.STIntersection(@polygon).ToString() = 'GEOMETRYCOLLECTION EMPTY' then 0 else 1 end