MySQL检查点在表中存储的多边形内部。

huangapple go评论55阅读模式
英文:

MySQL Check Point Within a polygon stored within a table

问题

我运行了以下查询:

SELECT ST_Contains(
    PolygonFromText('
      POLYGON((
        60.930551 -0.775212, 
        60.332866 -0.417906, 
        59.935582 -0.887788, 
        59.837354 -1.156991, 
        58.919848 -2.577975, 
        58.378359 -2.935748, 
        57.934035 -3.75355, 
        57.761068 -3.563538, 
        57.859628 -1.643417, 
        57.538993 -1.423404, 
        56.382748 -2.522191, 
        55.846937 -1.945553, 
        55.618743 -2.262236, 
        55.463878 -2.131039, 
        55.42282 -2.162707, 
        54.968355 -3.03811, 
        54.960564 -3.232644, 
        54.929382 -3.449798, 
        54.540197 -4.141976, 
        54.467094 -5.149036, 
        55.657203 -6.713395, 
        56.947221 -7.700598, 
        57.681709 -7.656722, 
        58.298006 -7.239903, 
        60.930551 -0.775212))'),
 PointFromText("POINT(57.05 -4.49)"));

这将返回 1 以确认 57.05, -4.49 确实位于多边形内。

然后,您希望将多边形数据存储在数据库中,可以使用以下插入语句:

INSERT INTO polygons (`ownerID`, `polygon`) 
VALUES ("test", PolygonFromText('POLYGON((60.930551 -0.775212, 60.332866 -0.417906, 59.935582 -0.887788, 59.837354 -1.156991, 58.919848 -2.577975, 58.378359 -2.935748, 57.934035 -3.75355, 57.761068 -3.563538, 57.859628 -1.643417, 57.538993 -1.423404, 56.382748 -2.522191, 55.846937 -1.945553, 55.618743 -2.262236, 55.463878 -2.131039, 55.42282 -2.162707, 54.968355 -3.03811, 54.960564 -3.232644, 54.929382 -3.449798, 54.540197 -4.141976, 54.467094 -5.149036, 55.657203 -6.713395, 56.947221 -7.700598, 57.681709 -7.656722, 58.298006 -7.239903, 60.930551 -0.775212))');

但是,您在运行初始的 ST_Contains 查询并返回表中的多边形时遇到了困难。以下查询:

SELECT @Test := polygon FROM polygons where ownerID = 'test';
SELECT ST_CONTAINS(ST_GEOMFROMTEXT(@Test), PointFromText("POINT(57.05, -4.499)"));

返回了空响应,我相信有一种简单的方法可以做到这一点,但不幸的是,它超出了我的基本技能,并且通过谷歌搜索返回的示例大多是在查询中定义多边形,而不是在表中存储多边形。

您可以如何检查点是否位于存储在表 polygons 中的多边形内?

英文:

A little out of my depth with this one.

I've ran the query:

SELECT ST_Contains(
    PolygonFromText('
      POLYGON((
        60.930551 -0.775212, 
        60.332866 -0.417906, 
        59.935582 -0.887788, 
        59.837354 -1.156991, 
        58.919848 -2.577975, 
        58.378359 -2.935748, 
        57.934035 -3.75355, 
        57.761068 -3.563538, 
        57.859628 -1.643417, 
        57.538993 -1.423404, 
        56.382748 -2.522191, 
        55.846937 -1.945553, 
        55.618743 -2.262236, 
        55.463878 -2.131039, 
        55.42282 -2.162707, 
        54.968355 -3.03811, 
        54.960564 -3.232644, 
        54.929382 -3.449798, 
        54.540197 -4.141976, 
        54.467094 -5.149036, 
        55.657203 -6.713395, 
        56.947221 -7.700598, 
        57.681709 -7.656722, 
        58.298006 -7.239903, 
        60.930551 -0.775212))'),
 PointFromText("POINT(57.05 -4.49)"));

Which returns a 1 to confirm that 57.05, -4.49 is indeed within the polygon.

I want to store the polygon data in my database and did so with:

INSERT INTO polygons (`ownerID`, `polygon`) 
VALUES ("test", PolygonFromText('POLYGON((60.930551 -0.775212, 60.332866 -0.417906, 59.935582 -0.887788, 59.837354 -1.156991, 58.919848 -2.577975, 58.378359 -2.935748, 57.934035 -3.75355, 57.761068 -3.563538, 57.859628 -1.643417, 57.538993 -1.423404, 56.382748 -2.522191, 55.846937 -1.945553, 55.618743 -2.262236, 55.463878 -2.131039, 55.42282 -2.162707, 54.968355 -3.03811, 54.960564 -3.232644, 54.929382 -3.449798, 54.540197 -4.141976, 54.467094 -5.149036, 55.657203 -6.713395, 56.947221 -7.700598, 57.681709 -7.656722, 58.298006 -7.239903, 60.930551 -0.775212))')");

But I am now struggling to run the initial ST_Contains query whilst also returning the polygon from the table.

SELECT @Test := polygon FROM polygons where ownerID = 'test';
SELECT ST_CONTAINS(ST_GEOMFROMTEXT(@Test), PointFromText("POINT(57.05, -4.499)"));

Returns a null response and I'm sure there is a simple way to do this but unfortunately it evades my basic skills and googling has returned lots of examples where the polygon is defined in the query, but not where it is stored in a table.

How can I check to see if a point is within a polygon that is stored in the table polygons?

Thanks

答案1

得分: 0

将值存储到您的变量@Test时,您忘记将geom值选择为文本,而您的POINT中有一个逗号。

SELECT @Test := ST_AsText(`polygon`) FROM `polygons` WHERE ownerID = 'test';
SELECT ST_CONTAINS(ST_GEOMFROMTEXT(@Test), ST_PointFromText("POINT(57.05 -4.499)"));

或者不转换为WKT -

SELECT @Test := `polygon` FROM `polygons` WHERE ownerID = 'test';
SELECT ST_CONTAINS(@Test, ST_PointFromText("POINT(57.05 -4.499)"));

或者只需 -

SELECT *,
    ST_AsText(`polygon`),
    ST_CONTAINS(`polygon`, ST_GeomFromText("POINT(57.05 -4.499)"))
FROM `polygons`
WHERE `owner_id` = 'test';
英文:

When storing the value to your variable @Test you forgot to select the geom value as text and your POINT has a comma in it.

SELECT @Test := ST_AsText(`polygon`) FROM `polygons` WHERE ownerID = 'test';
SELECT ST_CONTAINS(ST_GEOMFROMTEXT(@Test), ST_PointFromText("POINT(57.05 -4.499)"));

Or without converting to WKT -

SELECT @Test := `polygon` FROM `polygons` WHERE ownerID = 'test';
SELECT ST_CONTAINS(@Test, ST_PointFromText("POINT(57.05 -4.499)"));

Or just -

SELECT *,
    ST_AsText(`polygon`),
    ST_CONTAINS(`polygon`, ST_GeomFromText("POINT(57.05 -4.499)"))
FROM `polygons`
WHERE `owner_id` = 'test';

huangapple
  • 本文由 发表于 2023年2月8日 18:57:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/75384769.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定