<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Aren, hi Brent,<br>
<br>
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:<br>
<br>
select l.ref_mark_id,<br>
avg(ST_Distance(l.location,a.avg_location))<br>
from loc_table l<br>
inner join<br>
(select ref_mark_id,<br>
st_centroid(st_collect(location)) as avg_location<br>
from loc_table<br>
group by ref_mark_id) a<br>
on l.ref_mark_id=a.ref_mark_id<br>
group by l.ref_mark_id;<br>
<br>
Regards,<br>
<br>
Birgit.<br>
<br>
<br>
<br>
Am 25.04.2011 03:22, schrieb <a class="moz-txt-link-abbreviated" href="mailto:pcreso@pcreso.com:">pcreso@pcreso.com:</a>
<blockquote cite="mid:2256.91619.qm@web33204.mail.mud.yahoo.com"
type="cite">
<table border="0" cellpadding="0" cellspacing="0">
<tbody>
<tr>
<td style="font: inherit;" valign="top">Hi Aren,<br>
<br>
If I understand the question, then off the top of my head,
untested & without getting into calculating spheroidal
distances instead of cartesian ones :-) ...<br>
<br>
given a table loc_table with columns:<br>
ref_mark_id<br>
location (point geometry)<br>
<br>
something like:<br>
<br>
select ref_mark_id,<br>
avg(ST_Distance(l.location,a.avg_location))<br>
from loc_table l,<br>
(select ref_mark_id,<br>
setsrid(makepoint(avg(x(location)),
avg(y(location))),4326) as avg_location<br>
group by ref_mark_id as foo) a<br>
where l.ref_mark_id - a.ref_mark_id<br>
group by ref_mark_id;<br>
<br>
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.<br>
<br>
HTH<br>
<br>
Brent Wood<br>
<br>
<br>
--- On <b>Mon, 4/25/11, Aren Cambre <i><a class="moz-txt-link-rfc2396E" href="mailto:aren@arencambre.com"><aren@arencambre.com></a></i></b>
wrote:<br>
<blockquote style="border-left: 2px solid rgb(16, 16,
255); margin-left: 5px; padding-left: 5px;"><br>
From: Aren Cambre <a class="moz-txt-link-rfc2396E" href="mailto:aren@arencambre.com"><aren@arencambre.com></a><br>
Subject: [postgis-users] Calculating variance of a set
of points<br>
To: "PostGIS Users Discussion"
<a class="moz-txt-link-rfc2396E" href="mailto:postgis-users@postgis.refractions.net"><postgis-users@postgis.refractions.net></a><br>
Date: Monday, April 25, 2011, 12:42 PM<br>
<br>
<div id="yiv375566477">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.
<div><br>
</div>
<div>There are many events at each reference marker.
<div>
<br>
</div>
<div>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.</div>
<div><br>
</div>
<div>How would I do that with PostGIS?</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>Aren</div>
</div>
</div>
<br>
-----Inline Attachment Follows-----<br>
<br>
<div class="plainMail">_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
ymailto="mailto:postgis-users@postgis.refractions.net"
href="/mc/compose?to=postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
href="http://postgis.refractions.net/mailman/listinfo/postgis-users"
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div>
</blockquote>
</td>
</tr>
</tbody>
</table>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>