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: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Friday, December 26, 2008 11:45 PM To: [email protected] Cc: Dylan Beaudette Subject: RE: [postgis-users] Geographic Binning - How to? On Friday 26 December 2008, [email protected] wrote: >On Friday 26 December 2008, [email protected] 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 [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
