Re: [postgis-users] Calculate variance of a multipoint
Aren, Your purposed approach sounds reasonable to me. You can do it all in one query like: select c.gid, sum(c.dist*c.dist)/count(*) as variance from ( select b.gid, b.cent, st_distance(b.geom, b.cent) as dist from ( select a.gid, (st_dump(a.the_geom)).geom as geom, centroid(a.the_geom) as cent from ( select 99 as gid, 'MULTIPOINT(1 2,2 3,3 4,4 5)'::geometry as the_geom union all select 88 as gid, 'MULTIPOINT(1 2,2 3,3 4,4 5,3 5,9 9)'::geometry as the_geom ) as a ) as b ) as c group by gid order by variance desc; You should be able to replace the select...union all select ... with your table of multipoints. -Steve W On 5/27/2011 6:19 PM, Aren Cambre wrote: Did anyone have thoughts on this? :-) Aren On Wed, May 4, 2011 at 2:12 PM, Aren Cambre mailto:a...@arencambre.com>> wrote: 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 mailto:a...@arencambre.com>> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Calculate variance of a multipoint
Did anyone have thoughts on this? :-) Aren On Wed, May 4, 2011 at 2:12 PM, Aren Cambre wrote: > 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
Re: [postgis-users] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Fri, May 27, 2011 at 09:28:57AM -0700, Paul Ramsey wrote: > I'm finding it difficult to have the regression test and also the > NOTICE in isvalid and also have the differential behaviour for > different versions of GEOS. One is going to have to go. I can't > ensure the "reason" I provide in my short circuit to match the reason > GEOS is going to give. What you can do is _not_ using tickets.sql but a dedicated file, maybe bugXXX.sql and bugXXX_pre_geos33.sql, and switch between the two from the Makefile. --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] Seeking Funding for Faster PostGIS Indexes
One of the eye-opening talks of PgCon last week was the presentation from Oleg Bartunov and Teodor Sigaev on their work on spatial partitioning indexes in PostgreSQL. Oleg and Teodor are the maintainers of the GiST framework we use for our r-tree, and are proposing a new framework to allow quad-tree and kd-tree implementations in PostgreSQL. http://www.pgcon.org/2011/schedule/events/309.en.html The upshot is, this new approach is as much as 6-times faster than the r-tree (at least for points). If you're interested in seeing PostGIS indexes get vastly faster, consider funding this project. Get in touch with me directly for details. http://blog.opengeo.org/2011/05/27/pgcon-notes-3/ P. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Dot Density idea
On Fri, May 27, 2011 at 10:53:58AM -0600, John Abraham wrote:
> create or replace view dot_cnt as
> select the_geom, polygon_id, (quantity_column/100)::integer as numpoints
> from original_data;
>
> select dot_density('dot_cnt',
> 'the_geom',
> 'polygon_id',
> 'numpoints');
>
> alter table dp rename to save_it_for_future_use;
>
> And here are the functions:
It'd be nice if you could make your function accessible on the postgis
wiki, so it's easier to find it.
--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] Infinite loop in st_intersects - because of incorrect data out of st_transform?
On Thu, May 26, 2011 at 11:00 PM, Sandro Santilli wrote: > On Thu, May 26, 2011 at 11:46:34AM -0700, Paul Ramsey wrote: >> Like so? >> >> http://trac.osgeo.org/postgis/attachment/ticket/987/inf2.patch > > A few points: > > 1: GEOS-driven ST_IsValid raises a NOTICE containing the reason, home-made > version should do the same. > 2: I see you're not handling nan, just infinite. Is that intentional ? > 3: GEOS-3.2 also survive the inf/nans in GEOSValid, why checkin for 3.3+ ? Because the isvalid hooks work in conjunction with the lower hook, which is filtering at 3.3+. Turning off the isvalid hook at 3.2 just means you get caught one level deeper. For consistency, having it all at 3.3 is what's needed. > 4: Expecting the error in tickets.sql makes the whole test file > pass or fail based on the version of GEOS you built postgis against. > > --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] Dot Density idea
Just to follow up on this, here is the code we (Abdel-Rahman M. Muhsen and I)
finally got around to writing. We found a function called randompoint which
just creates random points within the bounding box of the geometry from
Alexandre Sorokine.
http://sorokine.blogspot.com/2011/05/postgis-function-for-random-point.html (I
see he's updated it 4 days ago to be better for multi-polygons! We don't have
his updates yet.)
Then we wrote a function called dot_density which creates a table called dp
which has a point geometry for each point in the polygon. If there are
negative numbers in the point count, it creates the positive number of points
but flags them in another column. (We often plot dot density maps with red dots
for decreases and blue dots for increases.)
There are still a few improvements we'll likely make. We'd like to create the
geometry column using the addgeometrycolumn function. We'd like to specify the
output table name as a function parameter. There was also a good idea to use
Halton sequences or other "pseudo-random" sequences, instead of truly random
points, and Martin Davis implemented a few of the other "pseudo-random" ideas
in JTS
http://lin-ear-th-inking.blogspot.com/2010/05/more-random-points-in-jts.html .
Other suggestions are welcomed.
This is how we typically use it:
create or replace view dot_cnt as
select the_geom, polygon_id, (quantity_column/100)::integer as numpoints
from original_data;
select dot_density('dot_cnt',
'the_geom',
'polygon_id',
'numpoints');
alter table dp rename to save_it_for_future_use;
And here are the functions:
CREATE OR REPLACE FUNCTION randompoint(geom geometry)
RETURNS geometry AS
-- from Alexandre Sorokine
$BODY$
DECLARE
maxiter INTEGER := 1000;
i INTEGER := 0;
x0 DOUBLE PRECISION;
dx DOUBLE PRECISION;
y0 DOUBLE PRECISION;
dy DOUBLE PRECISION;
xp DOUBLE PRECISION;
yp DOUBLE PRECISION;
rpoint Geometry;
BEGIN
-- find envelope
x0 = ST_XMin(geom);
dx = (ST_XMax(geom) - x0);
y0 = ST_YMin(geom);
dy = (ST_YMax(geom) - y0);
WHILE i < maxiter LOOP
i = i + 1;
xp = x0 + dx * random();
yp = y0 + dy * random();
rpoint = ST_SetSRID( ST_MakePoint( xp, yp ), ST_SRID(geom) );
EXIT WHEN ST_Within( rpoint, geom );
END LOOP;
IF i > maxiter THEN
RAISE NOTICE 'number of interations exceeded max';
END IF;
RETURN rpoint;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION randompoint(geometry) OWNER TO "usrPostgres";
-- Function: dot_density(text, text, text, text)
CREATE OR REPLACE FUNCTION dot_density(geom_table text, geom_col text, zone_col
text, num_of_points_col text)
RETURNS SETOF record AS
$BODY$
DECLARE
counter integer:=0;
tazrec record;
pointrec record;
result record;
num_points integer:=0;
np integer :=0;
begin
DROP sequence if exists randpnt_id;
CREATE sequence randpnt_id;
DROP TABLE IF EXISTS dp;
CREATE TABLE dp
(
gid integer PRIMARY KEY,
ser integer,
"zone" integer,
decrease_or_increase integer,
the_geom geometry
);
for tazrec in EXECUTE 'SELECT ' || zone_col || ' as geom_col , ' || zone_col ||
' as zone_col, '|| num_of_points_col || ' as num_of_points_col FROM ' ||
geom_table Loop
RAISE INFO 'Treating zone: %' , tazrec.zone_col;
num_points = tazrec.num_of_points_col;
IF num_points !=0 THEN np := num_points/abs(num_points);
ELSE np=0;
END IF;
EXECUTE 'INSERT INTO dp SELECT nextval(''randpnt_id'') as gid,
generate_series, '|| tazrec.zone_col || ', ' || np ||' , randompoint(the_geom)
FROM ' || geom_table || ', generate_series(1, '|| abs(num_points) ||') WHERE
'|| zone_col || '='|| tazrec.zone_col ;
END LOOP;
RETURN ;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION dot_density(text, text, text, text) OWNER TO postgres;
On 2010-05-06, at 3:10 AM, strk wrote:
> ST_RandomPoinsOnSurface(geometry, numpoints) would be an interesting
> function indeed. Sounds like a good job for GEOS/JTS.
>
> --strk;
>
> On Mon, May 03, 2010 at 10:49:32PM -0600, John Abraham wrote:
>> One of the things I miss about using ESRI's GIS is the ability to do
>> dot-density maps. Within a polygon, the number of dots is proportional to a
>> value, and the dots are randomly placed. I find it useful to be able to
>> present several data values at once (e.g. blue dots for population, red dots
>> for employment).
>>
>> I also find that it is a more intuitive way of scaling for zone size than
>> dividing the value by the area of the zone. That is, the count of the dots
>> represents the actual number, but the density of the dots represents the
>> density of the number. So I don't have to decide whether to divide the
>> value by the area of the polygon to plot density: both the absolute number
>> and the density are easily visible.
>>
>> Since my open-source GIS viewing systems (mostly QGIS and Mapserver) won't
>> plot dot-density, I've done without.
>>
>> But today I realized that I can build these on the
Re: [postgis-users] Infinite loop in st_intersects - because of incorrect data out of st_transform?
http://trac.osgeo.org/postgis/attachment/ticket/987/inf3.patch On Fri, May 27, 2011 at 9:28 AM, Paul Ramsey wrote: > I'm finding it difficult to have the regression test and also the > NOTICE in isvalid and also have the differential behaviour for > different versions of GEOS. One is going to have to go. I can't > ensure the "reason" I provide in my short circuit to match the reason > GEOS is going to give. > > On Thu, May 26, 2011 at 11:00 PM, Sandro Santilli wrote: >> On Thu, May 26, 2011 at 11:46:34AM -0700, Paul Ramsey wrote: >>> Like so? >>> >>> http://trac.osgeo.org/postgis/attachment/ticket/987/inf2.patch >> >> A few points: >> >> 1: GEOS-driven ST_IsValid raises a NOTICE containing the reason, home-made >> version should do the same. >> 2: I see you're not handling nan, just infinite. Is that intentional ? >> 3: GEOS-3.2 also survive the inf/nans in GEOSValid, why checkin for 3.3+ ? >> 4: Expecting the error in tickets.sql makes the whole test file >> pass or fail based on the version of GEOS you built postgis against. >> >> --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] Infinite loop in st_intersects - because of incorrect data out of st_transform?
I'm finding it difficult to have the regression test and also the NOTICE in isvalid and also have the differential behaviour for different versions of GEOS. One is going to have to go. I can't ensure the "reason" I provide in my short circuit to match the reason GEOS is going to give. On Thu, May 26, 2011 at 11:00 PM, Sandro Santilli wrote: > On Thu, May 26, 2011 at 11:46:34AM -0700, Paul Ramsey wrote: >> Like so? >> >> http://trac.osgeo.org/postgis/attachment/ticket/987/inf2.patch > > A few points: > > 1: GEOS-driven ST_IsValid raises a NOTICE containing the reason, home-made > version should do the same. > 2: I see you're not handling nan, just infinite. Is that intentional ? > 3: GEOS-3.2 also survive the inf/nans in GEOSValid, why checkin for 3.3+ ? > 4: Expecting the error in tickets.sql makes the whole test file > pass or fail based on the version of GEOS you built postgis against. > > --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
[postgis-users] Fw: re: Geocoder (from extras)
--- On Fri, 5/27/11, Mikal Laster wrote: From: Mikal Laster Subject: re:[postgis-users] Geocoder (from extras) To: postgis-users@postgis.refractions.net Date: Friday, May 27, 2011, 7:22 AM in response to http://postgis.refractions.net/pipermail/postgis-users/2011-May/029566.html. After creating some indexes and rewriting geocode_address. I was able to get geocode to run in 483-523 ms for "5775 Perimeter Dr Dublin, Ohio". This used to take 1700-2000 ms for me. I'm removing the main inner qui ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
