[postgis-users] Geographic Binning - How to?

[Paragon Corporation]

Adam,

For snapping in meters, the first thing that comes to mind is  transform
your points to UTM spatial ref, then snaptogrid, and then transform back to
longlat.

So say your points are in 4326 (WGS 84 long lat) - then you would rewrite as
follows  - where the utmzone function I am borrowing from the wiki.

http://postgis.refractions.net/support/wiki/index.php?plpgsqlfunctions
(utmzone functon takes a point geometry and figures out the srid for the
utmzone the geometry falls in). It assumes your geometries are marked with
an SRID .  If not you can use ST_SetSRID(the_geom,4326) to add that meta
data to your geometries.

Also check out
http://postgis.refractions.net/documentation/manual-svn/ST_Transform.html

So revised (using new naming convention of PostGIS functions)

SELECT
  ST_X(drive_test_data.grid) AS x,
  ST_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
      ST_Transform(ST_SnapToGrid(ST_Transform(best_ec.geometry,
utmzone(best_ec.geometry)), 1),4326) AS grid,
      best_ec.ec as rssi
    FROM
      best_ec
  ) AS drive_test_data
GROUP BY
  x,
  y;

Hope that helps,
Regina
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
adam at spatialsystems.org
Sent: Friday, December 26, 2008 11:45 PM
To: postgis-users at postgis.refractions.net
Cc: Dylan Beaudette
Subject: RE: [postgis-users] Geographic Binning - How to?

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








_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users