postgis

The title may be the worst part of this post. Hopefully. Anyway – here’s what happened. I will explain it in all of it’s goofy detail with some hope that by the end you’ll do the same thing I did.

I’ve a client that is currently  making me push what I can do with QGIS/PostGIS. We hit a technical bump with data collection and to fix it they walked in with a new app they had purchased from some online app store that records data from their super accurate GPS Unit. The manufacturer of the GPS unit had one – but they found this scratched the itch more than the one they could have used….and the one they were using. My eye didn’t twitch much. I just cautioned against randomly doing things with your setup. I mean what if it makes a file you can’t import into your database. What if………

They have in postgresql a table that was more or less this: column1, column2, column3 . This app could create several data formats that ultimately me with: Foo1, column2, Bar3. You can’t just “copy and paste” the data in because the columns need to match. So I need to force it to match. No matter what trick I tried it wasn’t easy BUT some of them did work.

An Example of my thought process.  My python skills aren’t great – BUT – using the psycopg2 library, some ogr, and “the google” I was able to connect and change my data and push into Postgresql. More or less you parse out the columns and match them to the PostgreSQL table:

import psycopg2
import osgeo.ogr 
#import shapely

shapefile = osgeo.ogr.Open("/home/rjhale/gis/shapefiles/monument.shp")
layer = shapefile.GetLayer(0)

conn = psycopg2.connect("dbname='database' user='me' \ 
       host='localhost' password='itsasecret'") 
cur = conn.cursor()

for i in range(layer.GetFeatureCount()):
      feature = layer.GetFeature(i)
      descriptio = feature.GetField("descriptio")
      pos_x_sf = feature.GetField("Pos_x_sf")
      pos_y_sf = feature.GetField("Pos_y_sf")
      elev_z_sf = feature.GetField("Elev_z_sf")
      photos = feature.GetField("photos")
      local_date = feature.GetField("local_date")
      pdop = feature.GetField("pdop") 
      #Get feature geometry
      geom = feature.GetGeometryRef()
      #Convert geometry to WKT format
      wkt = geom.ExportToWkt()
      #Insert data into database, converting WKT geometry to PostGIS geometry
      cur.execute("INSERT INTO test.monument(descriptio, \ 
                   pos_x_sf, pos_y_sf, elev_z_sf, \
                   photos, local_date, pdop, geom) \
                   VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', \ 
                   ST_GeomfromText('{}',6447))"\
                  .format(descriptio, pos_x_sf, pos_y_sf, elev_z_sf, photos, \
                  local_date, pdop, wkt))

Holy crap. It worked. I really need to up my python game BUT – no one wanted to run something from cmd line. MAKE A PLUGIN! Which I considered. In my digging I found the refactor fields tool  in QGIS. It’s very similar to loading data into a database with ArcGIS for those of you stuck on that side of the fence.

So how does it work? It’s easy. Load your data that doesn’t quite match anything as the input layer. Load the table you want it to match in the “load fields from layer” part at the bottom. Click Load Fields.

So what just happened? Refactor fields took my shapefile and matched it to the columns in PostgreSQL. If it can’t “guess” you have the option to make it match by typing in the fields. It generates a new layer that I can “copy and paste” into the database or import into the database. For some of the data I’m having to clean out the fields – so things that don’t match in the shapfile I’m removing from the new layer using this processing tool. Which – maybe I need to add those fields to PostgreSQL. I really want this table stabilized before we start. Although I’ve not tried it I’m guessing this works with any layer you want – I’m just using it against postgresql. I also will say this probably won’t match projections – so get your projection worries figured out before hand – this just handles the attributes.

Right now I’m  looking at a model so they can ultimately just “ClICK SOMETHING”. We all like menus. Mostly.

Anyway – load your data. QGIS has many tricks to make your life easy and I had no clue. I did learn something on the Python side of life though and for that I’m happy even though it turned into a dead-end.