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

[Aren Cambre]

Brent and Birgit,

Thanks to both of you for help on this. You helped lead me down a discovery
path, and I understand this stuff a lot better now.

It took a while to get the data usable because trial and error with
~12,000,000 records is a bit time-consuming. :-)

This is the query I ended up using, based on your models:
*
INSERT INTO "raw"."rm accuracy check"
select rm.rte_nm, rm.rm, "HA_Route", "HA_Milepost", "HA_County",
total_tickets_per_rm,
          avg(ST_Distance(ST_Transform(rm.the_geom,
3081),arrests.avg_location)), arrests.avg_location, arrests.arrest_locations
from
        (select "HA_Milepost", "HA_Route", "HA_County", gid,
count("HA_Milepost") as total_tickets_per_rm,
st_collect(ST_Transform(the_geom, 3081)) as arrest_locations,
st_centroid(st_collect(ST_Transform(the_geom, 3081))) as avg_location
         from raw."joint arrests" LEFT JOIN raw."geocoding"
 ON "joint arrests"."HA_Arrest_Key" = "geocoding"."HA_Arrest_Key"
 WHERE the_geom IS NOT NULL
         group by "HA_Milepost", "HA_Route", "HA_County", gid) arrests LEFT
JOIN
 gis.hip_reference_markers rm
on rm.gid=arrests.gid
group by rm.rte_nm, rm.rm, "HA_Route", "HA_Milepost", "HA_County",
arrests.avg_location, arrests.arrest_locations, total_tickets_per_rm;
*

Aren

On Wed, Apr 27, 2011 at 7:46 AM, Birgit Laggner
<birgit.laggner at vti.bund.de>wrote:

>  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><aren at arencambre.com>
> * wrote:
>
>
> From: Aren Cambre <aren at arencambre.com> <aren at arencambre.com>
> Subject: [postgis-users] Calculating variance of a set of points
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net><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://mc/compose?to=postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at postgis.refractions.nethttp://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/20110504/e8b9a88c/attachment.html>