Re: [postgis-users] Calculate variance of a multipoint

[Aren Cambre]

The more I think about it, is this a job for R? I know I need to start using
R at some point, just haven't begun yet.

Aren

On Wed, May 4, 2011 at 1:42 PM, Aren Cambre  wrote:

> Suppose you have a geometry type with a multipoint. How would you calculate
> the variance of the points in that multipoint?
>
> I looked through the PostGIS 1.5 function reference and am not coming up
> with any easy way.
>
> A hard way seems to be using st_centroid(multipoint) to find the
> multipoint's  center. From there, I can calculate the distance of each point
> from its center, and use that towards calculating the variance (each
> distance is squared, all squared distances are added together, then divide
> by number of points).
>
> I guess my ultimate need is to measure relative dispersion of multipoints.
> The multipoints that have the most dispersion are suspect, but I need a way
> of identifying which ones are like this.
>
> Aren
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Calculate variance of a multipoint

[Aren Cambre]

Suppose you have a geometry type with a multipoint. How would you calculate
the variance of the points in that multipoint?

I looked through the PostGIS 1.5 function reference and am not coming up
with any easy way.

A hard way seems to be using st_centroid(multipoint) to find the
multipoint's  center. From there, I can calculate the distance of each point
from its center, and use that towards calculating the variance (each
distance is squared, all squared distances are added together, then divide
by number of points).

I guess my ultimate need is to measure relative dispersion of multipoints.
The multipoints that have the most dispersion are suspect, but I need a way
of identifying which ones are like this.

Aren
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [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
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 
> * wrote:
>
>
> From: Aren Cambre  
> Subject: [postgis-users] Calculating variance of a set of points
> To: "PostGIS Users Discussion" 
> 
> 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://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

Re: [postgis-users] ST_Intersects

[Sean]

On May 4, 1:14 am, shreyes shiv  wrote:
> Dear all,
>
> I want to use the ST_Intersect on two tables. I want the resultant of the
> ST_Intersect to form a new table. How do i do that.
>
> Please help

Are you trying to create table that shows the overlap of the
geometries? Or are you simply trying to return the attributes of
intersecting geometries? It would help if you showed the table
definitions or simplified versions and a table definition of the table
you want to create.

Note: ST_Intersects returns True or False. It doesn't return a
geometry.

  Sean
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Sandro Santilli]

On Wed, May 04, 2011 at 11:59:35AM +0200, Birgit Laggner wrote:
> Am 04.05.2011 11:49, schrieb Sandro Santilli:
> >
> >... and I'd add ( a.id>  b.id ) in the WHERE condition.
> But why this, if table_a and table_b are different tables??

It's indeed wrong if "a" and "b" are different tables.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Birgit Laggner]

Hi Sandro,

Am 04.05.2011 11:49, schrieb Sandro Santilli:

On Wed, May 04, 2011 at 09:30:01AM +0200, Birgit Laggner wrote:


insert into new_table
select a.id, b.id, st_intersect(a.the_geom, b.the_geom)
from
table_a a
inner join
table_b b
on a.the_geom&&  b.the_geom
where st_intersects(a.the_geom, b.the_geom);


- st_intersect(a.the_geom, b.the_geom)
+ st_intersection(a.the_geom, b.the_geom)

yes!! this was a typo :-)

... and I'd add ( a.id>  b.id ) in the WHERE condition.

But why this, if table_a and table_b are different tables??

Regards,

Birgit.

--strk;

   ()   Free GIS&  Flash consultant/developer
   /\   http://strk.keybit.net/services.html
___
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

Re: [postgis-users] ST_Intersects

[Sandro Santilli]

On Wed, May 04, 2011 at 09:30:01AM +0200, Birgit Laggner wrote:

> insert into new_table
> select a.id, b.id, st_intersect(a.the_geom, b.the_geom)
> from
> table_a a
> inner join
> table_b b
> on a.the_geom && b.the_geom
> where st_intersects(a.the_geom, b.the_geom);
 
- st_intersect(a.the_geom, b.the_geom)
+ st_intersection(a.the_geom, b.the_geom)

... and I'd add ( a.id > b.id ) in the WHERE condition.

--strk;

  ()   Free GIS & Flash consultant/developer
  /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Birgit Laggner]

Hi Shreyes,

you have to create a new table and then insert the result of the 
st_intersect in the new table, e.g. like this:


insert into new_table
select a.id, b.id, st_intersect(a.the_geom, b.the_geom)
from
table_a a
inner join
table_b b
on a.the_geom && b.the_geom
where st_intersects(a.the_geom, b.the_geom);

Hope that helps,

Birgit.


Am 04.05.2011 07:14, schrieb shreyes shiv:

Dear all,

I want to use the ST_Intersect on two tables. I want the resultant of 
the ST_Intersect to form a new table. How do i do that.


Please help



--
shreyes shiv
email: shivshre...@gmail.com 
phone: 9557975780
IIRS(Indian Institute of Remote Sensing)
No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users