How can I handle MySQL polygon overlap queries?

Question

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.

Answer 1

SQL fiddle

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.