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.

  • Your gid column will likely not be unique. – Vince Jul 26 '16 at 22:24
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

  • What if there are no overlapping ids to merge? All my gids are mutually exclusive – Minh Mai Jul 26 '16 at 20:39
  • Also wouldn't there be a merging conflict with multiple gids? – Minh Mai 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 Mai 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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