Re: [postgis-users] Voronoi tessellation
On Mar 6, 2012, at 11:51 AM, Chris English wrote: > > Puneet - > Thank you for the clean notes, it has been interesting to follow. I'll try to > muddle through PL/R on 9.1. No need to muddle through it. It really is very easy, seriously, if I can do it If you get hung up, just ask. > Chris > >> From: punk.k...@gmail.com >> Date: Tue, 6 Mar 2012 11:43:46 -0600 >> To: postgis-users@postgis.refractions.net >> Subject: Re: [postgis-users] Voronoi tessellation >> >> For other poor sods like me who might want the same gratification, I have >> put up clean notes at >> >> http://punkish.org/Voronoi-Diagrams-In-PostGIS >> >> >> >> .. >> >> >> >> -- >> Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Puneet - Thank you for the clean notes, it has been interesting to follow. I'll try to muddle through PL/R on 9.1. Chris > From: punk.k...@gmail.com > Date: Tue, 6 Mar 2012 11:43:46 -0600 > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] Voronoi tessellation > > For other poor sods like me who might want the same gratification, I have put > up clean notes at > > http://punkish.org/Voronoi-Diagrams-In-PostGIS > > > > .. > > > > -- > Puneet Kishor > ___ > 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] Voronoi tessellation
For other poor sods like me who might want the same gratification, I have put up clean notes at http://punkish.org/Voronoi-Diagrams-In-PostGIS .. -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
On Mar 6, 2012, at 9:16 AM, Derek Jones wrote: > Two possibilities > > 1.) Coincident points as was suggested yeah, that's what it was. > .. So, once I remove the dupes, I can store the voronoi polys in a new column in the same table. Many thanks y'all. -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Two possibilities 1.) Coincident points as was suggested 2.) An incomplete exterior polygon perhaps? \ \ /--- || o | o | || /-- / Puneet Kishor wrote: Derek (and others), I've kinda, sorta got the voronoi() function working, but I have a related query for now. If I run the function on "n" points, shouldn't I get "n" polygons? I have a table with 1597 points, but when I ran the function, I got 1595 polys, and am wondering what the heck happened to two of them? On Mar 5, 2012, at 10:55 PM, Derek Jones wrote: Here is what I have that AFAIR works fine. It's been a while since I used the code - I may be digging it out again soon though :-) - create type voronoi as (id integer, polygon geometry); create or replace function voronoi(text,text,text) returns setof voronoi as ' library(deldir) # select the point x/y coordinates into a data frame... .. -- Puneet Kishor ___ 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] Voronoi tessellation
On Mar 6, 2012, at 9:08 AM, Ben Madin wrote:
> Hi Puneet,
>
> Did it work when you removed the offending semicolon, as described in the
> syntax error message?
Ben,
To be honest, I didn't try that approach. I am in a hurry for a presentation
this aft, so I wanted to get this rolling, and will try that later.
That said, I am not sure if that semicolon is the offender. Assuming the error
trace is identifying the correct line, the offending line is (I have cleaned it
up to reflect modern usage, Pg 9, PostGIS 1.5.x, etc.)
points <- pg.spi.exec(sprintf("SELECT ST_X(%2$s) AS x, ST_Y(%2$s) AS y
FROM %1$s;",arg1,arg2))
The semicolon, afaict, is required to construct the correct SQL statement. For
now, I hard coded the statement like so, and it works
points <- pg.spi.exec("SELECT ST_X(the_geom) AS x, ST_Y(the_geom) AS y
FROM mytable;")
>
> cheers
>
> Ben
>
>
>
> On 06/03/2012, at 11:52 AM, Puneet Kishor wrote:
>
>>
>> On Mar 5, 2012, at 9:44 PM, Derek Jones wrote:
>>
>>> Hi all,
>>>
>>> I have used an R solution that works well with the plsql to do this. Found
>>> here:
>>>
>>> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
>>>
>>> Needed some mods for my local solution, but helpful.
>>>
>>
>>
>> Yes, I tried that, but as I noted in my earlier email, I got the following
>> error
>>
>> ERROR: R interpreter expression evaluation error
>> DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y
>> from %1$s;", :
>> error in SQL statement : syntax error at or near ";"
>> CONTEXT: In R support function pg.spi.exec
>> In PL/R function voronoi
>>
>>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Hi Puneet,
Did it work when you removed the offending semicolon, as described in the
syntax error message?
cheers
Ben
On 06/03/2012, at 11:52 AM, Puneet Kishor wrote:
>
> On Mar 5, 2012, at 9:44 PM, Derek Jones wrote:
>
>> Hi all,
>>
>> I have used an R solution that works well with the plsql to do this. Found
>> here:
>>
>> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
>>
>> Needed some mods for my local solution, but helpful.
>>
>
>
> Yes, I tried that, but as I noted in my earlier email, I got the following
> error
>
> ERROR: R interpreter expression evaluation error
> DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
> %1$s;", :
> error in SQL statement : syntax error at or near ";"
> CONTEXT: In R support function pg.spi.exec
> In PL/R function voronoi
>
>
> ___
> 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] Voronoi tessellation
On 3/6/2012 10:04 AM, Puneet Kishor wrote: Derek (and others), I've kinda, sorta got the voronoi() function working, but I have a related query for now. If I run the function on "n" points, shouldn't I get "n" polygons? I have a table with 1597 points, but when I ran the function, I got 1595 polys, and am wondering what the heck happened to two of them? Coincident point? On Mar 5, 2012, at 10:55 PM, Derek Jones wrote: Here is what I have that AFAIR works fine. It's been a while since I used the code - I may be digging it out again soon though :-) - create type voronoi as (id integer, polygon geometry); create or replace function voronoi(text,text,text) returns setof voronoi as ' library(deldir) # select the point x/y coordinates into a data frame... .. -- Puneet Kishor ___ 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] Voronoi tessellation
Derek (and others), I've kinda, sorta got the voronoi() function working, but I have a related query for now. If I run the function on "n" points, shouldn't I get "n" polygons? I have a table with 1597 points, but when I ran the function, I got 1595 polys, and am wondering what the heck happened to two of them? On Mar 5, 2012, at 10:55 PM, Derek Jones wrote: > Here is what I have that AFAIR works fine. It's been a while since I used the > code - I may be digging it out again soon though :-) > > - > > > > > create type voronoi as (id integer, polygon geometry); > > create or replace function voronoi(text,text,text) returns setof voronoi as ' >library(deldir) > ># select the point x/y coordinates into a data frame... > >> .. -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Thanks Derek,
I was able to make this code work by fiddling with the `sprintf` lines. For
some reason, those lines (there are a couple of them below) are breaking. I
will take a more detailed look at this tomorrow, as this is going to be a
fairly used function for me.
I also saw a PL/PgSQL version of this floating around, but that was really
inefficient... I think that ends up doing a cartesian join because it takes
forever to run.
Voronoi and Delaunay are useful, and might be worth adding them in core Postgis.
On Mar 5, 2012, at 10:55 PM, Derek Jones wrote:
> Here is what I have that AFAIR works fine. It's been a while since I used the
> code - I may be digging it out again soon though :-)
>
> -
>
>
>
>
> create type voronoi as (id integer, polygon geometry);
>
> create or replace function voronoi(text,text,text) returns setof voronoi as '
>library(deldir)
>
># select the point x/y coordinates into a data frame...
>
>points <- pg.spi.exec( sprintf("select x(%2$s) as x, y(%2$s) as y from
> %1$s;",arg1,arg2) )
>
># calculate an approprate buffer distance (~10%):
>
>buffer = ( ( abs( max( points$x ) - min( points$x ) ) + abs( max( points$y
> ) - min ( points$y ) ) ) / 2 ) * (0.10)
>
># get EWKB for the overall buffer of the convex hull for all points:
>
>buffer <- pg.spi.exec(sprintf("select
> buffer(convexhull(geomunion(%2$s)),%3$.6f) as ewkb from
> %1$s;",arg1,arg2,buffer))
>
># the following use of deldir uses high precision and digits to prevent
> slivers between the output polygons, and uses
># a relatively large bounding box with four dummy points included to
> ensure that points in the peripheral areas of the
># dataset are appropriately enveloped by their corresponding polygons:
>
>voro = deldir ( points$x, points$y, digits=22,
> frac=0.01, list(ndx=2,ndy=2), rw=c ( min( points$x )
> - abs( min( points$x ) - max( points$x ) ), max( points$x ) + abs( min(
> points$x ) - max( points$x ) ), min( points$y ) - abs( min( points$y ) - max(
> points$y ) ), max( points$y ) + abs( min( points$y ) - max( points$y ) ) ) )
>
>tiles = tile.list(voro)
>
>poly = array()
>
>id = array()
>
>p = 1
>
>for (i in 1:length(tiles))
>{
> tile = tiles[[i]]
>
> curpoly = "POLYGON(("
>
> for (j in 1:length(tile$x))
> {
>curpoly = sprintf("%s %.6f %.6f,",curpoly,tile$x[[j]],tile$y[[j]])
> }
>
> curpoly = sprintf("%s %.6f %.6f))",curpoly,tile$x[[1]],tile$y[[1]])
>
> # this bit will find the original point that corresponds to the current
> polygon, along with its id
> # and the SRID used for the point geometry (presumably this is the same
> for all points)...
> # this will also filter out the extra polygons created for the
> # four dummy points, as they will not return a result from this query:
>
> ipoint <- pg.spi.exec ( sprintf ( "select %3$s as id,
> intersection(''SRID=''||srid(%2$s)||'';%4$s'',''%5$s'') as polygon from %1$s
> where intersects(%2$s,''SRID=''||srid(%2$s)||'';%4$s'');", arg1, arg2, arg3,
> curpoly, buffer$ewkb[1] ) )
>
> if (length(ipoint) > 0)
> {
>poly[[p]] <- ipoint$polygon[1]
>id[[p]] <- ipoint$id[1]
>p = (p + 1)
> }
>}
>
> Puneet Kishor wrote:
>> On Mar 5, 2012, at 9:44 PM, Derek Jones wrote:
>>> Hi all,
>>>
>>> I have used an R solution that works well with the plsql to do this. Found
>>> here:
>>>
>>> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
>>>
>>> Needed some mods for my local solution, but helpful.
>>>
>> Yes, I tried that, but as I noted in my earlier email, I got the following
>> error
>> ERROR: R interpreter expression evaluation error
>> DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y
>> from %1$s;", : error in SQL statement : syntax error at or near ";"
>> CONTEXT: In R support function pg.spi.exec
>> In PL/R function voronoi
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Here is what I have that AFAIR works fine. It's been a while since I
used the code - I may be digging it out again soon though :-)
-
create type voronoi as (id integer, polygon geometry);
create or replace function voronoi(text,text,text) returns setof voronoi
as '
library(deldir)
# select the point x/y coordinates into a data frame...
points <- pg.spi.exec( sprintf("select x(%2$s) as x, y(%2$s) as y
from %1$s;",arg1,arg2) )
# calculate an approprate buffer distance (~10%):
buffer = ( ( abs( max( points$x ) - min( points$x ) ) + abs( max(
points$y ) - min ( points$y ) ) ) / 2 ) * (0.10)
# get EWKB for the overall buffer of the convex hull for all points:
buffer <- pg.spi.exec(sprintf("select
buffer(convexhull(geomunion(%2$s)),%3$.6f) as ewkb from
%1$s;",arg1,arg2,buffer))
# the following use of deldir uses high precision and digits to
prevent slivers between the output polygons, and uses
# a relatively large bounding box with four dummy points included
to ensure that points in the peripheral areas of the
# dataset are appropriately enveloped by their corresponding polygons:
voro = deldir ( points$x, points$y, digits=22,
frac=0.01, list(ndx=2,ndy=2), rw=c ( min(
points$x ) - abs( min( points$x ) - max( points$x ) ), max( points$x ) +
abs( min( points$x ) - max( points$x ) ), min( points$y ) - abs( min(
points$y ) - max( points$y ) ), max( points$y ) + abs( min( points$y ) -
max( points$y ) ) ) )
tiles = tile.list(voro)
poly = array()
id = array()
p = 1
for (i in 1:length(tiles))
{
tile = tiles[[i]]
curpoly = "POLYGON(("
for (j in 1:length(tile$x))
{
curpoly = sprintf("%s %.6f %.6f,",curpoly,tile$x[[j]],tile$y[[j]])
}
curpoly = sprintf("%s %.6f %.6f))",curpoly,tile$x[[1]],tile$y[[1]])
# this bit will find the original point that corresponds to the
current polygon, along with its id
# and the SRID used for the point geometry (presumably this is
the same for all points)...
# this will also filter out the extra polygons created for the
# four dummy points, as they will not return a result from this
query:
ipoint <- pg.spi.exec ( sprintf ( "select %3$s as id,
intersection(''SRID=''||srid(%2$s)||'';%4$s'',''%5$s'') as polygon from
%1$s where intersects(%2$s,''SRID=''||srid(%2$s)||'';%4$s'');", arg1,
arg2, arg3, curpoly, buffer$ewkb[1] ) )
if (length(ipoint) > 0)
{
poly[[p]] <- ipoint$polygon[1]
id[[p]] <- ipoint$id[1]
p = (p + 1)
}
}
Puneet Kishor wrote:
On Mar 5, 2012, at 9:44 PM, Derek Jones wrote:
Hi all,
I have used an R solution that works well with the plsql to do this. Found here:
http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
Needed some mods for my local solution, but helpful.
Yes, I tried that, but as I noted in my earlier email, I got the following error
ERROR: R interpreter expression evaluation error
DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from %1$s;", :
error in SQL statement : syntax error at or near ";"
CONTEXT: In R support function pg.spi.exec
In PL/R function voronoi
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
On Mar 5, 2012, at 9:44 PM, Derek Jones wrote:
> Hi all,
>
> I have used an R solution that works well with the plsql to do this. Found
> here:
>
> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
>
> Needed some mods for my local solution, but helpful.
>
Yes, I tried that, but as I noted in my earlier email, I got the following error
ERROR: R interpreter expression evaluation error
DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
%1$s;", :
error in SQL statement : syntax error at or near ";"
CONTEXT: In R support function pg.spi.exec
In PL/R function voronoi
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Hi all, I have used an R solution that works well with the plsql to do this. Found here: http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 Needed some mods for my local solution, but helpful. Kind regards Derek. Puneet Kishor wrote: On Mar 5, 2012, at 9:09 PM, Simon Greener wrote: Jaspa, which could be used alongside PostGIS, has an ST_DelaunayTriangles() function but no Voronoi. Interesting. I have never heard of Jaspa. What is that? I wouldn't mind giving ST_DelaunayTriangles() a whirl in the meantime. Many thanks, -- Puneet Kishor ___ 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] Voronoi tessellation
On Mar 5, 2012, at 9:09 PM, Simon Greener wrote: > Jaspa, which could be used alongside PostGIS, has an ST_DelaunayTriangles() > function but no Voronoi. Interesting. I have never heard of Jaspa. What is that? I wouldn't mind giving ST_DelaunayTriangles() a whirl in the meantime. Many thanks, -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Y'know, I tried that. I got a cryptic error at Sorry, can't help you. Regina Obe will probably answer this. Jaspa, which could be used alongside PostGIS, has an ST_DelaunayTriangles() function but no Voronoi. regards Simon -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research." 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: si...@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) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
On Mar 5, 2012, at 8:44 PM, Simon Greener wrote:
> Try
> http://postgis.refractions.net/pipermail/postgis-users/2007-June/016102.html
Y'know, I tried that. I got a cryptic error at
points <- pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
%1$s;"
The error was
ERROR: R interpreter expression evaluation error
DETAIL: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
%1$s;", :
error in SQL statement : syntax error at or near ";"
CONTEXT: In R support function pg.spi.exec
In PL/R function voronoi
** Error **
ERROR: R interpreter expression evaluation error
SQL state: 22000
Detail: Error in pg.spi.exec(sprintf("select x(%2$s) as x, y(%2$s) as y from
%1$s;", :
error in SQL statement : syntax error at or near ";"
Context: In R support function pg.spi.exec
In PL/R function voronoi
> or
> http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02
> Simon
> On Tue, 06 Mar 2012 13:41:41 +1100, Puneet Kishor wrote:
>
>> Given a set of points in "the_geom", how can I create a voronoi tessellation
>> and store it as an additional geometry column? Are there a set of built in
>> functions in Pg that I can use?
>>
>>
>> --
>> Puneet Kishor
>>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Voronoi tessellation
Try http://postgis.refractions.net/pipermail/postgis-users/2007-June/016102.html or http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgresql_plr_tut02 Simon On Tue, 06 Mar 2012 13:41:41 +1100, Puneet Kishor wrote: Given a set of points in "the_geom", how can I create a voronoi tessellation and store it as an additional geometry column? Are there a set of built in functions in Pg that I can use? -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research." 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: si...@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) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Voronoi tessellation
Given a set of points in "the_geom", how can I create a voronoi tessellation and store it as an additional geometry column? Are there a set of built in functions in Pg that I can use? -- Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
