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

For all the good things about XMPie, one of my niggles has been the lack of support for PostGRE SQL. I guess thats pretty understanderble; most of there client base use SQL server and other such MS products to some degree. There are times when id really like to use PostGre SQL as the backend and it can be a bit annoying. Anyway…thats another story…. for now were just going to look at implementing the OLEDB conectivity onto the servers to make it talk to our datasource.

Before we begin please note that it is critical to make sure that your PostgreSQL server can accept connections from your host IP. Otherwise you will get an error detailing that windows could not initialize the OLEDB provider. So, for those not familiar with PGSQL, check your postgresql.conf and pg_hba.conf….

Step 1

Ok, firstly you will need to install the OLEDB driver onto the server (and all extension serves where applicible)…

Download the OLEDB driver files from here - its currently in beta so do bear that in mind. Once downloaded, unzip it and copy libpq.dll and PgOleDb.dll to your windows\system32 folder.

Next, to register those new files as sources, go Start > Run and type

regsvr32 PGOLEDB.DLL

All being well you should see a sucsess box - if not, then i guess check your spelling against the DLL :)

Step 2

Now, provided you got your working OLEDB driver up and running in the previous step (be sure to test this with a UDL script) you should be able to add the database into the xmpie dashboard using a dsn something like this:

Provider=PostgreSQL OLE DB Provider;Password=<pass>;
User ID=root;Data Source=127.0.0.1;Location=demo;Extended Properties=&#8221;&#8221;

I am going to be doing some testing to see if this is any quicker than SQL Server (fingers crossed it will be) - stay tunned readers!

Give me a shout if you dont seem to be able to make this work (for whatever reason)