postgis

TN 911 Server Updates

rjhale · Aug 30, 2019 ·

The TN NG911 server got an upgrade this month with some quality assurance checks. After working with Henry County for the last bit we’re into more “what if” scenarios.

What if we ran checks for misspelled addresses or improperly labeled addresses?
What if we started filling out the fields we weren’t filling with out with our ArcGIS/Geodatabase setup?

Working with Chad Howard (911 GIS Guru for Henry County TN) we’ve started playing with data problems. One thing I learned is that the amount of quality assurance checks at the county level are lacking – not because anyone didn’t want to do it it’s just hard working with a basic arcgis license to do anything significant. That’s where postgis comes into play. I’ve got a total of three checks on the addresses planned with two implemented as of right now.

What if you accidentally add two address points with the same geometry? A red circle appears in one of the layers in your database (and in your QGIS session) alerting you to the mistake. What if you accidentally misspell an address street name and that doesn’t match a street? A blue circle appears. If you look at the example it looks like someone can’t spell. Actually the old setup was adding spaces to names. So instead of “Main” you had “Main “. That’s an easy enough fix we can do in a bulk edit.

I expect these next set of checks to slowly become irrelevant over the next bit as the client takes full advantage of QGIS’s forms for data entry. Once again we’re hitting spaces as opposed to nulls in the data in 80% of the cases. We should only have 4 pre-directionals – well at most 8 – but for this county they only have 4.

We can bulk edit the spaces away in the database. It’s nice to have this table sitting in the qaqc schema just to check occasionally but I do expect that to drift off in significance the more we use Fulcrum and QGIS’s Forms for data input. These checks check everything from street types, names, to directionals. Like I said – over time these start drifting off in significance but it’s nice for now.

Anyway – more quality checks are planned but we’re over he first hurdle to making this even more useful and easy.

TN NG-911 Server

rjhale · Jul 24, 2019 ·

Last year I had the chance to work with Henry County TN’s 911 group on a software migration project. I moved the organization off a basic commercial GIS product to an open source enterprise server. Overall it was a great project with a defined end goal and a lot of flexibility in making it happen.

Once the project ended I never stopped working on the database and the tools. Over the last few months I’ve had the chance to turn the database into something that is 100% compliant with the State of TN’s NG911 System. I present the TN NG911 Server.

What does it look like?

Fulcrum for Mobile
QGIS for a GIS Desktop
PostGIS/Postgresql for the database
Geoserver to provide OGC compliant services

The workflow is simple. Field Personnel go out and collect data using menus set up in Fulcrum.

Data is stored in PostGIS/PostgreSQL and edited with custom menus in QGIS. The database is served out through Geoserver as an OGC service.

Data is exported from the server at night to the the states ESRI database.

The bonuses to this approach:

100% TN NG911 Compliant
Cloud Deployment Or Onsite Deployment
Local TN Support for your organization
Can Interface with Computer Aided Dispatch Systems through managed Exports.
Quality Checks on your attributes
Topology checks for your data
Unlimited Desktop Deployments for everyone
Can work with TN’s LIDAR Data and TN’s Imagery Services.
Handles more than just the 3 data layers the state needs for NG911

Granted it’s not 100% open source as Fulcrum is a commercial product. Fulcrum will need to be purchased but it’s only a fraction of what you could be spending on software and it has more uses than just address collection.

The TN NG911 Server has a solid deployment that’s been in production a year. If you’re interested in integrating Open Source GIS into your 911 system you should at least look at this approach. Everyone needs good clean data.

Future plans have a plugin and more quality checks on the data. For Version 1 though this is very solid and very functional.

What about the NENA Standard? The TN Standard is not NENA Compliant! Well – stay tuned for more news.

FOSS4GNA 2019 – The Short Review

rjhale · May 2, 2019 ·

I tend to spend probably more effort than I should telling people “Go to a FOSS4G Meeting”.

