Well, its time for production now – remember that tutorial way back about building PostGRE on OSX? Well Ive just done it on Ubuntu Server, but from scratch, with all the GIS bells and whistles ;)

Lets tuck in…

First off, rather than me labour the point (anyone familiar with linux will breeze this part) check out this really usefull post – its got all the things you need for doing the actual compilation and install.

There are however a few Ubuntu related “gotchas”. Firstly, after you install GEOS, both ldconfig doesnt yet know about the new libs you’ve just installed at /usr/local/lib. To make it read the new libs you’ve gotta create a new file called ld.so.conf and stick it in /etc – fill it with the following:

/lib/
/usr/lib/
/usr/local/lib/
/usr/X11R6/lib/

Then to make it read in the new config, do this:

/sbin/ldconfig -f /etc/ld.so.conf

To check that its now reading your GEOS libs, run the following, and make sure you see a similar output (depending on the version of GEOS you installed)

$ /sbin/ldconfig -p | grep geos
    libgeos_c.so.1 (libc6,x86-64) => /usr/local/lib/libgeos_c.so.1
    libgeos_c.so (libc6,x86-64) => /usr/local/lib/libgeos_c.so
    libgeos.so.2 (libc6,x86-64) => /usr/lib/libgeos.so.2
    libgeos.so (libc6,x86-64) => /usr/lib/libgeos.so

Another helping sir?

OK – were good to go on that front. The key thing now is that we need to be able to add GIS functions and type handlers to any given database. Provided you set up the ldconfig stuff correctly, doing:

su -- postgres /usr/local/pgsql/bin/psql \
-f /usr/local/pgsql/share/lwpostgis.sql \
-d yourdbnamegoeshere

If this didnt work, you have a problem with your ldconfig! There are fixes out there which advise changing the lwpostgis.sql script, but in my personal opinion, thats not fixing the problem, thats just dirtying a fresh install.

Next, after I had everything up and running, it was puzzling me that there was no init.d script available (the google force was not strong today), I rummaged around in the source I downloaded, and found a startup-scripts dir in the contrib directory, result. Take that script, and bosh it into /etc/init.d and do:

sudo /usr/sbin/update-rc.d postgresql-8.2.5 defaults

Then you have a fully working PostGRE install with GIS sugar!!!

Hopefully this might help someone one day :)

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.

Step 1

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

Step 2

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:

Step 3

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 = ‘‘

to

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.

Step 4

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:

CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'

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….