1

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.

5

From QGIS >> DB Manager >> DB Manager

enter image description here

enter image description here

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

  • I absolutely agree knowing the DB Manager is mandatory to work with PostgreSQL/PostGIS from within QGIS...but the crucial part is missing here, no? you should probably add that OP will end up with 15 tables in the DB from which the data still has to be 'normalized' and inserted into the final tables... – ThingumaBob Apr 28 '18 at 21:26
  • 1
    apart from that: good 1k rep day to you ,) – ThingumaBob Apr 28 '18 at 21:29
4

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.

  • 1
    btw., you didn't specify what to do with the geometries...*ogr2ogr* doesn't care, you'll end up with geometries in both tables (there'll be an fid column, too, I think)...drop them as needed, or set -unsetFid flag and/or -unsetDefaults, and don't forget to create an index on the geometries you want! – ThingumaBob Apr 28 '18 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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