SQL for GIS Table Query – Introduction

Structured Query Language (SQL) is a very common, very generic language in computer systems. It has implementations in lots of different brands of databases like Oracle and Postgres, Ingres and Spatialite. All these use a very similar syntax.

So, if you can understand the syntax in one database system, then the move to a different brand of database should not be too difficult. I hope that you’re getting the message that it’s important that you learn this very generic language. And for the queries that most people make, you only need to use SQL at a very simple level.

Using SQL for GIS table query is a very common practice once you become familiar with GIS and you’re using it often.

What Is The Structure Of SQL?

At its simplest, the structure of an SQL query goes…

“Select those rows from a table where the value of a column in that table meets some condition that you specify”.

The concept of SQL also exists in Spreadsheets. So let’s look at Excel.

The Filter button under the Data tab in Excel is a simple implementation of SQL. The screen capture on the left shows the dialog that results in those towns in Massachusetts with a population of greater than 600000 people being selected (right screen capture).

There’s 2 columns in this table that represents all towns in Massachusetts. One called TOWN2 and one called POP2010. The implementation of SQL in Excel is accessed via the filter icon. Similar functionality also exists in Open Source office programs such as OpenOffice. So, we’re going to “Select those rows where the value of POP2010 is greater than 600,000”. Boston, is the only town that it relates to. In Natural Language (non computer-speak) you would say “show me the towns in Massachusetts where the population is greater than 600,000”. Excel selected one of the 351 towns in Massachusetts.

In a GIS, SQL selects the relevant row in the table AND the geography relating to that row

The big difference between a GIS and a Spreadsheet or a database table is that a GIS also selects the “geography”. In a GIS, once you’ve selected the geography, you can perform other operations on your selection as well. This combination of spatial and attribute query is one of the things that makes GIS so powerful! More on that in other posts!

Questions or comments, let us know in the section below.