PostGIS team press release:
PostGIS 2.0.0 Released

The PostGIS development team is super excited, can hardly believe that they are actually doing this, aren’t maybe even sure that they are ready to make this kind of commitment, not so young, and not when we have so much more living to do, but:

PostGIS 2.0.0 is complete and available for download.

The development process for 2.0 has been very long, but has resulted in a release with a number of exciting new features.
• Raster data and raster/vector analysis in the database
• Topological models to handle objects with shared boundaries
• PostgreSQL typmod integration, for an automagical geometry_columns table
• 3D and 4D indexing
• Index-based high performance nearest-neighbour searching
• Many more vector functions including ◦ST_Split
◦ST_Node
◦ST_MakeValid
◦ST_OffsetCurve
◦ST_ConcaveHull
◦ST_AsX3D
◦ST_GeomFromGeoJSON
◦ST_3DDistance

• Integration with the PostgreSQL 9.1 extension system
• Improved commandline shapefile loader/dumper
• Multi-file import support in the shapefile GUI
• Multi-table export support in the shapefile GUI
• A geo-coder optimized for free US Census TIGER (2010) data

We are greatly indebted to our large community of beta testers who valiantly tested PostGIS 2.0.0 and reported bugs so we could squash them before release time.

And also we want to thank our parents for making PostGIS possible.

Yours,

The PostGIS development team

For more information and download, see:
http://www.prio.no/CSCW/Datasets/PRIO-Grid/

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!

In my work we need to combine a lot of spatial data from diverse sources. This includes socio-economic, physical and climatic data. Our latest requirement is to be able to import NetCDF files containing climate model data into our PostGIS database. NetCDF (Network Common Data Format) is a multidimensional spatio-temporal data structure with the usual x, y spatial coordinates and a z coordinate or value in addition to the t variable which represents time since some start date.
Our approach is to read the NetCDF in Python using the Scipy.io NetCDF module. This makes it possible to read the NetCDF file into Python and then output it to PostGIS using the psycopg2 module.

I will paste my python script below and comment on the various parts. Please feel free to replicate this script, but please site the project paper:

Tollefsen, Andreas Forø; Håvard Strand & Halvard Buhaug (2012) PRIO-GRID: A unified spatial data structure. Journal of Peace Research 49(2): 363-374.

Script:
# NetCDF to PostGreSQL database
# Delaware precipitation and temperature data. From NetCDF to database table
# Requires Python2.6, Postgresql, Psycopg2, Scipy
# Tested using Vista 64bit.

db1.commit()
cur.close()
db1.close()


So, what this scripts does is to read the NetCDF file into Python. Then loops through the month, lat, lon, creates a list of the respective values for each lat and long and insert these into the PostGIS table. This NetCDF file includes 1308 months of data. With 259200 observations per year. It takes approximately 30 seconds per month.

SELECT gid, CAST(AVG(((foo.geomval).val)) AS decimal(9,3)) as avgimr FROM (SELECT p.gid, ST_Intersection(r.rast, p.cell) AS geomval FROM imrgrid2p5m r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast)) AS foo WHERE (foo.geomval).val >=0 GROUP BY gid ORDER BY gid;

To explain, we select the vector grid cell’s gid, and the average of (foo.geomval).val FROM the Intersection of the infant mortality raster and the vector grid. Hence, the geomval is a list of each pixels intersection with the vector grid cell. This geomval contain both the actual geometry of this intersection and the value of the pixel. The we apply a where clause of where the cell and raster intersects since the raster is tiled. We also do not want to include negative values (missings) since infant mortality rate is not negative. Then we group the query by the grid cell’s gid since we are aggregating the average pixel value.

Then, when querying raster to vector with values, you get the correct result.
The below query tests this by querying the average pixel values within a quadratic polygon of 0.5 x 0.5 decimal degree’s.

SELECT gid, AVG((foo.geomval).val)
FROM (SELECT p.gid, ST_Intersection(ST_SetSRID(p.cell,4326), ST_SetSRID(r.rast,4326), 1) AS geomval FROM mountain r, priogrid_land p
WHERE ST_Intersects(ST_SetSRID(p.cell,4326), ST_SetSRID(r.rast,4326),1)) AS foo
WHERE gid = 183230 AND (foo.geomval).val >= 0
GROUP BY gid
ORDER BY gid;

Result
gid;avg
183230;0.404999998294645

From their website:
“We developed Natural Earth as a convenient resource for making custom maps. Unlike other map data intended for scientific analysis or military mapping, Natural Earth is designed to meet the needs of production cartographers using a variety of software applications. Maximum flexibility is a goal.”

Go to Natural Earth Website

Reference: http://hansvandermaarel.wordpress.com/