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.lagg...@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 pcr...@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 <a...@arencambre.com><a...@arencambre.com>
> * wrote:
>
>
> From: Aren Cambre <a...@arencambre.com> <a...@arencambre.com>
> Subject: [postgis-users] Calculating variance of a set of points
> To: "PostGIS Users Discussion"
> <postgis-users@postgis.refractions.net><postgis-users@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@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@postgis.refractions.nethttp://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
