Posts tagged ‘geo’

PostgreSQL geo locations with PostGIS

I been playing with some geo location data. Naturally when working with the data one needs to save it to a database for later retrieval and selection. If you happen to be using Postgres your in luck. You can add the PostGIS extension that will make your database OpenGIS compatible.
The most important thing is to have geo location aware database when dealing with geo data. That means that you can select objects using proximity right out of the database. It makes easy to figure out how far away different locations are, what are the nearest locations etc. If you don’t have geo location aware database you would have to figure out the proximity in code. Of course that would mean a lot of calculations and CPU cycles.

How I set it up on my Ubuntu / Mint linux box. First install PostGIS on the box then you want to add it to your database as below.

createlang plpgsql yourdatabase
cd /usr/share/postgresql/8.4/contrib/postgis-1.5
psql -d yourdatabase -f postgis.sql
psql -d yourdatabase -f spatial_ref_sys.sql

Then make sure PostGIS has been added to the database.

SELECT PostGIS_full_version()

Couple examples of usage.

To see how far away from my place ( Boulder ) some locations in the database are.
SELECT Distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) FROM location_geo;
Then get the five closest ones to my place
SELECT * FROM location_geo ORDER BY distance(geom, SetSRID(MakePoint( 40.0150, –105.2705 ),4326)) LIMIT 5;

You can even set boundaries, use polygons etc.