Union PostGIS tables turns blank

Question

I have a simple tasks that regarding combining multiple Postgis tables. Let's say I have 4 tables called tx_shape, ca_cd_1_shape,wy_shape, and ma_suffulk_co_shape. Two of which are states, one is a congressional district, and another is a county.

I want to combine them into one giant shape table. I tried using referring to this post, and ran

CREATE TABLE merged AS (
SELECT gid, geom FROM tx_shape
UNION
SELECT gid, geom from wy_shape
UNION
SELECT gid, geom FROM ca_cd_1_shape
UNION
SELECT gid, geom FROM ma_suffolk_co_shape);

But all it returns is a blank. The gid are displaying correctly but when I export it using pgsql2shp with

pgsql2shp -f "test_output" -h localhost -u username -P password my_db "select * from merged"

All I get is a blank, am I missing something here? It should be relatively straightforward.

Answer 1

you are trying to join 4 different geometries into a single shapefile, remember that the shapefiles only accept 1 kind of geometry (only points for instance). Also, PostGIS keeps a record of the tables geometries in the table 'geometry_columns'.

I recommend:

1. Create a new table with the specific geometry (for instance Point):

CREATE TABLE newtable (gid integer NOT NULL, geom geometry(Point,4326));

2. Populate it from the values from your tables:

insert into newtable(gid, geom) select gid, geom from tx_shape; insert into newtable(gid, geom) select gid, geom from ca_cd_1_shape; insert into newtable(gid, geom) select gid, geom from wy_shape; insert into newtable(gid, geom) select gid, geom from ma_suffulk_co_shape;

3. Export it as shapefile

pgsql2shp -f "test_output" -h localhost -u username -P password my_db newtable