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

[Birgit Laggner]

Hi Aren, hi Brent,

I would only add to Brent's suggestion, that you could use st_centroid 
of the collected multipoint instead of calculating average x and y 
points for each reference mark - perhaps like this:

select l.ref_mark_id,
           avg(ST_Distance(l.location,a.avg_location))
from loc_table l
inner join
         (select ref_mark_id,
                    st_centroid(st_collect(location)) as avg_location
          from loc_table
          group by ref_mark_id) a
on l.ref_mark_id=a.ref_mark_id
group by l.ref_mark_id;

Regards,

Birgit.



Am 25.04.2011 03:22, schrieb 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
>     </mc/compose?to=postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> 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/20110427/57f981cb/attachment.html>