do all of this in less than an hour using a modern computer and connection.

I’m going to talk about everything – what is it, how to download, how to install, how

to data display, how to do analysis, how to get data out, and finally a runtime comparison.

It’s a sad story. What is PostgreSQL? It is the world’s most advanced, open source

free relational database. It runs on all operating systems and has huge capacity so it’s a

really good database application in and of itself. It’s got this great spatial extension

called PostGIS which adds a layer onto the database that allows you to do all of the

things you can do from a common, GIS functionality and has a lot of other advanced features as

well. One of them, for instance, is that you use SQL, strangely. So you can have something

you select, a couple layers, some relationship between geometries, the geometries themselves,

and then some qualifier. So it’s a different kind of way of thinking about things. And

there are tons and tons of commands – here is just a smattering. There are hundreds that

allow you to create data, describe data, data analysis, data export, and so on.

Downloading? Simple. Just go to this website, find the link for your operating system, choose your

architecture, and it downloads a big 50 megabyte file, and then you’re ready to install.

And installation is quite straightforward. You’ll just follow all the prompts. The

only thing you do need to do is select a super user password from them. Basically you can

just keep hitting next, next, next so it’s very easy. Easier than other units.

Installing PostGIS, the layer that’s on top of PostgreSQL, there’s a stack builder you install. You

can literally just keeping hitting Next and 5 minutes later, you’ll have this software

installed. Since it is a client/server software, you have to do a little bit of monkeying with

the security settings, but there are tons of examples about this. Usually, you want

to have the server which is the client trust itself. And running the PostgreSQL is pretty

straightforward. There is a graphical user interface. But if you’re Harvey, you’ll

probably want to use the command prompt interface in PostgreSQL. If you want to create a spatial

database, two commands – one is “create database” that builds, basically, the container,

and then “create extension” that adds all the PostGIS functionality. You can do

that in the GUI and that will take a minute to two and a half. (audio lost briefly)…You

can take shp2pgsql and ogr2ogr. Here I’ve got the cities and census units for King County

and imported them into the database. And there are a lot of different clients you can use

to display PostGIS data. I use OpenJunk which has a lot of advantages that I won’t have

time to go into it. Basically, we’re looking at the cities and census units here. I’ll

give you an example of a simple intersection. This is SQL. I’m creating a dataset from

intersection of two different geometries from two different layers. And then I can simply display

that in the OpenJunk. The cool thing is you can do all this from memory. You don’t have

to create files to test your geo-processing. You don’t have to fill your drive with follow-up

or intermediate files. Here’s an example – the same intersection and creating buffers

on the fly. Getting data out? There are also two commands for ogr2ogr and pgsq12shp. Here

is this results of that intersection done on the fly. Made a shape file, and that’s

also displayed in ArcGIS. It’s also got additional functionality so there is raster

analysis and routing. Here’s an example of a website that will route you to these

two points using this as the backend. If you like programming, there’s a bunch of different

languages you can use. Since I’ve become more accustomed with R, I use R with its connection

capability with the database. Here’s an example of just showing counts and features.

Here’s the sad story: I did a comparison on the same virtual machine. It was 2 seconds

in ArcGIS versus 4 and 8 versus 50-something so it doesn’t run quite as fast as ArcGIS.

And I do like spicy food.