Re: [postgis-users] Spatial column from text via a VIEW, Possible? [SOLVED]
Dang LAT/LON LON/LAT differences. This works now, notice that the LAT/LON was switched around to LON/LAT.: -- PostGIS likes LON/LAT (NOT LAT/LON) -- DROP VIEW public.plots; CREATE VIEW plots AS select rxtime, ltrim(split_part(part1, ':', 1), '>') as cmd_type, split_part(part1, ':', 2) as esn, part2 as time_idx, part3 as lat, part4 as lon, ST_Transform(ST_SetSRID(ST_MakePoint(part4::numeric, part3::numeric),4326), 200068) as geom, part5 as speed, part6 as event, rtrim(part7, ';') as good from (select rxtime, split_part(cmd, ',', 1) as part1, split_part(cmd, ',', 2) as part2, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4, split_part(cmd, ',', 5) as part5, split_part(cmd, ',', 6) as part6, split_part(cmd, ',', 7) as part7 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; bobb >>> "Bob Basques" wrote: Yeah, that was my latest fix, I don't have user rights (yet) to run against the Spatial_ref_sys table, so waiting for someone else to run it. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ 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] Update from Alpha7 to beta 1
Hello, I have a question regarding postgis update. I installed a postgis 2.0 alpha 7 over postgresql 9.1.2 in Debian In the Manual for PostGis I can find: ALTER EXTENSION postgis UPDATE TO "2.0.0beta2SVN"; ALTER EXTENSION postgis_topology UPDATE TO "2.0.0beta2SVN"; What I don't understand is when do I execute this Alter Extension methods. I am specially confuse as to the two make installs inside the extensions. I have downloaded the 2.0 beta version, now my question is given this being the normal process to install: ./configure make make comments for extensions : cd extensions cd postgis make clean make make isntall cd .. cd postgis_topology make clean make make install for postgis: make check make install make comments-install where do I stop before I run ALTER EXTENSION postgis UPDATE TO "2.0.0beta2SVN"; ALTER EXTENSION postgis_topology UPDATE TO "2.0.0beta2SVN"; in psql? Thank you. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] The PostGIS Blog project
On Thursday, February 23, 2012, Pierre Racine wrote: > Hi all, > > With the venue of PostGIS 2.0, I was thinking about starting a new blog to teach a bit about PostGIS Raster but with the proliferation of blogs, everybody in its corner, I was thinking that it would be much better for the project to have a multi author blog uniquely devoted to and aggregating posts about PostGIS. Authors often blogging about PostGIS (like Paul and Regina) could concentrate their post about PostGIS there. We could accept occasional posts from advanced users and repost posts made by others. That would be a kind of one-stop blog for PostGIS. We could name it "The PostGIS Blog". > > I know some of you might prefer to keep your post in your own blog for visibility reasons but we could give you a comparable (or better) visibility in the new blog by making the association with a company clear and put the picture of the author. We could also make clear promotional add type boxes to the respective authors' organizations. > Aggregator sounds good to me. I'll be sure to update my infrequent posts with relevant tags so that the postgis nuggests can be filtered from my soil- related chaff. -- Dylan E. Beaudette USDA-NRCS Soil Scientist California Soil Resource Lab http://casoilresource.lawr.ucdavis.edu/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Bob, can you try something like this?
select
st_astext(st_setsrid(st_geomfromtext('POINT(-48 -19)'),4326)) as "wgs84",
st_astext(st_transform(st_setsrid(st_geomfromtext('POINT(-48
-19)'),4326),29192)) as "sad69-utm22"
Here are my results:
"POINT(-48 -19)" ; "POINT(815865.331177955 7896471.32478341)"
Looking at your coordinates, I see a -93.1268 as latitude. Perhaps the
input needs a little validating?
On Tue, Mar 6, 2012 at 4:25 PM, Bob Basques wrote:
> All,
>
> I hit this earlier in my testing, so I must have had things in the right
> order at some earlier point in the process.
>
> This :
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text,
>
>ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326), 200068)) as geom_city
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
> is producing this (which I did hit early on but thought I had the syntax
> wrong at the time) :
>
> ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude
> or longitude exceeded limits (-14)
>
>
>
> ** Error **
>
>
>
> ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or
> longitude exceeded limits (-14)
> SQL state: XX000
>
> This indicates to me that the transform stuff is there , but that it
> failed. I haven't found many references to this error online. The numbers
> above are the numbers from the first record in the table. When I hit this
> earlier, I thought I might be trying to transform against the TEXT (those
> numbers) object vs the GEOM object (which I'm supposed to use, right??).
> The result of the ST_SetSRID seems to be the correct type of object to
> pass to ST_Transform based on what I found in my searches. Maybe I'm not
> supposed to try the transform in this manner.
>
> bobb
>
>
>
>
>
> >>> Mike Toews wrote:
>
> On 7 March 2012 06:10, Bob Basques wrote:
> > ...
> >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> > part4::numeric),4326)), 200068) as geom_city
> > ...
> > ERROR: function st_transform(geometry) does not exist
> > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa...
>
> It looks like "4326)), 200068)" should be "4326), 200068))"
>
> -Mike
> ___
> 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
>
>
--
George R. C. Silva
Desenvolvimento em GIS
http://geoprocessamento.net
http://blog.geoprocessamento.net
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
All, I hit this earlier in my testing, so I must have had things in the right order at some earlier point in the process. This : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326), 200068)) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; is producing this (which I did hit early on but thought I had the syntax wrong at the time) : ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or longitude exceeded limits (-14) ** Error ** ERROR: transform: couldn't project point (44.9677 -93.1268 0): latitude or longitude exceeded limits (-14) SQL state: XX000 This indicates to me that the transform stuff is there , but that it failed. I haven't found many references to this error online. The numbers above are the numbers from the first record in the table. When I hit this earlier, I thought I might be trying to transform against the TEXT (those numbers) object vs the GEOM object (which I'm supposed to use, right??). The result of the ST_SetSRID seems to be the correct type of object to pass to ST_Transform based on what I found in my searches. Maybe I'm not supposed to try the transform in this manner. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ 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] Spatial column from text via a VIEW, Possible?
Also you can confirm if PROJ4 is installed by: SELECT * FROM POSTGIS_FULL_VERSION(); If PROJ4 is installed, it should appear in that string. George On Tue, Mar 6, 2012 at 3:48 PM, Mike Toews wrote: > On 7 March 2012 06:10, Bob Basques wrote: > > ... > >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > > part4::numeric),4326)), 200068) as geom_city > > ... > > ERROR: function st_transform(geometry) does not exist > > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... > > It looks like "4326)), 200068)" should be "4326), 200068))" > > -Mike > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- George R. C. Silva Desenvolvimento em GIS http://geoprocessamento.net http://blog.geoprocessamento.net ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Yeah, that was my latest fix, I don't have user rights (yet) to run against the Spatial_ref_sys table, so waiting for someone else to run it. bobb >>> Mike Toews wrote: On 7 March 2012 06:10, Bob Basques wrote: > ... >ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ 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] Spatial column from text via a VIEW, Possible?
On 7 March 2012 06:10, Bob Basques wrote: > ... > ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, > part4::numeric),4326)), 200068) as geom_city > ... > ERROR: function st_transform(geometry) does not exist > LINE 4: ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... It looks like "4326)), 200068)" should be "4326), 200068))" -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
I dont think so Bob.
Check this out:
In this case the table still exists
create table srid_inexistent_test(
> id serial not null);
> select * from
> addgeometrycolumn('public','srid_inexistent_test','the_geom',99,'POINT',2);
> NOTA: CREATE TABLE criará sequência implícita
> "srid_inexistent_test_id_seq" para coluna serial "srid_inexistent_test.id"
> ERRO: AddGeometryColumns() - invalid SRID
> CONTEXT: SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
> $5 , $6 )"
> função PL/pgSQL "addgeometrycolumn" linha 4 em comando SQL
> ** Erro **
> ERRO: AddGeometryColumns() -* invalid SRID
> *SQL state: P0001
> Contexto: SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
> $5 , $6 )"
> função PL/pgSQL "addgeometrycolumn" linha 4 em comando SQL
In this particular table I've dropped it:
drop table spatial_ref_sys;
> create table srid_inexistent_test(
> id serial not null);
> select * from
> addgeometrycolumn('public','srid_inexistent_test','the_geom',99,'POINT',2);
> ERRO: relação "spatial_ref_sys" não existe
> LINE 1: SELECT SRID FROM spatial_ref_sys WHERE SRID = $1
> ^
> QUERY: SELECT SRID FROM spatial_ref_sys WHERE SRID = $1
> CONTEXT: PL/pgSQL function "addgeometrycolumn" line 74 at comando SQL
> comando SQL "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 , $5 , $6 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at comando SQL
> ** Erro **
> ERRO: relação "spatial_ref_sys" não existe
> SQL state: 42P01
> Contexto: PL/pgSQL function "addgeometrycolumn" line 74 at comando SQL
> comando SQL "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 , $5 , $6 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at comando SQL
PostGIS uses spatial_ref_sys table to verify, when you create a geometry
table, if the projection you're using is available.
If I've made any mistakes, experts, please correct me :D
George
On Tue, Mar 6, 2012 at 3:32 PM, Bob Basques wrote:
> Ok, it looks like I may not have PROJ support compiled in, but wouldn't
> the existence of the SPATIAL_REF_SYS mean that PROJ is installed??
>
> bobb
>
>
>
> >>> "Bob Basques" wrote:
>
> All,
>
>
> made nice progress from Simon's suggestions, but . . .
>
>
> Ok, I'm probably trying to push this harder than I need to, but, I'm
> trying to ST_Transform the data from 4326 to 200068 (Our private
> projection) in the construction of the view, but I keep hitting permission
> errors.
>
>
> NOTE: we use the 200068 projection for all of our data, have for years.
> It's definition in SPATIAL_REF_SYS is correct.
>
>
> This works fine:
>
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
>
> But this give me an error, do I have the ST_Transform in the right spot,
> is 8.4.2 too old of a release? :
>
>
> select
>
>ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)
> as geom,
>
>ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)) as geom_text,
>
>ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric,
> part4::numeric),4326)), 200068) as geom_city
>
> from
>
> (select rxtime,
>
> split_part(cmd, ',', 3) as part3,
>
> split_part(cmd, ',', 4) as part4
>
> from
>
> cmdstpinfo
>
> where cmd
>
> like '>Plot:%') AS first_pass;
>
>
> Like:
>
>
> ERROR: function st_transform(geometry) does not exist
> LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa...
> ^
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
>
>
>
> ** Error **
>
>
>
> ERROR: function st_transform(geometry) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might
> need to add explicit type casts.
> Character: 212
>
>
>
> Thanks
>
>
> bobb
>
>
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
--
George R. C. Silva
Desenvolvimento em GIS
http://geoprocessamento.net
http://blog.geoprocessamento.net
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Spatial column from text via a VIEW, Possible?
Ok, it looks like I may not have PROJ support compiled in, but wouldn't the existence of the SPATIAL_REF_SYS mean that PROJ is installed?? bobb >>> "Bob Basques" wrote: All, made nice progress from Simon's suggestions, but . . . Ok, I'm probably trying to push this harder than I need to, but, I'm trying to ST_Transform the data from 4326 to 200068 (Our private projection) in the construction of the view, but I keep hitting permission errors. NOTE: we use the 200068 projection for all of our data, have for years. It's definition in SPATIAL_REF_SYS is correct. This works fine: select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; But this give me an error, do I have the ST_Transform in the right spot, is 8.4.2 too old of a release? : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)), 200068) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; Like: ERROR: function st_transform(geometry) does not exist LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** Error ** ERROR: function st_transform(geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 212 Thanks bobb ___ 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 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] Spatial column from text via a VIEW, Possible?
All, made nice progress from Simon's suggestions, but . . . Ok, I'm probably trying to push this harder than I need to, but, I'm trying to ST_Transform the data from 4326 to 200068 (Our private projection) in the construction of the view, but I keep hitting permission errors. NOTE: we use the 200068 projection for all of our data, have for years. It's definition in SPATIAL_REF_SYS is correct. This works fine: select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; But this give me an error, do I have the ST_Transform in the right spot, is 8.4.2 too old of a release? : select ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326) as geom, ST_AsText(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)) as geom_text, ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(part3::numeric, part4::numeric),4326)), 200068) as geom_city from (select rxtime, split_part(cmd, ',', 3) as part3, split_part(cmd, ',', 4) as part4 from cmdstpinfo where cmd like '>Plot:%') AS first_pass; Like: ERROR: function st_transform(geometry) does not exist LINE 4:ST_AsText(ST_Transform(ST_SetSRID(ST_MakePoint(pa... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** Error ** ERROR: function st_transform(geometry) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 212 Thanks bobb ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Slope
SELECT ST_Slope( ST_MapAlgebraExpr(ST_AddBand(ST_MakeEmptyRaster(10, 10, 0, 0, 0.1, 0.1, 0, 0, 4269), '8BUI'::text, 1, 0), '32BUI', '([rast.x] - 1) * 10 + [rast.y]') , 1, '8BUI') works for me. Make sure st_slope(rast raster, band integer, pixeltype text) exsit as a function. Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of JamesH > Sent: Tuesday, March 06, 2012 9:08 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] ST_Slope > > Hello all, > > I am trying to produce a slope surface raster from a tiff. > > The raster has 1 band, pixeltype is 8 bit unsigned integer but I cannot get > the > function to work. > > Have tried variances of: > > SELECT > ST_Slope(rast, 1, '8BUI') or (rast, 1, 8) FROM > nclraster > > But PostGIS returns ERROR: function st_slope(raster, integer, integer) does > not > exist LINE 2: ST_Slope(rast,1,8). > > Any pointers as to where I'm going wrong much appreciated > > James > > -- > View this message in context: http://postgis.17.n6.nabble.com/ST-Slope- > tp4551303p4551303.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > ___ > 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: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] error recovering multipolygones
ursprüngliche Nachricht- Von: "Sandro Santilli" s...@keybit.net An: "PostGIS Users Discussion" Datum: Tue, 6 Mar 2012 15:03:07 +0100 - > On Tue, Mar 06, 2012 at 02:36:13PM +0100, Christoph Schumacher wrote: > >> I have a problem with recovery multipolygons from an *.backup file out of >> the >> pgadmin. >> >> Due to an database error i had to backup the existing database with many >> multipolygones. >> After restoring the database every multipolygon was broken out to >> single polygones with their own entry in the database. > > There's no possibility for MULTIPOLYGON objects to become POLYGON due to > pg_restore or > postgis_restore. Maybe you don't remember what you had ? > > --strk; > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > I´m sure that i had multipolygone objects I don´t understand it by myself because the geometrie collum should be the same as it was in the original table ( why should there any converting?!). But I have to admit that I have no chance to check this. The original dataset is lost :( thanks a lot for answering, I think the only thing I can do is to recreate the multipolygones by hand. After this I´ll try the restore case again and post the behavior of the backup best wishes christoph ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Slope
Hello all, I am trying to produce a slope surface raster from a tiff. The raster has 1 band, pixeltype is 8 bit unsigned integer but I cannot get the function to work. Have tried variances of: SELECT ST_Slope(rast, 1, '8BUI') or (rast, 1, 8) FROM nclraster But PostGIS returns ERROR: function st_slope(raster, integer, integer) does not exist LINE 2: ST_Slope(rast,1,8). Any pointers as to where I'm going wrong much appreciated James -- View this message in context: http://postgis.17.n6.nabble.com/ST-Slope-tp4551303p4551303.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] error recovering multipolygones
On Tue, Mar 06, 2012 at 02:36:13PM +0100, Christoph Schumacher wrote: > I have a problem with recovery multipolygons from an *.backup file out of the > pgadmin. > > Due to an database error i had to backup the existing database with many > multipolygones. > After restoring the database every multipolygon was broken out to > single polygones with their own entry in the database. There's no possibility for MULTIPOLYGON objects to become POLYGON due to pg_restore or postgis_restore. Maybe you don't remember what you had ? --strk; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Summary Statistics (vector, raster)
Ok. Thank you. Andreas 2012/3/6 Pierre Racine : >> Are the SummarystatsAgg and AreaWeightedSummaryStats included in the latest >> trunk or should i install from plpgsql? > > That's what I forgot: They are not part of rtpostgis.sql... They are in > scripts/plpgsql. > > Pierre > ___ > 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] error recovering multipolygones
Hello everybody, I have a problem with recovery multipolygons from an *.backup file out of the pgadmin. Due to an database error i had to backup the existing database with many multipolygones. After restoring the database every multipolygon was broken out to single polygones with their own entry in the database. My question is now how to fix this issue, or the better question is, why isnt the geometrie colum as it was befor the backup? thanks a lot and best wishes christoph -- Freundliche Grüße planungsbüro schumacher Im Auftrag gez. Christoph Schumacher Telefon 02262 7205-13 __ pbs planungsbüro schumacher Dipl.-Ing. Jürgen Schumacher Oststraße 8, 51674 Wiehl Telefon 02262 7205-0, Fax 02262 7205-6 i...@pbs-schumacher.de http://www.pbs-schumacher.de __ Wir garantieren nicht für Fehler- bzw. Virenfreiheit der E-Mail und/oder angehängten Dateien. Für Schäden, die aus der Nutzung der Daten entstehen, übernehmen wir keine Haftung. Bitte prüfen Sie die Daten vor der Nutzung. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Summary Statistics (vector, raster)
> Are the SummarystatsAgg and AreaWeightedSummaryStats included in the latest > trunk or should i install from plpgsql? That's what I forgot: They are not part of rtpostgis.sql... They are in scripts/plpgsql. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Summary Statistics (vector, raster)
Hi Pierre, Thank you so much. Are the SummarystatsAgg and AreaWeightedSummaryStats included in the latest trunk or should i install from plpgsql? best, Andreas 2012/3/6 Pierre Racine : > In raster space, on a tiled raster coverage (equivalent to your second query > but a bit more simple): > > SELECT gt. id, (ST_SummaryStatsAgg(ST_Clip(rt.rast, gt. geom, true)).* > FROM rasttable rt, geomtable gt > WHERE ST_Intersects(rt.rast, gt.the_geom) > GROUP BY gt.id > > In raster space, on a non-tiled raster coverage: > > SELECT gt. id, (ST_SummaryStats(ST_Clip(rt.rast, gt. geom, true)).* > FROM rasttable rt, geomtable gt > > In vector space (more precise, works better with big pixels and small > intersecting areas and points and lines, has to be done on a tiled raster > coverage): > > SELECT (ST_AreaWeightedSummaryStats(gv)).* > FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv > FROM rasttable rt, geomtable gt > WHERE ST_Intersects(rt.rast, gt.geom) > ) foo > GROUP BY gt.id > > Pierre > >> -Original Message- >> From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- >> boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen >> Sent: Tuesday, March 06, 2012 4:12 AM >> To: PostGIS Users Discussion >> Subject: [postgis-users] Summary Statistics (vector, raster) >> >> Hi all, >> >> I have used a number of different queries to find the mean raster value >> within a >> polygon. >> However, this is beginning to be outdated i guess since functions have been >> changing. >> What is the quickest way to summarize mean raster values within polygons? >> >> I used these queries before where lspop is the raster and groupclusters is >> the >> polygon: >> >> SELECT a.clusterid, >> (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.the_geom, >> b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))), >> true)).* FROM groupclusters a LEFT JOIN lspop b ON ST_Intersects(a.the_geom, >> b.rast) WHERE a.clusterid = 4422 GROUP BY a.clusterid ORDER BY a.clusterid; >> >> A different query: >> >> SELECT clusterid, (ss).* FROM ( >> SELECT clusterid, ST_SummaryStatsAgg(gv) ss FROM ( SELECT gt.clusterid, >> ST_Clip(rt.rast, gt.the_geom) gv FROM lspop rt, groupclusters gt WHERE >> ST_Intersects(rt.rast, gt.the_geom) AND >> (gt.type=1 OR gt.type=3 OR gt.type=6) AND gt.endyear>=1990 >> ) foo >> WHERE clusterid = 4422 >> GROUP BY clusterid >> ) foo2; >> >> Are there any other ways of doing this? >> What is the PostGIS official raster summary statistics query for finding >> raster >> values within polygons? >> >> Best regards, >> >> Andreas >> ___ >> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Summary Statistics (vector, raster)
In raster space, on a tiled raster coverage (equivalent to your second query but a bit more simple): SELECT gt. id, (ST_SummaryStatsAgg(ST_Clip(rt.rast, gt. geom, true)).* FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.the_geom) GROUP BY gt.id In raster space, on a non-tiled raster coverage: SELECT gt. id, (ST_SummaryStats(ST_Clip(rt.rast, gt. geom, true)).* FROM rasttable rt, geomtable gt In vector space (more precise, works better with big pixels and small intersecting areas and points and lines, has to be done on a tiled raster coverage): SELECT (ST_AreaWeightedSummaryStats(gv)).* FROM (SELECT ST_Intersection(rt.rast, gt.geom) gv FROM rasttable rt, geomtable gt WHERE ST_Intersects(rt.rast, gt.geom) ) foo GROUP BY gt.id Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen > Sent: Tuesday, March 06, 2012 4:12 AM > To: PostGIS Users Discussion > Subject: [postgis-users] Summary Statistics (vector, raster) > > Hi all, > > I have used a number of different queries to find the mean raster value > within a > polygon. > However, this is beginning to be outdated i guess since functions have been > changing. > What is the quickest way to summarize mean raster values within polygons? > > I used these queries before where lspop is the raster and groupclusters is the > polygon: > > SELECT a.clusterid, > (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.the_geom, > b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))), > true)).* FROM groupclusters a LEFT JOIN lspop b ON ST_Intersects(a.the_geom, > b.rast) WHERE a.clusterid = 4422 GROUP BY a.clusterid ORDER BY a.clusterid; > > A different query: > > SELECT clusterid, (ss).* FROM ( > SELECT clusterid, ST_SummaryStatsAgg(gv) ss FROM ( SELECT gt.clusterid, > ST_Clip(rt.rast, gt.the_geom) gv FROM lspop rt, groupclusters gt WHERE > ST_Intersects(rt.rast, gt.the_geom) AND > (gt.type=1 OR gt.type=3 OR gt.type=6) AND gt.endyear>=1990 > ) foo > WHERE clusterid = 4422 > GROUP BY clusterid > ) foo2; > > Are there any other ways of doing this? > What is the PostGIS official raster summary statistics query for finding > raster > values within polygons? > > Best regards, > > Andreas > ___ > 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] Summary Statistics (vector, raster)
Hi all, I have used a number of different queries to find the mean raster value within a polygon. However, this is beginning to be outdated i guess since functions have been changing. What is the quickest way to summarize mean raster values within polygons? I used these queries before where lspop is the raster and groupclusters is the polygon: SELECT a.clusterid, (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.the_geom, b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))), true)).* FROM groupclusters a LEFT JOIN lspop b ON ST_Intersects(a.the_geom, b.rast) WHERE a.clusterid = 4422 GROUP BY a.clusterid ORDER BY a.clusterid; A different query: SELECT clusterid, (ss).* FROM ( SELECT clusterid, ST_SummaryStatsAgg(gv) ss FROM ( SELECT gt.clusterid, ST_Clip(rt.rast, gt.the_geom) gv FROM lspop rt, groupclusters gt WHERE ST_Intersects(rt.rast, gt.the_geom) AND (gt.type=1 OR gt.type=3 OR gt.type=6) AND gt.endyear>=1990 ) foo WHERE clusterid = 4422 GROUP BY clusterid ) foo2; Are there any other ways of doing this? What is the PostGIS official raster summary statistics query for finding raster values within polygons? Best regards, Andreas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
