Virtual layers with QGIS – wait a second, this is starting to look like Manifold…

If you are like me, you love writing spatial SQL. But what can you do if you have a geodatabase, some shapefiles, or any other data format? Well, you can always take those data formats and import them into Postgres or SQLite, and then issue the SQL for the results. Sure, it works, but it’s an extra step, and you no longer have access to the desktop GIS tools.

Well, with QGIS, you can drag and drop any data format into your project. And, with the DB Manager, you can refer to those layers as Virtual Layers, allowing you to issue SQL on them. Take a look at this video to see how issuing SQL on disparate data sources is a breeze.

I still think Manifold has the best integrated SQL engine tied to their GIS, but virtual layers in QGIS is seriously compelling, especially with the drag-and-drop capabilities of bringing in the different data sets.

if you want to learn more about spatial SQL or QGIS, check out my courses on Learning the FOSS4g Stack: QGIS Desktop, and Learning the FOSS4g Stack: Spatial SQL with Postgres and PostGIS.

Easy Bivariate Maps with QGIS, SQL, and the DB Manager

Previously, I blogged about creating bivariate maps with Postgres, and Arcpy. Pretty cool stuff, check it out!

But, to be honest even though Postgres was super easy to do as far as the SQL was concerned, I was always bothered by the rigmarole involved in making maps. First, you had to move the data into Postgres. Then, you had to run the query and create a new table. Then, you could visualize the map.

But, here is a super easy way to make the maps, using QGIS and the DB Manager. In case you didn’t know, QGIS can link to Virtual Layers. That is, layers in the table of contents can be accessed with the DB Manager, and they are treated like a layer connected to an external database. However, in the case of Virtual Layers, you’ll be using SQLite and SpatialLite to perform SQL.

So, this short little video will show you how to create a bivariate map on-the-fly with QGIS and the DB Manager. To me, this is one of the coolest features introduced in QGIS.

if you want to learn more about spatial SQL or QGIS, check out my courses on Learning the FOSS4g Stack: QGIS Desktop, and Learning the FOSS4g Stack: Spatial SQL with Postgres and PostGIS.

SQL or Models in QGIS – you decide

In my previous post, I showed how to create a model in QGIS – I think that was pretty cool! But, as I say over and over, SQL is even cooler! I am republishing my video on using QGIS to create a model, but also including a video on how to accomplish the same this with SQL in Postgres and PostGIS.

For background, here was the task: clip my parcels with a particular watershed (Cascadilla Creek), and then clip the result with the floodzones. With that result, I then wanted to summarize the spatial (area) and attribute data (assessment values by property class).

I think QGIS handled it pretty well. But, the video takes about 15 minutes to explain. In about a minute, we can accomplish the same thing with a small SQL query! Check it out, and tell me what you think.

These videos are part of my courses in Learning the FOSS4g Stack. If you would like to learn how to use free and open source GIS products, you can check out Learning the FOSS4g Stack: QGIS Desktop, and Learning the FOSS4g Stack: Spatial SQL with Postgres 11 and PostGIS. My next course Learning the FOSS4g Stack: Python and Geospatial will be out in October.

Models in QGIS

Did you know that QGIS can design graphical models, similar to the way Model Builder in ArcGIS works? The following video is taken from my course Learning the FOSS4g Stack: QGIS Desktop.

The capabilities are still a little raw, but you’ll see in this short video that you can indeed create, document, and run a complete GIS model that you simply draw on your screen.

As I mentioned, this video is part of an entire course on QGIS, called Learning the FOSS4g Stack: QGIS Desktop. You can check out the course here. In fact, you can check out all of my courses at

Big Data Geo-Analytics with Postgres workshop Evaluation

