[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.. 

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?

--
Brian M Hamlin
OSGeo California Chapter
blog.light42.com