[postgis-users] Geographic Binning - How to?

[pcreso at pcreso.com]

Hi Adam,

I have done similar exercises, looking at a variety of Postgis point attributes overlaid on a spatial grid. There are several ways of doing this with Postgres, Postgis or using other gridding tools like R or GMT.

Some of these are:

I have used a script to generate a Postgis table of cells as square polygons, of the desired size & covering the appropriate extent.

Then for each cell you can do things like select the average value of the points within each cell, as well as number of points/cell, min/max, etc of the points... basically just a group by.


Another approach I've used with some success is to create new columns of x,y coords for each point, by rounding them, effectively losing one digit of precision each time.

You can then group by x,y at whichever precision suits to effectively bin your data at steps of one decimal precision.


I have also used GMT for many such analyses, it has some useful gridding/binning capabilities, and ogr2ogr can generate GMT datasets directly from Postgis, & import the GMY XYZ output back into Postgis.

You might also look at R. This is a powerful stats/modelling tool that can  be used with Postgis, either by exporting/importing data, using R:gdal or the R ODBC driver to read data directly from Postgres/PostGIS, or by using PL/R to embed R functionality within Postgres.


To summarise, there are plenty of ways of doing this, both within Postgis and by using other FOSS applications that integrate with or work with Postgis/Postgis data.


Cheers,

  Brent Wood




--- On Sat, 12/27/08, Dylan Beaudette <debeaudette at ucdavis.edu> wrote:

> From: Dylan Beaudette <debeaudette at ucdavis.edu>
> Subject: Re: [postgis-users] Geographic Binning - How to?
> To: postgis-users at postgis.refractions.net
> Date: Saturday, December 27, 2008, 1:16 PM
> On Friday 26 December 2008, adam at spatialsystems.org wrote:
> > I'm not sure how to do Geographic Binning of data
> or if it's possible with
> > PostGIS.
> >
> > I have millions of GPS data points of RF signal
> measurements taken every
> > second from a car traveling in a major city.  I want
> to divide the data
> > into 30 meter sections and average the data points for
> every section.  So
> > when I drive through an intersection twice or overlap
> drive routes, the
> > data will show up as one point averaged out.
> >
> > Can someone give me a lead on how to geographically
> bin data out of
> > PostGIS?
> >
> > Thanks,
> > Adam
> 
> Sounds like a wardriving exercise. See ST_SnapToGrid() for
> "binning", then 
> aggregate on the (x,y) coordinates of the returned
> geometry.
> 
> Here is an example:
> 
> http://casoilresource.lawr.ucdavis.edu/drupal/node/485
> 
> Dylan
> 
> -- 
> Dylan Beaudette
> Soil Resource Laboratory
> http://casoilresource.lawr.ucdavis.edu/
> University of California at Davis
> 530.754.7341
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users