Great fun at the Maryland State GIS conference (@tugis) where I had over 20 people attend my Big Data Geo-Analytics with Postgres workshop. And, like other workhops I’ve taught at Tugis (see here, here, and here), the evaluations were really strong. I should remind you that this was not your run of the mill workshop. This was really advanced stuff where we discussed indexing, parallel processing, multi-processing, the central limit theorem, and also processed gigabytes of data. I warned people beforehand that this was not an introductory workshop. And, people responded perfectly. Everyone in attendance was prepared for the material, and that is what made the workshop go so well. The full workshop results are here. But, as far as highlights, here are the main take-aways:
I love it when professionals taking my workshop feel as though it has value to their career. The reality is, why take a workshop that won’t help you in your career? I’m so happy that people see this as valuable to their career.
This response was great. Usually, I get about 30% of the people to give the workshop a 10/10 (I get other 9s and 8s, of course). But in this case, 50% of the people gave this a 10/10. That is really huge. So, I know that I am teaching this content in the right way. When asked what they liked best, some of my favorite positive quotes are:
  • Technical and Detailed. Great teacher explanation. Really good!
  • Practical advice, in-depth enough to really learn something useful (most one-day workshops do not provide as much useful info and advice as this one).
  • The optimization of the database and processing (parallel processing in particular).
  • Learning about Postgres and the ability to run sql queries rather than run step by step in ArcGIS
  • Using Postgres to utilize data organization and data manipulation was great insight. It showed me postgres is a great alternative to SQL server or Oracle
  • Art knows his material and keeps the class engaged. Lots of new information.
  • The discussions on multiprocessing, indexes, and using statistical estimation were most useful to me.
When asked to provide ways to make the workshop better, these were some of my favorite quotes:
  • Nothing – good job. Thanks
  • Slower pace of the lessons. It was like drinking water out of a firehose.
  • Make it two days, or a week long, or a full semester!
  • I thought it was pretty good as is, I can’t think of anything off hand.
  • Have the materials available ahead of time for review. Would be useful to go deeper into how to use this at work and what we need to get started. Nothing – good job. Thanks
  • Slower pace of the lessons. It was like drinking water out of a firehose.
  • Make it two days, or a week long, or a full semester!
  • I thought it was pretty good as is, I can’t think of anything off hand.
So there you have it. Another successful workshop teaching GIS professionals about big data analytics. If you want to learn more about free and open source GIS, whether its QGIS, Postgres/PostGIS, GDAL, Geoserver, or Python and SQL, take a look at the courses I offer through Finally, I want to start offering this big data analytics workshop with Postgres and PostGIS during the year. I would be happy to come to your city or GIS conference to teach the class. Just send me a note, and we can work out a way to get me to your area to introduce GIS professionals to more FOSS4g contents.

Bivariate Choropleth Maps with Arcpy

In my previous post, I showed how to prepare the data for a bivariate choropleth map using PostGIS and QGIS. I also indicated that there is a website that shows an ArcGIS tool to do it. But, this actually turns into a good opportunity to illustrate some Python, and how to create the bivariate data using Arcpy.

Arcpy is certainly not as terse as SQL, but it does get the job done, and rather easily. We just have to think about the project a little differently. The code below is a Script tool that I created.

import arcpy, math, numpy
fc = arcpy.GetParameterAsText(0)

numrecs = int(arcpy.GetCount_management(fc).getOutput(0))

fields = arcpy.ListFields(fc, "bimode")
if len(fields) != 1:
    arcpy.AddField_management(fc, "bimode", "text", 3)

f1 = arcpy.GetParameterAsText(1)
f2 = arcpy.GetParameterAsText(2)
fields = ['bimode',f1,f2]

var1 = arcpy.UpdateCursor(fc, sort_fields=f1)

for row in var1:
    row.setValue("bimode",str(int(math.ceil((float(i) / float(numrecs)) * 3.0))))

var2 = arcpy.UpdateCursor(fc, sort_fields=f2)

for row in var2:
    row.setValue("bimode",row.getValue("bimode") + "." + str(int(math.ceil((float(i) / float(numrecs)) * 3.0))))
Continue reading

Easy bivariate map with Postgres

A friend recently asked me about the cool looking bivariate maps produced with ArcOnline, lamenting that the capability seemed lacking in ArcGIS. Well, it turns out that ESRI has a .dll you can use, and there is a good article here. So, if you want to create these great looking maps in ArcGIS, it shouldn’t be a problem. The website will allow you to download the .dll, and you can also watch the video on how to use it. Well worth the time.

So of course that got me thinking: could we make the same map with spatial SQL. Well, sure, and it is super easy. If you want to get spun up on what these 9 color bivariate choropleth maps are, and the theory behind it, have a look at this great site. Josh does a great job explaining how this works, but it is a little cumbersome if you want to pump out map after map. But, with a very little bit of SQL, you can easily pull it off.

Let’s start with our data: I have a Postgres table of United States County boundaries, with the attributes percentobese and percentdiabetes, along with a FIPS code and a geometry column.

To prepare the data for the map, we simply issue this SQL query:

