QGIS PostGIS – How to Create New PostGIS Table with Records and Geometries from Another Table

attribute-tablecopygeometrypostgisqgis

I have a bunch of PostGIS tables (one per county) containing polygons for all of the property parcels in that county. Each layer also includes several attributes like street address, property value, and acreage that I'm interested in keeping. Each layer also has several dozen columns that I don't need and want to discard. I want to merge all of the data I'm keeping from these tables into a single PostGIS table parcels with an additional county column, that looks like this:

enter image description here

I understand how to use INSERT/SELECT statements to take values from one table and insert into another, but I am confused about exactly what I need to do to copy the geometries from each county's layers along with the attribute data.

Am I supposed to just copy the geom column along with the columns I'm interested in keeping? In addition to the geom field, several of the county layers have columns called x_coord, y_coord, and others have columns with names like shape_area, shape_len, shape_star, etc … I'm wondering if I need to copy any of these over along with the geom field? Are any of these fields related to how PostGIS stores geometry data, or are these all just useless remnants from the ArcGIS tools that the counties used to generate these tables?

What exactly is the process for copying attributes + geometry data from one PostGIS table to another? I'm using QGIS and would like to know how to do it with DB manager, if possible, but I'd also like to know how to do if from the psql command line if anyone knows how.

Just to have a concrete example, let's say I have a table called jefferson_parcels that contains 40+ attribute fields, but I only want to copy the fields situs_addr, situs_city, cfmv_total, ttl_acres, land_use from jefferson_parcels and INSERT them into the parcels table in the fields parcel_addr, parcel_city, total_value, total_acres, land_use respectively, and set the county field equal to jefferson. Along with all of this attribute data, I want to copy over the geometry data for these parcels. How would I do this?

Best Answer

Data in geometry columns are independent from any other columns (what makes sense in terms of database design...) and are treated like any other data type.

You could use the following SQL to copy the data in your example:

    INSERT INTO parcels 
(parcel_addr, parcel_city, total_value, total_acres, land_use, county, geom)
      SELECT
        situs_addr
        , situs_city
        , cfmv_total
        , ttl_acres
        , land_use
        , 'jefferson'
        , geom
      FROM jefferson_parcels
    ;

See also this similar question on dba.stackexchange.com

Related Question