[postgis-users] Geographic Binning - How to?

[adam at spatialsystems.org]

On Friday 26 December 2008, debeaudette at ucdavis.edu wrote:
>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


Thanks for the hint.  This isn't wardriving but measuring RF propagation
of an antenna.


Below is what I came up with.  I see that "1" in ST_SnapToGrid() is in
decimal degrees.

How can I input that in meters and have it still be accurate around the
globe?  
I think "distance_spheroid" may be the answer but how would that be
applied?


SELECT
  X(drive_test_data.grid) AS x,
  Y(drive_test_data.grid) AS y,
  max(drive_test_data.rssi) AS max_rssi,
  count(drive_test_data.rssi) AS rssi_count
FROM
  (
    SELECT
      SnapToGrid(best_ec.geometry, 1) AS grid,
      best_ec.ec as rssi
    FROM
      best_ec
  ) AS drive_test_data
GROUP BY
  x,
  y;


Thanks,
Adam