[postgis-users] Center of Points Collection
Paragon Corporation
lr at pcorp.us
Thu Sep 17 11:45:57 PDT 2009
If its any interest to anyone. We implemented a median aggregate function
in PostgreSQL a while back (just using SQL functions). Of course it also
was kind of geared towards our needs.
Take a look and see if this is of use.
http://www.postgresonline.com/journal/index.php?/archives/67-Build-Median-Ag
gregate-Function-in-SQL.html
For large numbers of records per grouping (if you are using 8.4) -- its
more efficient to use array_agg, median(array_agg(...))
Leo
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Simon
Greener
Sent: Wednesday, September 16, 2009 7:25 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Center of Points Collection
Chris,
Never thought my method was theoretically correct: just had a bit of fun
working out how one would do it given no database (except Oracle) has a
MEDIAN aggregate cf AVG.
I looked at your URL and decided I didn't have the time to look at
implementing it.
regards
SImon
On Thu, 17 Sep 2009 02:08:47 +1000, Chris Hermansen
<chris.hermansen at timberline.ca> wrote:
> A median in X will either yield a point in the set or the average of
> the two points bracketing the (theoretical) median. Same in Y. In 2D
> the medians of both will often produce a point that is not in either
> set, even in cases where there is not an interpolation going on. Also
> what does one do about duplicate coordinates?
>
> In the median there is clearly the concept of "half of the points
> being on one side of the median, half on the other side". It's this
> "sidedness" in 2D that makes me fret.
>
> Here is an example for which I can't conceive of a great solution:
>
> POINT(1 6),
> POINT(0 5),
> POINT(0 4),
> POINT(2 3),
> POINT(2 2),
> POINT(2 1),
> POINT(1 0)
>
> What is the median of that? Simon, what your propose (medians of x
> and
> y) should I think give POINT(1.5 3) if duplicates are only counted
> once and POINT(1 3) if not.
>
> Simon Greener wrote:
>> What would happen if you calculated the median of the x and y
>> ordinates instead of the average?
>>
>> WITH points As
>> (
>> SELECT st_collect(column1) as the_geom
>> FROM (values ('POINT(0 0)'),
>> ('POINT(0 1)'),
>> ('POINT(0 2)'),
>> ('POINT(1 0)')) as foo
>> )
>> SELECT astext(st_makepoint(avg(x),avg(y)))
>> FROM ( SELECT st_x(geom) as x, st_y(geom) as y
>> FROM (select (ST_Dump(the_geom)).geom as geom from points
>> as foo) as p
>> ORDER by st_x(geom) ASC, st_y(geom) ASC
>> LIMIT (select case when mod(ST_NumGeometries(the_geom),2) =
>> 0 then 1 else 2 end from points)
>> OFFSET (select case when mod(ST_NumGeometries(the_geom),2)
>> = 0 then ST_NumGeometries(the_geom)/2-1 else
>> (ST_NumGeometries(the_geom)-1)/2 end from points)
>> ) T;
>> -- where p=1 and n=N/2-1 when the number of non null rows N is even,
>> or p=2 and n=(N-1)/2 when N is odd.
>>
>> "POINT(0 1)"
>>
>> See
>> http://scorreiait.wordpress.com/2008/10/28/how-to-compute-a-median-in
>> -sql
>>
>> S
>> On Wed, 16 Sep 2009 05:25:29 +1000, Chris Hermansen
>> <chris.hermansen at timberline.ca> wrote:
>>
>>> Hmm on further thought I'm not completely clear on the definition of
>>> "median" in two dimensions, either. I found this interesting
>>> article on Google, including a way cool Java app for calculating and
graphing:
>>>
>>> http://www.tiac.net/~sw/2007/11/2D_median/index.html
>>>
>>> pcreso at pcreso.com wrote:
>>>> Hi guys,
>>>>
>>>> A bit more difficult, & way out in left field, but if you use PL/R
>>>> to create a median function for Postgres, you could build your
>>>> point from the median(X) & (Y) values instead of the average.
>>>>
>>>> Where this would actually lie obviously depends on the distribution
>>>> of the points. The centroid is most affected by (actually defined
>>>> by) outlying points, the avg somewhat less & the median less still.
>>>>
>>>> Of course once you have PL/R to play with, you have much more
>>>> flexibility to look at returning statistics from datasets than just
>>>> the median.
>>>>
>>>> Cheers,
>>>>
>>>> Brent Wood
>>>>
>>>>
>>>>
>>>>
>>>> --- On Tue, 9/15/09, Kevin Neufeld <kneufeld at refractions.net> wrote:
>>>>
>>>>
>>>>> From: Kevin Neufeld <kneufeld at refractions.net>
>>>>> Subject: Re: [postgis-users] Center of Points Collection
>>>>> To: "PostGIS Users Discussion"
>>>>> <postgis-users at postgis.refractions.net>
>>>>> Date: Tuesday, September 15, 2009, 8:51 AM Paul Ramsey wrote:
>>>>>
>>>>>> Faster than creating a multipoint is to recognize that
>>>>>>
>>>>> ST_Centroid()
>>>>>
>>>>>> is just going to return the center of the bbox of the
>>>>>>
>>>>> collection
>>>>>
>>>>>> anyways...
>>>>>>
>>>>> Unfortunately, Paul, ST_Centroid returns the center of mass, not
>>>>> the center of the bbox.
>>>>>
>>>>> SELECT astext(st_centroid(st_collect(column1))),
>>>>> FROM (values ('POINT(0 0)'),
>>>>>
>>>>> ('POINT(0 1)'),
>>>>>
>>>>> ('POINT(0 2)'),
>>>>>
>>>>> ('POINT(1 0)')) as foo;
>>>>> astext
>>>>> ------------------
>>>>> POINT(0.25 0.75)
>>>>> (1 row)
>>>>>
>>>>> Your second post, taking the avg of the x,y does seem to be the
>>>>> nice approach, and produces the same results as ST_Centroid - the
>>>>> center of mass.
>>>>>
>>>>> SELECT astext(st_makepoint(avg(st_x(column1)),
>>>>> avg(st_y(column1))))
>>>>> FROM (values ('POINT(0 0)'),
>>>>>
>>>>> ('POINT(0 1)'),
>>>>>
>>>>> ('POINT(0 2)'),
>>>>>
>>>>> ('POINT(1 0)')) as foo;
>>>>> astext
>>>>> ------------------
>>>>> POINT(0.25 0.75)
>>>>> (1 row)
>>>>>
>>>>> If Dustin is after the center of the collection, then something
>>>>> along your first suggestion might be more appropriate.
>>>>> (taking the center of the extents)
>>>>>
>>>>> Cheers,
>>>>> Kevin
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://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
>>>>
>>>
>>>
>>
>>
>
>
--
SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle
Database 10g Administrator Certified Associate; Oracle Database 10g SQL
Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE,
Manifold GIS, FME, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
Email: simon at spatialdbadvisor.com
Voice: +61 362 396397
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list