Happy PostGIS day!
Posted on 17 November 2011
Happy PostGIS day everyone!
Until about year ago, most of my work involved using proprietary GIS software, most of it ArcGIS. Today, almost 100 % of the work i do involve open-source software. Contributing much to this shift has without a doubt been PostGIS.
One of my main projects the last year have been the development of the PRIO-GRID project for the peace and conflict research arena. PRIO-GRID is a spatio-temporal grid structure constructed to aid the compilation, management, and analysis of spatial data within a time-consistent framework. It consists of quadratic grid cells that jointly cover all terrestrial areas of the world. Although PRIO-GRID was designed with peace and conflict research in mind, its potential applicability extends well beyond the study of civil war. What it really is about is to collect spatial data from diverse sources and combine these into a common unit of observation.
The first version of the PRIO-GRID was developed using ArcGIS. We quickly realized that for scientific purposes, ArcGIS did not cover the requirements we had for data replication, easy documentation of all processes we did and software capabilities. In mid 2010 we started discovering PostGIS, and quickly realized that we could create far more advanced queries that covered all of our needs. One example of this was when we wanted to calculate the distance from a set of centroids in the grid to the nearest neighbouring polygon belonging to a different country but still “touching” the departure point’s respective polygon.
In ArcGIS we would have to do this one polygon pair at the time.
In PostGIS we simply wrote this as:
SELECT a.gid, a.gwcode, a.gridyear, MIN(ST_Distance(a.centroid, b.geom))/1000 INTO borddist FROM gridcells a, countrypolygons b, countrypolygons c WHERE a.gwcode != b.gwcode AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) GROUP BY a.gid, a.gwcode, a.gridyear;
All in all. What i really would like to say is that PostGIS have helped us save time, money and some potential grey hair.
Happy PostGIS day!