[postgis-users] typemod on ST_Union() results

[maplabs at light42.com]

Hi All -

  I have a process where I split a large number of geometries by a second set of (smaller) geometries, then recombine them using a GROUP BY gid.  Both source set and splitter set have the same SRID, and the results, and the combined results, therefore have an SRID. 

  But in the age of typemod, geometry_columns sees a big zero SRID. ugh  Using a cast inline has been suggested, but in this case, the result of the ST_Union()  can be either Polygon or Multipolygon.   As far as I know, the typemod has to know specifically which one.. 
(actually the result should be a single polygon each time but I havent enforced it yet)

e.g. 

-- results in geometry_column SRID 0

create table dbg_z0_avgfs_blue as 

  select  

    p.gid,  sum( p.avg_family_size_estimate * st_area(p.geom_purple) / 22500.0  ) as avg_family_size_estimate,  

    st_union( p.geom_purple ) as geom

from   dbg_z0_avgfs_purple p 

GROUP BY  p.gid;

-- no no

create table dbg_z0_avgfs_blue as  

  select     p.gid,  sum( p.avg_family_size_estimate * st_area(p.geom_purple) / 22500.0  ) as avg_family_size_estimate,    

  st_union( p.geom_purple )::geometry(Multipolygon,3310) as geom  --< FAIL, sometimes its a Polygon

from   dbg_z0_avgfs_purple p

GROUP BY  p.gid;

--

  What to do?  input welcome

--

Brian M Hamlin
OSGeo California Chapter
blog.light42.com