How can I use group functions on a spatial JOIN to update a table in MySQL?

Question

I'm trying to update the total revenue for offices located in different geographies. The geographies are defined by circles and polygons which are both in the shapes.shape column.

When I run the query below, MySQL throws "R_INVALID_GROUP_FUNC_USE: Invalid use of group function"

I tried to adapt this answer, but I can't figure out the logic with the conditional join and geospatial data -- it's not as simple as adding a subquery with a WHERE clause. (Or is it?)

For context, I have about 350 geographies and 150,000 offices.

UPDATE
    shapes s
    LEFT JOIN offices ON (
        CASE
            WHEN s.type = 'circle' THEN ST_Distance_Sphere(o.coords, s.shape) < s.radius
            ELSE ST_CONTAINS(s.shape, o.coords)
        END
    )
SET
    s.totalRevenue = SUM(o.revenue);

UPDATE:

This works, but it's slow and confusing. Is there a faster/more concise way?

UPDATE
    shapes s
    LEFT JOIN (
        SELECT
            t.shape_id,
            SUM(g.revenue) revenue
        FROM
            shapes t
            LEFT JOIN offices o ON (
                CASE
                    WHEN t.type = 'circle' THEN ST_Distance_Sphere(o.coords, t.shape) < t.radius
                    ELSE ST_CONTAINS(t.shape, o.coords)
                END
            )
        GROUP BY
            t.shape_id
    ) b ON s.shape_id = b.shape_id
SET
    s.totalRevenue = b.revenue;

Please fix the queries -- the alias `o` is not defined in the first query and `g` is not defined in the second. — Rick James, Nov 17 '21 at 20:25

score 0 · Answer 1 · answered Nov 17 '21 at 20:30

I think that speed can be helped by splitting into two UPDATEs:

... WHERE t.type = 'circle' 
      AND ST_Distance_Sphere ...

and

... WHERE t.type != 'circle' 
      AND ST_CONCAINS ...

And then see if the resulting SQLs can be simplified.

To further investigate the query, please isolate the subquery b and see if the bulk of the time is in doing that SELECT (as opposed to the time doing the UPDATE).

Please provide SHOW CREATE TABLE for each table and EXPLAIN for both the UPDATE(s) and the isolated SELECT(s). A number of clues might come from such.

How can I use group functions on a spatial JOIN to update a table in MySQL?

1 Answers1