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.
Stack Exchange network consists of 181 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.Visit Stack Exchange
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.Sign up to join this community
Assuming attributes are identical in all original tables and the geometry type is exactly the same in all tables, you can either
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);