ntile(3) over (order by percentobese) || '.' ||
ntile(3) over (order by percentdiabetes) AS bimode
INTO qlayer
FROM ushealthrisk

yes, that’s it. Really.

I’m not kidding. We’re done, folks. Go home, nothing left to see.

Well, if you insist on reading, I’ll tell you what the SQL does, and how to actually visualize the data.

Continue reading

Ready for a new career?

I just got off the phone with a very large developer. I want to call him a GIS developer, but really that’s not was he does. Rather, he is a spatial solutions developer. According to him, when we talk about GIS, we are mostly talking about a particular software product, or products. He feels like the term GIS pigeon holes him. In his world, he solves really large spatial problems, and thus, doesn’t think in terms of GIS, but rather spatial solutions. Who am I to argue with him, they have hundreds of spatial scientists working for them.

It turns out his company moved their entire spatial analysis tasks to FOSS4g. He was calling me because the guy that oversees a lot of their FOSS4g work said he was inspired by the courses I have on That was humbling, for sure. But what really struck me was in the middle of our conversation when talking about the courses I teach at the University, he said:

Art, if you had 9 or 10 students graduating with those FOSS4g skills, I could set them up with a job in under two weeks.

In fact, he said they, and others like them, need hundreds of new employees. It was astounding to me to hear about all the really cool spatial work going on out there, without the term GIS being used, and how much of it was using FOSS4g, and how much of it was being used in really large organizations that would never even talk about it publicly.

It made me think about the courses and workshops I offer, and wanting to help create a path for professionals to move from traditional GIS to that of spatial data analysts with FOSS4g experience. I know in the past that people have taken my courses (i.e. Python for FOSS4g, PostGIS, Geoserver, etc.) and gotten spun up to get an interview doing spatial database work, and even obtain a job in the field.

So, the picture below shows the FOSS4g courses I offer, and if you are willing to disappear for a few weekends or evenings, you can get spun up on all these technologies and be ready to move into the exciting FOSS4g world.

Six FOSS4g courses offered by to help you gain the skills to move into a high performing spatial analysis field

I’m hoping that he might be willing to share a little more publicly, as I’d like to see students cycle through these 6 courses (and my upcoming Big Data Analytics with FOSS4g) and then hopefully walk into some new careers. I’m actually testing this approach with a couple of people now, so we’ll see how much they can learn in a month or two. All in all, I’m almost up to 10,000 students overall, and the ratings for the courses are 4.3 out of 5!

Engaging students with multiuser editing, and FOSS4g

A friend is doing some research on estrogen and estrodial in water samples in Delaware. We have about 50 sub-watersheds that show the upstream contributing area for each water sample. We’ve done some regression analysis to see if we can find any correlation of estrogen with landscape parameters, like land use.

Geography students collaborating on data collection for a research project. Notice there are no windows in this lab – this is the best way to get students to want to go to graduate school so they don’t have to work like this again!

So far, we haven’t been impressed with the results. Nonetheless, I’m not ready to give up, so I thought:

what if there is a relationship with the number of poultry houses upstream of each sample.

The bad news: we don’t have a layer of poultry houses in Delaware.
The good news: I have a bunch of eager students who want to learn GIS.
The solution: Let’s use a lecture to teach students about multi-user, simultaneous editing with Postgres and PostGIS, and have them do it.

So that’s what we did. On Wednesday, I took 18 of my Advanced GIS students, and introduced them to an 8 minute video that would step them through digitizing all the poultry houses in the watershed. We had Postgres running on the teaching computer, and QGIS on 18 workstations in the classrooms. Our basic setup looked like this (with 18 QGIS workstations, of course):

I then had to instruct my students how to do the digitizing. Since people catch on at different paces, the best thing was to just use this video:

After 1h 40m, the students had around 1,000 poultry houses digitized! And, as a learning experience, they had the opportunity to see how an enterprise class database could be stood up in a matter of minutes to facilitate mult-user digitizing. It blew their mind to realize that after 2 hours, we had actually put in 36 man hours of digitizing. They had never used QGIS before, and it only took an 8 minute video to spin them up on the project!

This was easy to do, excited the students to be exposed to multi-user editing and open source GIS, and accomplished an important task for a research project that an individual student is working on. Everyone wins!

I hope this inspires you to come up with some creative ways to introduce GIS concepts to your class.

If you want to learn more about how to build an enterprise GIS with open source tools, check out my courses on