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

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

PostGIS and Multiprocessing


NYC taxi cab pickup locations for October, 2012

OK, let’s cut to the chase: I used Python’s parallel processing capabilities with Postgres to perform a spatial overlay on approximately 25 million taxi pickup locations (over 5 GB of data), and processed all of it in under 3 minutes!!  There.  Now you can decide if it’s worth your time to read this long post. Continue reading