2

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.

enter image description here

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;

enter image description here

Any Idea how to perform a similar operation (aggregate+group by) in Mysql.

Thanks.

1

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.