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……
