• Skip to primary navigation
  • Skip to content

North River Geographic Systems Inc

Spatial Problem Solving

  • Home
  • About NRGS
    • Newsletter
  • Blog
  • Services
    • QGIS Support
    • Fulcrum Support
    • Spatial Analysis
    • Training
    • Image Analysis
  • Gallery
  • GIS Tutorials
  • Show Search
Hide Search

Open Source GIS

I didn’t know it was called Refactor Fields in QGIS

rjhale · Mar 2, 2018 ·

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.

Refactor Fields

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.

loaded refactor 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.

 

 

 

Docker for the Geo People

rjhale · Dec 24, 2017 ·

This might get lengthy. You have been warned. Docker people – settle down I’m new at this.

I dabbled in Docker  to get pgadmin4 up and running. A few months ago I tweeted (and I couldn’t find it) something derogatory about docker and virtual machines and what not. I was poked at for tweeting it. A few weeks after one person lamented why do the old school guys not embrace docker….because I’m old and that’s the way I likes it.

So something like 5 years ago I discovered Virtual machines. I was primarily running ESRI on everything and didn’t run anything in a Virtual Machine. Everything “just ran” and there was no need for one more complication in my life. One day I got a call and found myself going out of the country for a bit and I had been experimenting with dual booting my computer between windows/linux. Some more reading and I had loaded linux on my main workstation and moved my windows environment to a VM. I set up a VPN and suddenly everything is at my fingertips through SSH or VNC.

A couple of years after that – vagrant popped up on my radar. I could quickly deploy a ubuntu/debian server with fun things like a web server or a database or whatever I wanted. I could play with it and then delete it and adjust my VagrantFile to load more or less or compile software at startup.

Docker pops up. I’ve had a few false starts with it. When I started this I was determined I was going to write my own dockerfile. So I did. It didn’t work. I became irritated. I ended up getting one running. I threw a command against it. It left a container. So then I had images….and containers….and I did something else and I had two containers and I felt like I was junking up my computer. At one point I had like 15 containers and to me a VM seemed more natural. It’s a certain size. I know what is going to happen.  After RTFM’ing– I realized I didn’t have to “leave containers” all over the place. I didn’t have to keep an image. I could start and stop docker when I wanted.

So to really have some fun – I loaded the Kartoza DockerFile for PostGIS/Postgresql  (read the directions). In short I did this (and I’m on linux – but you can do this on windows): 

  1. docker pull kartoza/postgis
  2. sudo docker run –name “postgis” -p 25432:5432 -d -t kartoza/postgis
  3. psql -h localhost -U docker -p 25432 -d gis

I attached to it through the PSQL prompt and QGIS. I loaded data. It was fine. It was normal. If you think of it as a separate service running on your computer things get more “normal”. I have a lot of services I deal with every day from postgres to  ssh to dropbox etc. It’s just separate from the computer so you can’t break things. It’s self contained.

Maybe instead of a normal postgresql install I just load docker on my laptop and crank up Postgis/Postgresql when I need it. Maybe maybe maybe. I’m probably going to put pgadmin4 (through docker) on the postgis “server” I have running over in the corner. Maybe I get QGIS Server up and running in docker?

So I said it simplifies everything In my head……….once I “learn” enough to be proficient with it. Here’s my old man yelling at cloud moment.

ONE MORE THING I HAVE TO LEARN. Which may be why my old school comrades aren’t so quick to embrace this. I’m still flailing with GIT  on a good day.  So 18 years ago I had my first ArcGIS Desktop Experience. 18 YEARS AGO. I love this side of FOSS4G though – I have to learn something new. Which is why I sometimes hate the FOSS4G side of life – I can’t get comfortable and just go “Hey look I’m clicking buttons”. Some days there is comfort in clicking buttons – but I do the clients a disservice if all I can do is click buttons.

So I’m not going to a docker conference. I’m not going to write software and push it into docker for the masses. This isn’t a hammer for every nail I see……..BUT it is something you need to be paying attention to over the long haul. It’s another tool in your gistoolbox to make your life a bit more fun.

One Day FOSS4G Meeting in Knoxville – May 19th 2017

rjhale · Mar 15, 2017 ·

Did I mention it was free? I just wanted to post an update here to let people know this is happening.

This meeting is growing organically – which is how it should be. We have people preparing to come from Alabama, Florida, and Georgia to meet and give talks. Right now we’re at something like 6 or 7 talks. This is one day only. I’m hoping we get enough momentum to do this again – so far so good.

Website: https://wiki.osgeo.org/wiki/Knoxfoss4g20170519

So far we have talks on LIDAR from 2 different people. Also talks on QGIS. Talks on Leaflet. It’s going to be fun and informative. I’m planning on running QGIS 3 for the group to show the upcoming changes. We’ve got one person going over the OSGEO Live Distro.

We are digging for sponsors and as of right now we have about $350 collected for the effort.

Sponsors so far:

  • Location Tech
  • North River Geographic Systems, Inc.
  • University of Tennessee Department of Geography
  • UT County Technical Assistance Service

Anyway – get a ticket: https://www.eventbrite.com/e/foss4g-in-tennessee-tickets-32330056068

Email me if you want to give a talk. It’s going to be fun and did I mention free? Because it’s free.

FOSS4G 2017 Workshops now open

rjhale · Feb 8, 2017 ·

Workshop Submittal is now open for submittals for Boston FOSS4G 2017

