获取多边形点mysql

发布于 2024-10-18 17:20:01 字数 729 浏览 3 评论 0原文

我在mysql中创建了一个表来存储多边形数据：

CREATE TABLE geom (g GEOMETRY);

并且我使用以下语法向其中插入了一个多边形：

INSERT INTO geom (g)
VALUES(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))')
);

现在我如何在mysql中取回该多边形的顶点（点）？为什么我问的意思是，稍后我想找出一个点是否在多边形内部。为了实现这一点，我希望我需要多边形顶点。

原文

i have created a table in mysql to store polygon data:

CREATE TABLE geom (g GEOMETRY);

And i inserted a polygon into it, using the following syntax:

INSERT INTO geom (g)
VALUES(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))')
);

Now how can i get back the vertices (points) of this polygon in mysql? Why i am asking means, later i want to find whether a point is inside a polygon. And to achieve this, i hope i need the polygon vertices.

分享到QQ

分享到微博

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

发布评论

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

时间海 2024-10-25 17:20:01

如果您想要恢复 WKT：SELECT AsText(g) FROM geom;

回复收藏 0 原文

生活了然无味 2024-10-25 17:20:01

要回答您的问题，一个不错的选择是输出 GeoJSON 格式。
在此处查看更多信息：(https://dev.mysql。 com/doc/refman/5.7/en/spatial-geojson-functions.html）

select ST_AsGeoJSON(g) from geom;

为了帮助您解决在边界多边形中选择点的实际问题：@jcorry 通过 ST_Contains

要进一步扩展它以从多边形封装的坐标表中选择所有点，您可以执行以下操作：

select
ST_X(point) as x,
ST_Y(point) as y   
from coordinates
where ST_Contains(
    (select g from geom limit 1), 
    point
)

To answer your question, a great option would be to output the GeoJSON format.
See more here: (https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html)

select ST_AsGeoJSON(g) from geom;

To help with your actual problem of selecting points in the bounding polygon: @jcorry has a great solution with ST_Contains

To further expand this out to select all points from a coordinates table that the Polygon encapsulates, you can do something like:

select
ST_X(point) as x,
ST_Y(point) as y   
from coordinates
where ST_Contains(
    (select g from geom limit 1), 
    point
)

回复收藏 0 原文

最后的乘客 2024-10-25 17:20:01

如果您想查找某个点是否在多边形中，则不需要派生各个顶点来执行此操作。 MySQL（5.6+）中有一个函数可以实现这一点：

SELECT ST_Contains(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))'), PointFromText("POINT(10 42)")
);

If you want to find whether a point is in a polygon, you don't need to derive the individual vertices to do that. There's a function in MySQL (5.6+) for this:

SELECT ST_Contains(PolygonFromText('POLYGON((
9.190586853 45.464518970,
9.190602686 45.463993916,
9.191572471 45.464001929,
9.191613325 45.463884676,
9.192136130 45.463880767,
9.192111509 45.464095594,
9.192427961 45.464117804,
9.192417811 45.464112862,
9.192509035 45.464225851,
9.192493139 45.464371079,
9.192448471 45.464439002,
9.192387444 45.464477861,
9.192051402 45.464483037,
9.192012814 45.464643592,
9.191640825 45.464647090,
9.191622331 45.464506215,
9.190586853 45.464518970))'), PointFromText("POINT(10 42)")
);

回复收藏 0 原文

~没有更多了~