英文:
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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论