1

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?

Improve this question
0

1 Answer 1

Reset to default
5

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

Improve this answer
1
  • Thanks - like I said in the original post, I already know how to copy data from one table to another and had actually come across the post you mentioned, and tried something similar to the SQL query you wrote. It seemed to be working (just selecting geom along with the other columns), but I wasn't sure if there was some subtle problem I was causing by not copying over the x_coord, shape_area, longitude, etc fields as well, and I couldnt' find any official documentation on how to copy tables like this, or whether geom contained all of the geometry data.
    – J. Taylor
    Commented May 1, 2018 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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