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:
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
y_coord, and others have columns with names like
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
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?