如何处理MySQL多边形重叠查询？ [英] How can I handle MySQL polygon overlap queries?
问题描述
例如，我有一个用户表。每个用户都是地图上的正方形（或圆形）。
我想找到在地图上重叠另一个正方形的用户。 MySQL 5.6当前支持此功能吗？ （关于MySQL的开发版本呢？）
请注意，我并不是在寻找在这个正方形内找到一个点的用户。我正在寻找找到其正方形与该正方形重叠的用户（不一定包含；只要两个正方形都接触-很好）。
如果有人可以提供一个插入记录的示例，然后使用ST_INTERSECT多边形查询它们。
以查找哪个功能可以为您完成工作。
请注意ST / MBR函数之间的以下区别：
选择完全在正方形内的所有内容（从下面的＃0开始）
（正方形＃1，＃2，三角形＃4）
SELECT id，AsText（poly）FROM`空间`
WHERE
Contains（
GEOMFROMTEXT（'POLYGON（（0 0,20 0,20 20,0 20,0 0））'，0），
`poly `
）
;
选择完全在正方形内的所有内容（从下面的＃0开始）并且不共享边
（正方形＃2，三角形＃4）
SELECT id，AsText（poly）FROM`空间`
WHERE
ST_Contains（
GEOMFROMTEXT（'POLYGON（（0 0,20 0,20 20,0 20,0 0））'，0），
`poly `
）
;
编辑＃2：
@StephanB的非常好加法（对空间扩展的支持并非完美无缺，但是它可以帮助您了解什么是扩展空间。在您的数据库中。帮助了我附上这些图像。
For example, I have a table of users. Users each are a "square" (or circle) on a map.
I want to find users that overlap another square on a map. Is this currently supported in MySQL 5.6? (What about development versions of MySQL?)
Please note that I'm not looking for "find users with a point inside this square". I'm looking for "find users whose square overlaps this square (not necessarily contain; as long as both squares touch - it's fine)".
It would really help if someone could provide an example of INSERTING records and then QUERYING them using ST_INTERSECT polygons.
Create table with polygon column
Please note, that to use spatial indexes, you can not use InnoDB. You can use the geometry without spatial indexes, but performance degrades as usual.
CREATE TABLE IF NOT EXISTS `spatial` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`poly` geometry NOT NULL,
UNIQUE KEY `id` (`id`),
SPATIAL INDEX `poly` (`poly`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Get 3 squares and a triangle inserted
INSERT INTO `spatial` (`poly`) VALUES (GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0));
INSERT INTO `spatial` (`poly`) VALUES (GeomFromText('POLYGON((10 50,50 50,50 10,10 10,10 50))',0));
INSERT INTO `spatial` (`poly`) VALUES (GeomFromText('POLYGON((1 15,5 15,5 11,1 11,1 15))',0));
INSERT INTO `spatial` (`poly`) VALUES (GeomFromText('POLYGON((11 5,15 5,15 1,11 5))',0));
Select everything that intersects small square in lower left corner (purple square #1)
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Intersects(`poly`,
GEOMFROMTEXT('POLYGON((0 0,2 0,2 2,0 2,0 0))', 0 )
)
;
Select everything that intersects triangle ranging from lower left to lower right corners to upper right corner) (squares #1 and #2 and triange #4.)
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Intersects(`poly`,
GEOMFROMTEXT('POLYGON((0 0,50 50,50 0,0 0))', 0 )
)
;
Selects everything in square that is outside of our image (nothing)
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Intersects(`poly`,
GEOMFROMTEXT('POLYGON((100 100,200 100,200 200,100 200,100 100))', 0 )
)
;
Edit #1:
I reread the question and I think you have the spatial relations a little confused. If what you want is find everything that fits wholy inside a square (polygon), then you need to use Contains/ST_Contains. Please see spatial functions in MySQL documentation to find out which function does the job for you. Please note the following difference between ST/MBR functions:
Selects everything that is completely inside a square (#0 from below) (squares #1, #2, triangle #4)
SELECT id,AsText(poly) FROM `spatial`
WHERE
Contains(
GEOMFROMTEXT('POLYGON((0 0,20 0,20 20,0 20,0 0))', 0 ),
`poly`
)
;
Selects everything that is completely inside a square (#0 from below) and shares no edges (square #2, triangle #4)
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Contains(
GEOMFROMTEXT('POLYGON((0 0,20 0,20 20,0 20,0 0))', 0 ),
`poly`
)
;
Edit #2:
Very nice addition from @StephanB (SQL fiddle)
To list all intersections join the table onto itself and filter away the reverse intersection
Select any overlapping objects
SELECT s1.id,AsText(s1.poly), s2.id, AsText(s2.poly)
FROM `spatial` s1, `spatial` s2
WHERE
ST_Intersects(s1.poly, s2.poly)
AND s1.id < s2.id
;
(just note, that you should remove the AND s1.id < s2.id if you are working with CONTAINS, as CONTAINS(a,b) <> CONTAINS(b,a) while Intersects(a,b) = Intersects(b,a))
In the following picture (non-exhaustive list):
2 intersects #6.
6 intersects #2
0 intersects #1, #2, #3, #4, #5
1 intersects #0, #5
0 contains #1, #3, #4 and #5 (#1, #3, #4 and #5 are within #0)
1 contains #5 (#5 is within #1)
0 st_contains #3, #4 and #5
1 st_contains #5
Edit #3: Searching by distance/Working in (with) circles
MySQL does not directly support circle as a geometry, but you can use spatial function Buffer(geometry,distance) to work around it. What Buffer() does, is creating a buffer of said distance around geometry. If you start with geometry point, the buffer is indeed a circle.
You can see what buffer actually does by calling just:
SELECT ASTEXT(BUFFER(GEOMFROMTEXT('POINT(5 5)'),3))
(result is quite long, so i won't post it here) It actually creates polygon which represents the buffer - in this case (and my MariaDB) the result is 126 point polygon, which aproximates a circle. With such a polygon you can work as you would work with any other polygon. So there should be no performance penalty.
So if you want to select all polygons which fall into a circle you can rinse and repeat previous example (this will find just the square #3)
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Contains(
Buffer(GEOMFROMTEXT('POINT(6 15)'), 10),
`poly`
)
;
Select all polygons which intersect with a circle
SELECT id,AsText(poly) FROM `spatial`
WHERE
ST_Intersects(
Buffer(GEOMFROMTEXT('POINT(6 15)'), 10),
`poly`
)
;
When working with shapes different from rectangles, you should use the ST_* functions. Functions without ST_ use a bounding rectangle. So the previous example selects the triangle #4 even though it is not in the circle.
As Buffer() creates quite big polygons, there will definitely be some performance penalty over using the ST_Distance() method. Unfortunately I can not quantify it. You will have to do some benchmarking.
Another way of finding objects by distance is using the ST_Distance() function.
Select all elements from the table and compute their distance from point POINT(6 15)
SELECT id, AsText(`poly`),
ST_Distance(poly, GeomFromText('POINT(6 15)'))
FROM `spatial`
;
You can use ST_Distance in WHERE clause as well.
Select all elements whose distance from POINT(0 0) is less or equal than 10 (selects #1, #2 and #3)
SELECT id, AsText(`poly`),
ST_Distance(poly, GeomFromText('POINT(6 15)'))
FROM `spatial`
WHERE ST_Distance(poly, GeomFromText('POINT(6 15)')) <= 10
;
Although the distance is computed from closest point to closest point. Making it similar to the ST_Intersect. So the above example will select #2 even though it does not fit wholly inside the circle.
And yes, the second argument (0) for GeomFromText(text,srid), does not play any role, you can safely ignore it. I have picked it up from some sample and it kind of stuck in my answer. I have left it out in my later edits.
btw. phpMyAdmin support for spatial extension is not flawless, but it helps quite a bit to see what is in your database. Helped me with these images I have attached.
这篇关于如何处理MySQL多边形重叠查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
