5

I've decided to move to PostGIS to merge polygons rather than spend hours doing in in QGIS. However, I'm hitting an issue where the results of an ST_Union query are being stored, or at least outputted as points and not merged polygons.

Here is my SQL query:

DROP TABLE opengeo.london_merged;
CREATE TABLE opengeo.london_merged (
  the_geom geometry(MultiPolygon),
  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
);

INSERT INTO opengeo.london_merged 
SELECT ST_Union(the_geom) AS the_geom
FROM opengeo.tq_building

This is the output from the new opengeo.london_merged table that I'm getting: enter image description here

However, if I just run a straight this same SQL on the orignal opengeo.tq_building table as an SQL view in GeoServer (so not creating a new table, just outputting the results):

SELECT ST_Union(the_geom) AS the_geom
FROM opengeo.tq_building

I get the following, correct output: enter image description here

There's obviously something going on with how the new table is created or how the data is stored / retrieved from it, I just don't yet know enough about PostGIS to put my finger on it.

How can I store the ST_Union output in a new table so that the output is the expected polygons?

Edit

I ran the following SQL view on the opengeo.london_merged table in GeoServer and it gives me the correct output, suggesting that the data is stored correctly but it being outputted / given to me wrong when I view the table normally. Perhaps a type or SRID issue? I don't know.

SELECT the_geom FROM opengeo.london_merged

I'm still keen to solve this.

4
  • What's in the geometry_columns table for opengeo.london_merged?
    – Micha
    Commented Feb 8, 2013 at 13:21
  • Micha, opengeo.london_merged is a table. I can't find anything called geometry_columns anywhere. Am I looking in the wrong place? Commented Feb 8, 2013 at 13:24
  • Thanks Micha, it turns out Populate_Geometry_Columns() is what I was after so I've added an answer with the results. Commented Feb 8, 2013 at 13:45
  • The usual procedure for creating a spatial table is: create the table without a geom column, then run the AddGeometryColum() function to correctly set up the constraints and the entry in the geometry_columns table, and only at the end to insert data.
    – Micha
    Commented Feb 9, 2013 at 19:42

1 Answer 1

3

Thanks to a hint by Micha (below) I solved this by creating a new table with the results of the ST_Union query, however this time running the Populate_Geometry_Columns() method afterward to make sure the geometry types and SRID are correct.

This is the new, much cleaner SQL:

CREATE TABLE opengeo.london_merged_correct AS
SELECT
  ST_Union(the_geom) AS the_geom
FROM opengeo.tq_building;

-- Update the geometry_columns table
SELECT Populate_Geometry_Columns();

Resulting in the correct output: enter image description here

This process is also documented here.

Thank you for the help!

1
  • Why do you need to update geometry columns here?
    – geobar
    Commented Nov 10, 2015 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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