Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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. Any help would be greatly appreciated. I think I use Append, but I am unsure even where to start.

Thanks in advance.

share|improve this question
add comment

1 Answer 1

up vote 9 down vote accepted

(Pre-flight-check: are attributes identical in all original tables? Is the geometry type 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);

HTH, Micha

share|improve this answer
    
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 '12 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 '12 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 '12 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 '12 at 18:51
1  
use 2 instead of 'XY' as that argument specifies the dimensions. –  lynxlynxlynx Jun 13 '12 at 19:21
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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