How to transfer data from shapefiles into a PostGIS database?

Question

I have a project in QGIS that has 15 vector layers, each of which is from an ESRI shapefile, and represents the property parcels in a county in Washington State. Each file has an attribute table with different field names (for example, one has a field called owner and another has two fields for the same data called owner_first and owner_last).

I want to move this data all into one PostGIS database with the data normalized into consistent fields, with an additional county field to identify which county the data was from. Are there any QGIS built-ins or plugins for moving data from shapefiles into PostGIS, and doing whatever conversions are necessary to re-map the shapefile attribute table fields to the database schema?

I understand that it will be slightly different for each shapefile, based on the way that particular shapefile is structured, but I'm just trying to get a handle on the basic process. As a simple example to illustrate what I'm trying to do, suppose my PostGIS database was called parcel_data and it had a table called parcels with the following fields:

id         (Integer)   # primary key
parcel_no  (String)    # parcel number (string bc can be of form 0000123433)
owner_id   (Integer)   # a foreign key into owners table
address    (String)    # street address
value      (Float)     # property value
county     (String)    # name of county

and another table for owners:

id      (Integer)      #primary key
name    (string)       #owner's full name
address (string)       #street address
zip     (string)       #zip code

and I wanted to import data into these tables from a shapefile called mason_county.shp that had the following fields in its attribute table:

parcel           # insert into the parcel_no field in DB
owner_last       # last name of owner
owner_first      # first name ... concatenate w/ last & insert into "name"
street_addr      # the street address, inserted into "address" field
propvalu         # should be mapped to "value" field in DB
zip              # zip code - insert into "zip field"

So I want to insert all of these into the correct DB tables, along with the geometry/CRS/etc information, and then use "mason" as the value for the county field in the parcels table. How would I go about doing this? I am using QGIS, and can also use tools like ogr2ogr. Is there a way to do this from inside of QGIS? If there is no way to do it with QGIS directly or from command line with ogr2ogr, I am also open to suggestions using Python.

Answer 1

From QGIS >> DB Manager >> DB Manager

And you will have most of the option you need for your vector data to import into your database.

Answer 2

There are plenty of ways to get what you want.

I'd go for a two-liner with ogr2ogr in a for ... in ... do terminal script (assuming Windows); from a terminal window in the folder containing your shapefiles, run

for %f in (*.shp) do "path/to/ogr2ogr.exe" -f "PostgreSQL" PG:"host=<host> port=<port> dbname=parcel_data user=<user> password=<password>" -append -nln owners -sql "SELECT %f.owner_first || %f.owner_last AS name, street_addr AS address, zip AS zip FROM %f"
for %f in (*.shp) do "path/to/ogr2ogr.exe" -f "PostgreSQL" PG:"host=<host> port=<port> dbname=parcel_data user=<user> password=<password>" -append -nln parcels -sql "SELECT parcel AS parcel_no, o.id AS owner_id, street_addr AS address, propvalu AS value, trim(trailing '_county' from %f) AS county FROM %f, owners AS o WHERE %f.owner_first || %f.owner_last = o.name"

This should iterate twice over all .shp files in that folder, creating the owners table first and the parcels table second (to be able to refer the owner.id as owner_id, based on the same owner name). Also, this assumes a SERIAL PRIMARY KEY on both (already existent) tables' id columns.

I can't test right now, there may be spelling/syntax errors, but the general structure should be a good starting point. Also, this does not handle cases yet where there is only a owner name field (maybe add -skipfailures to both queries to simply do nothing if the processed shapefile has that attribute), since checking for existence of columns is not trivial.

Hope this helps.