0

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.

Community
  • 1
Minh
  • 611
  • 2
  • 9
  • 16

1 Answers1

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));

  1. 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;

  1. Export it as shapefile

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

pateto777
  • 400
  • 1
  • 4
  • 12
  • What if there are no overlapping ids to merge? All my gids are mutually exclusive – Minh Jul 26 '16 at 20:39
  • Also wouldn't there be a merging conflict with multiple gids? – Minh Jul 26 '16 at 20:50
  • I misunderstood your question, I assumed you need to use an unique id. If the gids are mutually exclusive only the ids from the first table will be show. Also, there would be a merging conflict. You just need to know that all the geometries your are joining have to be of the same class. I recommend to create a new table with a specific geometry (check the table geometry_columns) and populate it with the data from your 4 tables. – pateto777 Jul 26 '16 at 20:55
  • Gotcha, I tried another approach using CREATE TABLE merged (geom geometry); INSERT INTO merged (geom) SELECT geom FROM tx_shape; INSERT INTO merge(geom) SELECT geom FROM ma_suffolk_co_shape; etc. but no luck. – Minh Jul 26 '16 at 21:00
  • 1
    Try to define the geometry: CREATE TABLE merged( gid integer NOT NULL,
    geom geometry(Point,4326) );     – pateto777 Jul 26 '16 at 21:08