2

Using PostGIS I want to collect all attributes into one polygon. The old table looks like this:

Column   | Type
---------+------
xyz_cols | `xyz`
geometry | geometry(MultipolgyonZ, 2193)

Performing the query:

CREATE TABLE new_table AS 
SELECT
  1::INT as id,
  ST_Collect(geom)
FROM
  old_table

which works but I lose all geometry information and the output table is like this:

Column   | Type
---------+------
id       | INT
geometry | geometry

Even if I wrap the ST_Collect in a ST_Multi() I lose the geometry information.

3

You've only lost the geometry's typmod. This is analogous to

select 1::numeric(3,2) AS num1, 1::numeric(3,2) / 1 AS num2;
 num1 |          num2
------+------------------------
 1.00 | 1.00000000000000000000

where the scale/precision typmod for numeric is undefined from the maths operation.

If you want to retain/enforce the typmod for the result, just use it with a cast, and pick out only MultiPolygons with ST_CollectionExtract:

CREATE TABLE new_table AS 
SELECT
  1::INT as id,
  ST_CollectionExtract(ST_Collect(geom), 3)::geometry(MultiPolygonZ,2193)
FROM
  old_table

Note that other geometry types, if present, will be MULTIPOLYGON Z EMPTY.

  • This solves the issue per se but unfortunately I cannot cast the output as MultiPolygonZ but only as a GeometryCollectionZ which is not able to be plotted in QGIS. How does one cast to as multipolygonz instead of the collection? – Phil Donovan Sep 23 '14 at 5:24
  • Try ST_CollectionExtract – Mike T Sep 23 '14 at 5:35
  • Yeah I tried that. Just took me back to where I started with an uncollected geometry. – Phil Donovan Sep 23 '14 at 5:40
  • 1
    Maybe you intend to use ST_Union – Mike T Sep 23 '14 at 5:42
  • It's just for viewing purposes and involves 50,000 odd geometries so it'd be great to be able to collect the multipolygons in one unified multipolygon without having to actually break down the geometry. Regardless, this is probably another question. You have solved the issue I had. – Phil Donovan Sep 23 '14 at 5:53

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.