Workshop Info For Boston:

  • Workshops will be held on August 14th and 15th at the Harvard Center for Geographic Analysis (CGA) in Cambridge, MA
  • Workshops are scheduled in 4 hour blocks, but a limited number of full-day (8 hr.) workshops will be considered
  • Compensation for a 4 hour workshop is $650, which is the cost of an Early-Bird registration for FOSS4G
  • Workshops will be BYOD (Bring Your Own Device) for attendees. FOSS4G will provide bootable OSGeo Live USB sticks and remote desktop access to cloud-hosted machines.
  • In the past, workshops have included topics related to the following, but the committee is open to other topics that are relevant to the FOSS4G community and will generate interest :
    • QGIS/PyQGIS
    • PostGIS
    • GeoServer
    • MapServer
    • Web Mapping
    • GRASS GIS
    • GDAL/OGR CLI Tools
    • pgRouting
    • Using OSGeoLive
    • GeoPython
    • GvSig
    • Aerial Mapping and Point Clouds/LIDAR/Photogrammetry
    • Using open source GIS in a cloud environment

Important Dates:
Call for Workshops Closed: March 13, 2017
Workshop Selections Announced: March 20, 2017
Detailed Workshop Program: April 3, 2017
Workshops Take Place at Harvard: August 14/15, 2017
Here is a link to the workshop page for the submission form and further details: http://2017.foss4g.org/workshops/

PostGIS and Me.

rjhale · Jan 27, 2017 ·

It’s about time to start the PostGIS talking….

Way back when I had a project and the company to which I was subbing tossed me into FOSS4G way over my comfort level. I had used open source operating systems off and on – most notably Linux (Ubuntu/Fedora/Debian) and OpenBSD.  The project was PostGIS heavy and to be honest I was lost. You had geometry columns and other columns and these stupid foreign keys. ST Commands. Don’t get me started on those. “What do you mean I just can’t go ‘buffer this table’?”

Anyway – so started a slow-moving exploration of PostGIS. I realized this last weekend I still have a lot to learn and I have more to unlearn.

My first dive into PostGIS was a database for a forestry client. It took a week to combine and shove 100 shapefiles into PostGIS. Over the weeks I started normalizing the database. It’s been running pretty flawlessly for almost 2 years. The only thing I haven’t done is to set up some scripts to ‘age’ the tree stands. I never received all the approximate planted dates and I know how I want to do it but no incentive to make it happen now.

As of right now I’ve got 4 projects running in PostGIS. It’s a bit scary – I’m not that great at it. I am learning. I keep threatening to do a class on PostGIS to go with the QGIS one and I haven’t gotten quite there yet.

Anyway – expect more rambling up here on PostGIS. Some will be insightful. Some will be boring. Some will just be “here is what I did to fix this problem”.

Like today.

One of my clients wanted to go open source on their GIS. So they did. They downloaded all the parts and using QGIS they started importing data into PostGIS. If they messed something up they would pull the data out into a shapefile. Edit it. Delete the PostGIS table. Push it back into PostGIS. Before they called me wanting help they were inching along. Not a GIS person in the house – just a group of people wanting answers. That’s how easy this has gotten. Let that sink in for a minute. It’s a Geographic Information System and no one was “trained to do it”. It’s all open source also. Just read instructions, download, install, and start working.

Today I started normalizing the database. I moved everything locally and just started developing scripts to “fix” things. Which is another side to this I need to get better with – scripting. Why did we not normalize it before? They were busy building it out and I walked in at the half way mark. It is eye-opening how much time I spend making it right vs starting out that way. Of course I would argue that they’ve been working and answering questions with some degree of accuracy so it’s right (mostly).

I also started a database for a non-profit of their water testing points. In PostGIS. This may be the first time I’m doing this right from the start – which actually makes this 5 databases I have running in PostGIS.

Anyway – back to the problem. One of their point tables had kept the primary key but lost its sequencing. So when they added a point they had to manually go “42” for the primary key vs the database just pushing it forward one digit. It’s not the end of the world – just a terrible way to live in 2017. Ninety Five points total with the largest number being 100. Somehow they skipped 5 numbers.

So you know what – I rebuilt the sequence:

create sequence <sequence> increment 1;

alter table alter column id set default nextval(‘sequence’::regclass);

alter sequence <sequence> owned by <schema.table.id>;

select pg_get_serial_sequence(‘schema.table’, ‘id’);

select setval(pg_get_serial_sequence(‘schema.table’, ‘id’), max(id)) from <schema.table>;

So the next added point gets a primary key value of 101.

…and I know that’s a lot of stuff for one problem. Also – it has nothing to do with GIS – just databases. So I turned into a DB Admin for a moment. Sorry to the DB Admin I cussed so heavily back in 2007. I knew not what I was doing.

To end it – some of you reading this are going “OMG – We know about PostGIS you are so far behind”. Some of us grew up with flat files and other insanity and it’s only been the last bit for me that I’ve seen the light (so to speak) on data. Treat your data like a database.

With all of this PostGIS knowledge so now what am I doing? Building triggers (what’s a trigger?) and doing a lot of searching and reading. I’m Doing some analysis of the data. Fixing it. I’m importing Fulcrum data at times.

What am I getting out of all of this? Clients who get Answers. A decent knowledge base on databases. Some personal satisfaction which (truth be told) can get a bit thin when you’re consulting. At least with me.

More on PostGIS coming……

 

 

 

 

  • Page 1
  • Page 2
  • Page 3
  • …
  • Page 34
  • Next Page »

Contact

  • (423) 653-3611
  • info@northrivergeographic.com

Copyright © 2018 · Monochrome Pro on Genesis Framework · WordPress · Log in

  • Home
  • About NRGS
  • Blog
  • Services
  • Gallery
  • GIS Tutorials