MySQL equivalent of a spatial ST_Union aggregate function?

Question

I'm working on a DB-spacial project in Mysql and I was trying to perform a union operation for a group of shapes. Im looking for something similar to the SQL Server function UnionAggregate, or the PostGIS Spatial Aggregate function ST_Union

for example (In SQL SERVER):

SELECT
        geometry::STGeomFromWKB(Shape,27582),
        area_code
FROM area_table 
WHERE area_code='xxxxxxx';

ST_GeomFromWKB do the same job in Mysql.

in the second part and with the UnionAggregate function + group by :

select 
        dbo.UnionAggregate((geometry::STGeomFromWKB(Shape,27582)).MakeValid()),
        area_code
FROM area_table 
WHERE area_code='xxxxxxx'
GROUP BY area_code;

How to perform a similar operation (aggregate+group by) in MySQL?

score 3 · Accepted Answer · answered Mar 02 '17 at 14:52

I found another alternative to perform the union aggregation by using ST_Union. I had some issues cause this Mysql function get only 2 parameters as inputs.

As a solution, I create my own function as follow :

CREATE FUNCTION `fct_create_geom_collection`(ac CHAR(8))          
 RETURNS GEOMETRY
BEGIN
                    DECLARE position INT;
                    DECLARE sortie BOOLEAN DEFAULT FALSE;
                    -- get all area_IDs
                    DECLARE curgeom CURSOR FOR
                    SELECT DISTINCT area_ID     
                    FROM area_table  
                    WHERE area_code= ac ORDER BY area_ID;
                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET sortie = TRUE; 

    OPEN curgeom;   
                  -- get the first area_id
                   FETCH curgeom INTO position ;
                  -- put the shape into @var_g_0 for this area_id
                   SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g_0 
                   FROM area_table  
                   WHERE area_ID = position  ;              
    LLD: LOOP
    IF sortie THEN
    LEAVE LLD;
    END IF;
                    -- get the second area_id
                    FETCH curgeom INTO position  ;
                    -- put the second  shape into @var_g for this area_id
                    SELECT ST_GeomFromWKB(Shape,27582) INTO @var_g 
                    FROM area_table  
                    WHERE area_ID    = position   ;

                    -- performing a union operation between @var_g_0 and @var_g
                    SET @geom = ST_UNION(@var_g_0,@var_g); -- enclosed st_union                 
                    -- put the result into @var_g_0
                    SET @var_g_0 = @geom;   

                   END LOOP;
                   CLOSE curgeom;
                   RETURN ST_GeomFromText(ST_ASTEXT(@geom)); 
                 -- RETURN ST_GeomFromWKB(@geom); 

END$$

This function enable an aggregate union over shapes for each "area code" which is what UnionAggregate do and what I need.

MySQL equivalent of a spatial ST_Union aggregate function?

1 Answers1

Linked