Phil: So I’m going to show you this in 5 minutes but you should actually be able to
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.