16

I am looking to merge a number of individual tables into a new table in PostGIS. This is an easy task when working with Shapefile data, but I am unsure how to do this in PostGIS.

I think I use Append, but I am unsure even where to start.

Improve this question

1 Answer 1

Reset to default
24

Assuming attributes are identical in all original tables and the geometry type is exactly the same in all tables, you can either

  1. create the (empty) table first, then use INSERT INTO...SELECT... FROM to get all the data from each of the original tables into the merged one.
  2. Create the new table from one big UNION statement.

For 1 it might go:

CREATE TABLE merged (id serial primary key, attrib1 integer, attrib2 varchar(15),....);
SELECT AddGeometryColumn('merged','geom',<SRID>,'<FEATURE_TYPE>,'XY');
INSERT INTO merged (attrib1, attrib2, ...., geom) SELECT attribA, attribB,...,geom FROM table_1;
INSERT INTO merged (attrib1, attrib2, ...., geom) SELECT attribA, attribB,...,geom FROM table_2;

and so on...

For option 2:

CREATE TABLE merged AS( 
SELECT attribA, attribB,...,geom FROM table_1
UNION 
SELECT attribA, attribB,...,geom FROM table_2
UNION
.... 
);
SELECT Populate_Geometry_Columns('merged'::regclass);
Improve this answer
6
  • Thank you Micha, I will try this. Yes, all the tables have the same attribute and they are the same geometry type. Thanks again...
    – Ryan Garnett
    Jun 13, 2012 at 16:46
  • I have tried and I am getting an error associated to the XY. I know I am putting in the wrong thing, as I am unsure as to what I should be using there. Here is my query and the output response. Any help would be great.
    – Ryan Garnett
    Jun 13, 2012 at 18:49
  • CREATE TABLE pbear_pp (id serial primary key, entityname varchar(40)); SELECT AddGeometryColumn('pbear_pp','geom','-1','polygon','XY'); INSERT INTO pbear_pp(entityname, geom) SELECT entityname, geom FROM nts250k.nts250k_043e_poly; INSERT INTO pbear_pp(entityname, geom) SELECT entityname, geom FROM nts250k.nts250k_043f_poly; ETC ETC...
    – Ryan Garnett
    Jun 13, 2012 at 18:49
  • NOTICE: CREATE TABLE will create implicit sequence "pbear_pp_id_seq" for serial column "pbear_pp.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pbear_pp_pkey" for table "pbear_pp" ERROR: invalid input syntax for integer: "XY"
    – Ryan Garnett
    Jun 13, 2012 at 18:51
  • 1
    use 2 instead of 'XY' as that argument specifies the dimensions.
    – lynxlynxlynx
    Jun 13, 2012 at 19:21

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