Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
What's in the geometry_columns table for opengeo.london_merged? –  Micha Feb 8 '13 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? –  Robin Hawkes Feb 8 '13 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. –  Robin Hawkes Feb 8 '13 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 Feb 9 '13 at 19:42
add comment

1 Answer

up vote 2 down vote accepted

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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