I made my……(think..think… 2013, 2015, 2016, 2017, 2018, 2019) fifth North American Event. Six if you count Boston – but that was International but lets count it – so six.

I think this might be a record for me with consecutive conference attendance. I’m a bit burned out on conferences currently. Usually gis conferences are more of the same – except a FOSS4GNA event isn’t. You never know what’s going to happen. If you make enough of these you become part of the group and to be honest – I could sit in a central area and just talk to people for 3 days. Which I did a lot of….more than I will admit. We talk life, projects, and software.

FOSS4GNA tends to skip around the country – this year it was in San Diego. Which was funny because when I said “Hey I’m going to San Diego people would immediately blurt out ‘OMG You’re going to the conference‘”. Well – no. Not that one – I’m going to the fun one. Jeff Johnson hosted at the Marina Village Conference Center. It was actually a better facility than I gave it credit for at first. It was spread out so you had to be outside. What you ended up doing while walking from one talk to another was stopping and going “Hey I’m in Southern California”. You would at some point stop and sit and not worry about things. Jeff had food trucks swinging by the conference center during breaks – you just wander out and get something. Food was plentiful. Every night there was a social of some sort. You get more done at the socials and over food and drink than you do anything else.

I drifted into more LIDAR talks than normal. I’m a bit blown away at PDAL – I don’t use it for work (yet) but I do play around with it in my spare time. The work going into it and the use cases around it are exciting. I’ve been working with Geoserver more and I went to talks on the new releases that are coming.I bounced into a few QGIS talks. I sat and talked to a lot of people – some that I knew and some that I didn’t. It’s nice hearing what people are doing with FOSS4G software and why people are there. I ran into 3 people from the Southeastern US – and that was nice. I’m not alone in this area screaming into the void.

I did my talk on Henry County’s 911 Server and had some great conversations with people after it was over. It’s nice to see other municipalities drifting into the open source software realm.

I enjoy hearing Paul Ramsey’s Keynotes. He tends to look at the economic side of what we do – that’s an important thing for me. Especially because I make my living doing this and I’m always trying to give back. The synopsis of his talk: Open Source Geo is at it’s “Mid Life” with about 20 years of development. At mid life you sit around and look and there’s not much money for FOSS4G development. There’s plenty in implementation because we are all about selling solutions. If you want to check out the slides – here they are. What if you took your normal maintenance costs from your commercial software and threw it at an Open Source Project? Maybe hired a developer?

So – I know probably not the best conference summary but it’s hard to summarize the people. That’s really my main attraction to this conference. I can walk into a room of 500 and see people I know and miss people that aren’t there. I can catch up with friends – people are the most important part of this whole thing for me.

What about next year? Next year is the international conference in Calgary….

….and you should go. I’m going to do my best to get there. Why? People. Come join the community of Free and Open Source Software Community. If you missed out on FOSS4GNA 2019 you missed a good time. If you can’t make 2020 in Calgary keep your eyes open for the next FOSS4G-NA in 2021.

rjhale · Mar 20, 2019 ·

So I’ve been neck deep in Postgresql/Postgis which has been a bit fun but a bit maddening.

One problems I ran into was “What is this column?”. Generally if a client hands me the database I start working with it and do a few things but I don’t worry about their columns in the database. People keep weird things for no reason and I’ve learned to stop asking so many questions on why.

In this case the client had a PDF describing the standard they were working toward. They also had some extra fields they needed for other applications. So I found myself constantly cracking open the PDF or emailing them going “Why is this and what is this?”. If only there was some metadata….

…….but they have this in a PDF and it is good:

So the comment command to the rescue. I’m going to attach the definition to the field: comment on column oirid is ‘leave your comment here’;

Yeah I know that was a lot to add for a comment and I did it all from the magic of the psql command. If I take a look at the table from QGIS using DB Manager now:

