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