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 [email protected]:
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 /<[email protected]>/* wrote:
From: Aren Cambre <[email protected]>
Subject: [postgis-users] Calculating variance of a set of points
To: "PostGIS Users Discussion" <[email protected]>
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
[email protected]
</mc/[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
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