At this point I know what the column is even if this is a bit overkill. The question is now “Can I get this easy enough to get the client to document their database on the columns I don’t know?”

Actually that’s easy with DBmanager. I can go in and edit an existing table:

From here I can edit a column and make comments. If I write up some directions the client can jump in and start documenting the more scattered things they have the in database. They can even leave a comment on the table to describe why you have the table.

I’m constantly overlooking DBManager and the things you can do with it. In this case you can edit an existing table and add comments to help with documenting your database/project. You can import data into PostGIS. You can toss SQL against the database.

PostgreSQL Hack

rjhale · Feb 21, 2019 ·

Many of you will look and go “OMG – Randy has a new hack for a database”. No – Randy is a hack at databases.

So about 4 or 5 years ago I was dropped into the idea of databases. I was working a job several thousands miles to the south and life was easier with a database as opposed to flat files. Up until that point I had flirted with databases and taken some online courses. I would make small databases but it wasn’t something I had to deal with day to day. I did deal with it indirectly – because at the time my employer was running ArcSDE on top of Oracle – BUT – as a lowly employee I wasn’t allowed to do much with it. That was the job of the admins. I made ESRI File based Geodatabases and was quite happy. Every now and then I’d even make Microsoft Access based ones.

So flash forward to this last year and I’ve been up to my neck in databases. A lot of you will shrug. You do amazing things with PostgreSQL and SQL server and it’s not that big of a deal. Last year I did my third big migration of flat Geo files into PostgreSQL/PostGIS. It was a fairly simple thing to do: QGIS, PostGIS, and I loaded Geoserver (but we still haven’t used it – I’m confident we will).

The client moved from a “ArcView/File based Geodatabse” environment to a multiuser database environment. They are a 911 and it’s a small county. They went from 1 person being able to work to 3 people working and viewing the data. I think currently they have 4 people adding data and updating data now. That’s a win.

What wasn’t a win was my moving of data into the database. File based Geodatabases don’t have a primary key. Maybe there is some way to deal with that these days – I don’t know. So they were using an extension called Attribute Assistant to deal with unique columns and sequences in ArcMap. I loaded the data and didn’t pay enough attention to what was happening.

To make a short story longer – I mucked up the unique column that the data had. I didn’t know it had to be assigned then never change – as explained “it just needs to be unique”. When I loaded it the Primary key and the Unique ID were off. Primary key was 4 and ID was 5. Look there is a primary key of 10 and a ID of 247. The unique ID had nothing to do with the county data – but everything with the state 911 database. So I calc’d the ID off the Primary key and that’s when things went stupid. So what magical GIS thing did I do? I didn’t.

I made a new index column.

update hc911.addresspoints set index = right(oirid, -6)::int;

Which ripped the number off the unique column and assigned it to a new field. I built a new sequence with a new incrementing value. I messed that up. I ended up redefining a new primary key. Fixed. No GIS knowledge applied – just databases.

How do you know you are tired? This is how!

Which – not a huge deal but for me a learning experience. I need to spend one day talking to the client and making out a path way before we migrate. Migrating data took 2 hours. Institutional knowledge probably would have taken 8 hours. The next one won’t have this problem. You’re going to have to talk to me a lot about your data and process. Way more than you want and that’s fine – we just need to talk.

Data is everything. To make everyone happy I set up a few cron jobs to dump data into Geopackage and Shapefiles using OGR. They’ve been quietly chugging along for 6 months now doing their work with no applause or fanfare. More people in the office are slowly going “hey you mean I can use QGIS to connect to the database? I can see the data?”.

I can say this whole move minus the ID hiccup has been a huge win for the organization. I was just lucky enough to be the guy to do it.

So if you’re an organization struggling with your commercial system – why not take a look at this side of the fence. We’re a lot of fun. If you’re wondering “Hey what should I learn these days” – I invite you to dive into PostgreSQL/PostGIS.

postgis

Contact