Grouping database results based on intersection using MySQL GIS?

Question

I am using MySQL GIS (5.7.18).

I have a number of locations in the database. I also have a number of other polygons (not in the database).

I would like to perform a single query, intersecting all the places with all the polygons, grouping the places based on the polygon that they intersect with.

If this is possible, how can it be done?

Answer 1

I do this is MSSQL and I know that MySql has different syntax but they both implement OGC geometry methods so I'll propose what works in sql server and you can probably translate it to MySql because the concept will be the same.

First step, you'll have to get your polygons into the database. There's no way to get the two joined up unless you want to add a column to your point table and send the polygons in one at a time and update the field with the polygon id if it intersects the point. But you requested a single query so, I am going to explain how to do it once the polygons are in a table.

First step is to join the points to the polygon like this:

SELECT a.point_id, pg.polygon_id FROM pointtable pt
INNER JOIN polygontable pg ON pt.geom.STIntersects(pg.geom) = 1

Now, if you want to truly GROUP BY the polygon that the points are in, you'll have to determine which fields you are summarizing and determine how you want to GROUP the geometry. For example, do you want all of the points to become one multi-point geometry or do you want to get the centroid of all of the points and represent that as the geometry or just use the polygon as the resulting geometry?

I'll show you the simplest, which is to just use the polygon as the geometry. I'll throw in one column that we are summarizing just for an example. We will simply do a count of how many points were in that polygon. Then we have to join those results back to the original polygon layer to get the shape since aggregate functions don't work on geometry columns unless you are combining geometries.

SELECT
    pg2.polygon_id,
    agg.pt_qty,
    pg2.geom
FROM
    (
        SELECT
            ptpg.polygon_id,
            COUNT(ptpg.point_id) AS pt_qty
        FROM
            (
                SELECT
                    pt.point_id,
                    pg.polygon_id
                FROM
                    pointtable pt
                    INNER JOIN polygontable pg ON pt.geom.STIntersects(pg.geom) = 1
            ) AS ptpg
        GROUP BY
            ptpg.polygon_id
    ) AS agg
    INNER JOIN polygontable pg2 ON pg2.polygon_id = agg.polygon_id;

This will give you a result like the following:

 |polygon_id |pt_qty    |geom
-|-----------|----------|-------------
 |K-10       |1613      |0x00000000010...
 |K-12       |1415      |0x00000000010...
 |K-14       |1376      |0x00000000010...