How to install PostgreSQL with PostGIS spatial on OSX
Recently spatial calculations have fascinated me. Hardly what most people would consider fascinating, but hey!
Most of the modern enterprise (I use that term loosely as someone is bound to comment complaining that some of this list are not ‘enterprise’ enough) RDBMS have spatial GIS extensions. This allows them to conduct exteamly complex calculations about size, posistion and location in a three dimensional way - pretty freaking cool! Common databases that have GIS extensions are:
PostGRE - PostGIS
Oracle - Oracle spatial
DB2 - IBM Spatial
MySQL - MySQL spatial entensions
there are lots more… just google for your specific backend.
Anyway… for this article we’ll focus on PostGRE PostGIS, and running it on OSX.
You will need to (download a whole bunch of frameworks from here)http://www.kyngchaos.com/software/unixport/frameworks and run the installer. That will give you some of the base libs and frameworks that PostGIS requires; such as GEOS, GDAL etc etc
Add a new user on your system and make sure its short name is postgres - this is the user you will run the database server as. See below from my box:
Download the latest PostGRE installer from here. This will load up PostGRE in /usr/local/pgsql; while your there i’d install the startup item so you dont have to load the server manually evertime you restart your mac.
Once installed you need to do some editing of the conf files. For me, I just needed a local development server, so I loaded up /usr/local/pgsql/data/postgresql.conf and changed the line
#listen_addresses = ‘‘**
listen_addresses = ‘‘**
This will ensure that our server binds to all the interfaces the box has. If you want to be more specific, just enter the IP of your machine.
Next, you need to edit the pg_hba.conf file to add a generic host like so:
host all all 0.0.0.0/0 md5
This lets anyone from any IP/subnet connect as any user. I must stress that this was for development purposes so you might want to be a bit more explicit for a production enviroment.
Now you have PostGRE installed and configured, run the PostGIS installer - this will PostGIS to your install. We now need to create a new database and add the spatial extensions to it so that our querys will work… but before we can do that we’ll need to enable PL/SQL like so:
<code>CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'</code>
Then, from PgAdmin3, open the query tool, and browse to /usr/local/pgsql/share/lwpostgis.sql - run that query, and dont worry about the output (unless its an error!) and refresh the view in PgAdmin3, you should then see the functions list gone from 0 for 600+ - thats all your spatial extensions loaded up ready for use.
That should be it - check out Boston GIS for more on GIS querys and so on - its exteamly complex and out of scope for this article. Happy geocoding people….