[postgis-users] Calculating variance of a set of points

[pcreso at pcreso.com]

Hi Aren,

If I understand the question, then off the top of my head, untested & without getting into calculating spheroidal distances instead of cartesian ones :-) ...

given a table loc_table with columns:
ref_mark_id
location (point geometry)

something like:

select ref_mark_id,
          avg(ST_Distance(l.location,a.avg_location))
from loc_table l,
        (select ref_mark_id,
                   setsrid(makepoint(avg(x(location)), avg(y(location))),4326) as avg_location
         group by ref_mark_id as foo) a
where l.ref_mark_id - a.ref_mark_id
group by ref_mark_id;

Should work. ie: generate a virtual table as a query which provides the average X/Y point for each marker, then join this to the original table by marker to average the distances between each point & the average point, grouped by marker.

HTH

Brent Wood


--- On Mon, 4/25/11, Aren Cambre <aren at arencambre.com> wrote:

From: Aren Cambre <aren at arencambre.com>
Subject: [postgis-users] Calculating variance of a set of points
To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
Date: Monday, April 25, 2011, 12:42 PM

I have a table with events. Each event happened at a listed reference marker on a highway, and it also has latitude and longitude as recorded by an observer of the event.
There are many events at each reference marker.


I want to check the precision of the latitude and longitude for events recorded at each reference marker. Roughly, I would collect all latitude/longitude points at each reference marker, then I would want to see the average distance between each point and a centroid of all the points.


How would I do that with PostGIS?
I understand how to convert latitude/longitude to a geometry type, but I am not clear how to do the rest without iterating through each point individually.


Aren

-----Inline Attachment Follows-----

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110424/23dd1bdd/attachment.html>