Re: [postgis-users] ST_Intersects(geom, geom) Memory issue
Thanks Andreas. Could I ask you to also try further filtering your inputs so to avoid point geometries as a whole ? It'd help further debugging the issue. It would take adding something like this: AND ST_Dimension(p.cell) != 0 AND ST_Dimension(p.geom) != 0 --strk; On Wed, Feb 08, 2012 at 04:55:08PM +0100, Andreas Forø Tollefsen wrote: I tested the query again and identified which commit of revisions introduced the problem. Until revision 8876 the query works fine. After revision 8877 the query leaks memory and eats up all of the servers memory in a couple of minutes. Tested: 8096 OK 8796 OK 8866 OK 8876 OK 8877 NOT OK 8886 NOT OK 8896 NOT OK 8996 NOT OK 9096 NOT OK Again the query was simply: SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.geom) AND g.startyear = 1946 AND g.endyear = 1946 GROUP BY a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ; Would be great if you could look into this. Best, Andreas 2012/2/4 Andreas Forø Tollefsen andrea...@gmail.com: I created a new clean db and installed latest rev. Then loaded my tables and ran the query with the same error and postgres server crash. Any suggestions on how i can solve this? As for now i am using rev 8292 and then the query and my raster summaries work. -- ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Why so terribly slow?
I see, thanks for the tip. I used PL/PGSQL because some functions such as ST_AsRaster are implemented that way and are structurally similar to the ones I want to make. But well, after taking a look at the map algebra functions I think they will be helpful... I'll just have to go over the syntax of these kind of expressions. Thank you very much! pvaldes: the function I put up there is just a basic example with no utility. Later on, I'll want to work on fuzzy logic, weighted average... -- View this message in context: http://postgis.17.n6.nabble.com/Why-so-terribly-slow-tp4376157p4378822.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] Installing raster in POSTGIS 2.0
Hello friends, I am trying to install, postgis 2.0 over postgresql 9.0, but i am not successful. Can anyone give me a suitable link (which you have tried and working properly) which explains the installation of Postgis 2.0 also with raster capabilities (rtpostgis.sql). thanks -- View this message in context: http://postgis.17.n6.nabble.com/Installing-raster-in-POSTGIS-2-0-tp4378919p4378919.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] postgis tutorial
Can someone point to a free tutorial of postgis for a fresher : a comp science student , having installed ( and with familiarity with basics of gis ) qgis, postgres9.0, geoserver etc Sent from BlackBerry® on Airtel ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis tutorial
Hi, You will find a couple of tutorials at the http://bostongis.com/ page. Andreas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] postgis tutorial
Hello, Look on this topic on gis stack exchange http://gis.stackexchange.com/questions/10376/spatial-databases-learning-resources-for-newbies Regards ThomasG ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Intersects(geom, geom) Memory issue
Yes, I will test this as soon as I am done running a couple of my queries. Andreas 2012/2/9 Sandro Santilli s...@keybit.net: Thanks Andreas. Could I ask you to also try further filtering your inputs so to avoid point geometries as a whole ? It'd help further debugging the issue. It would take adding something like this: AND ST_Dimension(p.cell) != 0 AND ST_Dimension(p.geom) != 0 --strk; On Wed, Feb 08, 2012 at 04:55:08PM +0100, Andreas Forř Tollefsen wrote: I tested the query again and identified which commit of revisions introduced the problem. Until revision 8876 the query works fine. After revision 8877 the query leaks memory and eats up all of the servers memory in a couple of minutes. Tested: 8096 OK 8796 OK 8866 OK 8876 OK 8877 NOT OK 8886 NOT OK 8896 NOT OK 8996 NOT OK 9096 NOT OK Again the query was simply: SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.geom) AND g.startyear = 1946 AND g.endyear = 1946 GROUP BY a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ; Would be great if you could look into this. Best, Andreas 2012/2/4 Andreas Forř Tollefsen andrea...@gmail.com: I created a new clean db and installed latest rev. Then loaded my tables and ran the query with the same error and postgres server crash. Any suggestions on how i can solve this? As for now i am using rev 8292 and then the query and my raster summaries work. -- ,--o-. | __/ | Thank you for PostGIS-2.0 Topology ! | / 2.0 | http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Intersects(geom, geom) Memory issue
Hi Sandro, Rewrote the script to: INSERT INTO geoepr_cell SELECT a.gid, a.gridyear, g.cowgroup, g.type, SUM(ST_Area(ST_Intersection(p.cell, g.the_geom))) FROM priogridall a, priogrid p, geoepreth2valid g WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.the_geom) AND g.startyear = 1946 AND g.endyear = 1946 AND ST_Dimension(p.cell) != 0 AND ST_Dimension(g.the_geom) != 0 GROUP BY a.gid, a.gridyear, g.cowgroup, g.type ORDER BY gid, gridyear ; Same problem. Andreas 2012/2/9 Andreas Forø Tollefsen andrea...@gmail.com: Yes, I will test this as soon as I am done running a couple of my queries. Andreas 2012/2/9 Sandro Santilli s...@keybit.net: Thanks Andreas. Could I ask you to also try further filtering your inputs so to avoid point geometries as a whole ? It'd help further debugging the issue. It would take adding something like this: AND ST_Dimension(p.cell) != 0 AND ST_Dimension(p.geom) != 0 --strk; On Wed, Feb 08, 2012 at 04:55:08PM +0100, Andreas Forř Tollefsen wrote: I tested the query again and identified which commit of revisions introduced the problem. Until revision 8876 the query works fine. After revision 8877 the query leaks memory and eats up all of the servers memory in a couple of minutes. Tested: 8096 OK 8796 OK 8866 OK 8876 OK 8877 NOT OK 8886 NOT OK 8896 NOT OK 8996 NOT OK 9096 NOT OK Again the query was simply: SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.geom) AND g.startyear = 1946 AND g.endyear = 1946 GROUP BY a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ; Would be great if you could look into this. Best, Andreas 2012/2/4 Andreas Forř Tollefsen andrea...@gmail.com: I created a new clean db and installed latest rev. Then loaded my tables and ran the query with the same error and postgres server crash. Any suggestions on how i can solve this? As for now i am using rev 8292 and then the query and my raster summaries work. -- ,--o-. | __/ | Thank you for PostGIS-2.0 Topology ! | / 2.0 | http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Why so terribly slow?
I see, thanks for the tip. I used PL/PGSQL because some functions such as ST_AsRaster are implemented that way and are structurally similar to the ones I want to make. But well, after taking a look at the map algebra functions I think they will be helpful... I'll just have to go over the syntax of these kind of expressions. Thank you very much! The syntax for ST_MapAlgebraExpr() is the same as a SQL expression with the addition of three keywords that you must put in your expression: [rast.val] the value of the pixel being computed [rast.x] the x raster coordinate of the pixel being computed [rast.y] the y raster coordinate of the pixel being computed There is the main expression and alternate expressions to deal with nodata values. For two rasters ST_MapAlgebraExpr() the keywords are [rast1.val], [rast1.x], [rast1.y] and [rast2.val], [rast2.x], [rast2.y]. pvaldes: the function I put up there is just a basic example with no utility. Later on, I'll want to work on fuzzy logic, weighted average... For weighted average of raster values over the area covered by polygons see the tutorial (a bit outdated but the idea is the same): http://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01 there is now a st_areaweightedsummarystats() prototype function in: http://trac.osgeo.org/postgis/browser/trunk/raster/scripts/plpgsql to help computing the weighted average. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing raster in POSTGIS 2.0
On which OS? -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of nikhil Sent: Thursday, February 09, 2012 6:31 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Installing raster in POSTGIS 2.0 Hello friends, I am trying to install, postgis 2.0 over postgresql 9.0, but i am not successful. Can anyone give me a suitable link (which you have tried and working properly) which explains the installation of Postgis 2.0 also with raster capabilities (rtpostgis.sql). thanks -- View this message in context: http://postgis.17.n6.nabble.com/Installing-raster- in-POSTGIS-2-0-tp4378919p4378919.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] ST_Intersects(geom, geom) Memory issue
On Thu, Feb 09, 2012 at 03:59:08PM +0100, Andreas Forø Tollefsen wrote: Hi Sandro, Rewrote the script to: INSERT INTO geoepr_cell SELECT a.gid, a.gridyear, g.cowgroup, g.type, SUM(ST_Area(ST_Intersection(p.cell, g.the_geom))) FROM priogridall a, priogrid p, geoepreth2valid g WHERE a.gid = p.gid AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.the_geom) AND g.startyear = 1946 AND g.endyear = 1946 AND ST_Dimension(p.cell) != 0 AND ST_Dimension(g.the_geom) != 0 GROUP BY a.gid, a.gridyear, g.cowgroup, g.type ORDER BY gid, gridyear ; Same problem. Right, I came to the same conclusion: its' the prepared geometry code having memory issues, not the PostGIS cached PIP code. It looks like every new object cached grows the total memory. Let's continue this on trac: http://trac.osgeo.org/postgis/ticket/547 Thanks again. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Question about using ST_Transform()
Hello All, I'm trying to understand how to use ST_Transform() on a query. When I have everything in the same projection (in this case Albers Equal Area Conic) my query returns the correct result: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) as stats FROM ny_albers as r, cities_albers as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, p.geom) ) as foo; If I use a table with polygons in a different projection (in this case Web Mercator), I do not get any values returned: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, ST_Transform(p.geom,102003), NULL, TRUE)) as stats FROM ny_albers as r, cities_webMercator as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, ST_Transform(p.geom,102003)) ) as foo; I added the projection SRID:102003 using the following lines: INSERT into spatial_ref_sys ( srid, auth_name, auth_srid, srtext, proj4text ) values ( 102003, -- I changed this line as it was originally 9102003 which did not satisfy the constraints 'esri', 102003, 'PROJCS[USA_Contiguous_Albers_Equal_Area_Conic,GEOGCS[GCS_North_American_1983,DATUM[North_American_Datum_1983,SPHEROID[GRS_1980,6378137,298.257222101]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453292519943295]],PROJECTION[Albers_Conic_Equal_Area],PARAMETER[False_Easting,0],PARAMETER[False_Northing,0],PARAMETER[longitude_of_center,-96],PARAMETER[Standard_Parallel_1,29.5],PARAMETER[Standard_Parallel_2,45.5],PARAMETER[latitude_of_center,37.5],UNIT[Meter,1],AUTHORITY[EPSG,102003]]', '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=37.5 +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs '); I had some problems when I tried to add this projection from http://spatialreference.org/ref/esri/102003/postgis/ The names and values of srtext, and proj4text were reversed, and the SRID seemed incorrect so I changed it. I don't know if this is related to my question above, but I thought it worth mentioning. I did import the raster and polygon using this SRID on the query that worked. Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Postgis topology issue
Hi all,
i'm playing with Postgis 2.0 Topology function and i like it.. but i receive
this error:
** Error **
ERROR: SQL/MM Spatial exception - point not on edge
SQL state: P0001
Context: PL/pgSQL function topogeo_addpoint line 66 at assignment
PL/pgSQL function topogeo_addlinestring line 111 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select topology.
TopoGeo_addLinestring(atopology, rec.geom, tolerance) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 23 at assignment
SQL statement INSERT INTO sample1.relation(topogeo_id, layer_id,
element_type, element_id) SELECT 5, 1, 3, topogeo_addPolygon('sample1',
'01030001000A80AB6829413789412068EB284137894160A96829412BEB2841C976BEDFA568294100C0C0EA2841C976BE1FA4682941378941A08AEA2841008049682941378941A08DEA2841C976BE5F4B6829410080C5EA284100804B6829410080C7EA28414F68294100402FEB28415168294100406BEB28410080AB6829413789412068EB2841'::
geometry, 0);
PL/pgSQL function totopogeom line 125 at EXECUTE statement
these are my query that are copy and paste from
http://postgis.refractions.net/pipermail/postgis-users/2012-January/032169.
html
--Create new togology ---
SELECT CreateTopology('sample1');
--Add topoGeomColumn---
SELECT AddTopoGeometryColumn('sample1',
'public', 'small_polygon', 'topogeom',
'POLYGON');
UPDATE small_polygon SET topogeom = toTopoGeom(the_geom, 'sample1',1);
Do you have any suggestion? maybe i used a a wrong geometry..
Thanks
Francesco
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Running postgis from apache user
Roberto Marzocchi wrote: Within a pywps script I want to access to postgresql database using Grass gis and I receive an error message: *ERROR 1: PostgreSQL ddriver doesn't currently support database creation.* I have already verified the correctness of the command running it from normal unix user and I think the problem is a misconfiguration of postgis/postgresql. Without any knowledge of what you actually tried to do - because you didn't tell us - I'd recommend to take error messages seriously. It's not that uncommon for certain DB middleware _not_ to support creation of new databases (with ODBC being one of the popular examples). Cheers, Martin. -- Unix _IS_ user friendly - it's just selective about who its friends are ! -- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis topology issue
On Thu, Feb 09, 2012 at 05:01:15PM +0100, francescobocca...@libero.it wrote:
Hi all,
i'm playing with Postgis 2.0 Topology function and i like it.. but i
receive
this error:
** Error **
ERROR: SQL/MM Spatial exception - point not on edge
SQL state: P0001
Context: PL/pgSQL function topogeo_addpoint line 66 at assignment
PL/pgSQL function topogeo_addlinestring line 111 at assignment
SQL statement SELECT array_cat(edges, array_agg(x)) FROM ( select topology.
TopoGeo_addLinestring(atopology, rec.geom, tolerance) as x ) as foo
PL/pgSQL function topogeo_addpolygon line 23 at assignment
SQL statement INSERT INTO sample1.relation(topogeo_id, layer_id,
element_type, element_id) SELECT 5, 1, 3, topogeo_addPolygon('sample1',
'01030001000A80AB6829413789412068EB284137894160A96829412BEB2841C976BEDFA568294100C0C0EA2841C976BE1FA4682941378941A08AEA2841008049682941378941A08DEA2841C976BE5F4B6829410080C5EA284100804B6829410080C7EA28414F68294100402FEB28415168294100406BEB28410080AB6829413789412068EB2841'::
geometry, 0);
PL/pgSQL function totopogeom line 125 at EXECUTE statement
these are my query that are copy and paste from
http://postgis.refractions.net/pipermail/postgis-users/2012-January/032169.
html
--Create new togology ---
SELECT CreateTopology('sample1');
--Add topoGeomColumn---
SELECT AddTopoGeometryColumn('sample1',
'public', 'small_polygon', 'topogeom',
'POLYGON');
UPDATE small_polygon SET topogeom = toTopoGeom(the_geom, 'sample1',1);
Do you have any suggestion? maybe i used a a wrong geometry..
It's a bug, what you can do:
1) Make sure you're running the latest PostGIS version.
This includes re-loading all the SQL files
2) Reduce your input as much as possible while still
triggering the error
3) Attach your reduced input to a ticket on
http://trac.osgeo.org/postgis
Hopefully (1) will fix !
--strk;
,--o-.
| __/ |Delivering high quality PostGIS 2.0 !
| / 2.0 |http://strk.keybit.net
`-o--'
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Question about using ST_Transform()
I generally display things in OpenJump to make sure they align correctly before doing anything instead of assuming they do. Do the projected geometries align properly with the rasters? You can display the raster extents easily in OpenJump by casting the raster to geometry (rast::geometry)... You can also do this easily in QGIS or ArcGIS 10. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 09, 2012 10:45 AM To: PostGIS Users Discussion Subject: [postgis-users] Question about using ST_Transform() Hello All, I'm trying to understand how to use ST_Transform() on a query. When I have everything in the same projection (in this case Albers Equal Area Conic) my query returns the correct result: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) as stats FROM ny_albers as r, cities_albers as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, p.geom) ) as foo; If I use a table with polygons in a different projection (in this case Web Mercator), I do not get any values returned: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, ST_Transform(p.geom,102003), NULL, TRUE)) as stats FROM ny_albers as r, cities_webMercator as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, ST_Transform(p.geom,102003)) ) as foo; I added the projection SRID:102003 using the following lines: INSERT into spatial_ref_sys ( srid, auth_name, auth_srid, srtext, proj4text ) values ( 102003, -- I changed this line as it was originally 9102003 which did not satisfy the constraints 'esri', 102003, 'PROJCS[USA_Contiguous_Albers_Equal_Area_Conic,GEOGCS[GCS_North_A merican_1983,DATUM[North_American_Datum_1983,SPHEROID[GRS_1980 ,6378137,298.257222101]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453 292519943295]],PROJECTION[Albers_Conic_Equal_Area],PARAMETER[False_ Easting,0],PARAMETER[False_Northing,0],PARAMETER[longitude_of_center ,- 96],PARAMETER[Standard_Parallel_1,29.5],PARAMETER[Standard_Parallel_2 ,45.5],PARAMETER[latitude_of_center,37.5],UNIT[Meter,1],AUTHORITY[E PSG,102003]]', '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=37.5 +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs '); I had some problems when I tried to add this projection from http://spatialreference.org/ref/esri/102003/postgis/ The names and values of srtext, and proj4text were reversed, and the SRID seemed incorrect so I changed it. I don't know if this is related to my question above, but I thought it worth mentioning. I did import the raster and polygon using this SRID on the query that worked. Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Question about using ST_Transform()
Thanks for the suggestion, Pierre. I turned out to be a mistake on my part as I used the first Albers Equal Area Conic listing I found on http://spatialreference.org. It turns out there are two. ESRI:102003 http://spatialreference.org/ref/esri/102003/: USA Contiguous Albers Equal Area Conic ESRI:102008 http://spatialreference.org/ref/esri/102008/: North America Albers Equal Area Conic -David On Thu, Feb 9, 2012 at 12:16 PM, Pierre Racine pierre.rac...@sbf.ulaval.cawrote: I generally display things in OpenJump to make sure they align correctly before doing anything instead of assuming they do. Do the projected geometries align properly with the rasters? You can display the raster extents easily in OpenJump by casting the raster to geometry (rast::geometry)... You can also do this easily in QGIS or ArcGIS 10. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto: postgis-users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 09, 2012 10:45 AM To: PostGIS Users Discussion Subject: [postgis-users] Question about using ST_Transform() Hello All, I'm trying to understand how to use ST_Transform() on a query. When I have everything in the same projection (in this case Albers Equal Area Conic) my query returns the correct result: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) as stats FROM ny_albers as r, cities_albers as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, p.geom) ) as foo; If I use a table with polygons in a different projection (in this case Web Mercator), I do not get any values returned: SELECT SUM((stats).sum) FROM ( select ST_SummaryStats(ST_Clip(r.rast, 1, ST_Transform(p.geom,102003), NULL, TRUE)) as stats FROM ny_albers as r, cities_webMercator as p WHERE p.name = 'new_york' AND ST_Intersects(r.rast, ST_Transform(p.geom,102003)) ) as foo; I added the projection SRID:102003 using the following lines: INSERT into spatial_ref_sys ( srid, auth_name, auth_srid, srtext, proj4text ) values ( 102003, -- I changed this line as it was originally 9102003 which did not satisfy the constraints 'esri', 102003, 'PROJCS[USA_Contiguous_Albers_Equal_Area_Conic,GEOGCS[GCS_North_A merican_1983,DATUM[North_American_Datum_1983,SPHEROID[GRS_1980 ,6378137,298.257222101]],PRIMEM[Greenwich,0],UNIT[Degree,0.017453 292519943295]],PROJECTION[Albers_Conic_Equal_Area],PARAMETER[False_ Easting,0],PARAMETER[False_Northing,0],PARAMETER[longitude_of_center ,- 96],PARAMETER[Standard_Parallel_1,29.5],PARAMETER[Standard_Parallel_2 ,45.5],PARAMETER[latitude_of_center,37.5],UNIT[Meter,1],AUTHORITY[E PSG,102003]]', '+proj=aea +lat_1=29.5 +lat_2=45.5 +lat_0=37.5 +lon_0=-96 +x_0=0 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs '); I had some problems when I tried to add this projection from http://spatialreference.org/ref/esri/102003/postgis/ The names and values of srtext, and proj4text were reversed, and the SRID seemed incorrect so I changed it. I don't know if this is related to my question above, but I thought it worth mentioning. I did import the raster and polygon using this SRID on the query that worked. Thank you, David ___ 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] Keeping vertices from lines in order... .
Hi All, If I'm converting from a line to its vertices, I use something like this (ala the postgis docs): SELECT ST_PointN( the_geom, generate_series(1, ST_NPoints(the_geom)) ) FROM ms_trails_test AS foo; Now, if I want to make sure I keep all those vertices in order for later, I better give them an id, so here's my original cludge: SELECT ST_PointN( the_geom, generate_series(1, ST_NPoints(the_geom)) ), generate_series(1, ST_NPoints(the_geom)) + foo.gid * 10 FROM ms_trails_test AS foo; Which is fine for most cases, but not really a general solution. . Better yet, I'll just keep my original gid as the feature id and have a separate vertex id: SELECT ST_PointN( the_geom, generate_series(1, ST_NPoints(the_geom)) ), gid as feature_id, generate_series(1, ST_NPoints(the_geom)) as vertex_id FROM ms_trails_test AS foo; Thus, I can play with the points and reassemble them back into lines later. But, it seems inefficient to run generate_series twice. Is this the best way? http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather Geographic Information Systems (GIS) Manager (216) 635-3243 s...@clevelandmetroparks.com http://www.clemetparks.com/ clevelandmetroparks.com image001.png___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] error: conversion to non-scalar type requested
Hi, I try to build the PostGIS from source. I followed the instruction until step 13: Compiling LibXML2. For some reason the following error shows up: testThreads.c: In function 'main': testThreads.c:110:6: error: conversion to non-scalar type requested However, if I compile it under Cygwin, it would not happen. It only appears in MinGW. Does anyone has any ideas? Thank you! Edison ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Keeping vertices from lines in order... .
Hi,
The new st_dumppoints() function may be useful in your case. It returns an
array of (path, point) that represents the line vertices:
select st_dumppoints('LINESTRING(0 0, 1 1, 2 2)'::geometry);
st_dumppoints
--
({1},010100)
({2},010100F03FF03F)
({3},0101400040)
(3 rows)
Nicolas
On 9 February 2012 21:57, Stephen V. Mather s...@clevelandmetroparks.comwrote:
Hi All,
If I’m converting from a line to its vertices, I use
something like this (ala the postgis docs):
** **
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
)
FROM ms_trails_test AS foo;
** **
Now, if I want to make sure I keep all those vertices in order for later,
I better give them an id, so here’s my original cludge:
** **
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
),
generate_series(1, ST_NPoints(the_geom)) + foo.gid * 10
FROM ms_trails_test AS foo;
** **
Which is fine for most cases, but not really a general solution… .
Better yet, I’ll just keep my original gid as the feature id and have a
separate vertex id:
** **
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
),
gid as feature_id,
generate_series(1, ST_NPoints(the_geom)) as vertex_id
FROM ms_trails_test AS foo;
** **
Thus, I can play with the points and reassemble them back into lines
later. But, it seems inefficient to run generate_series twice. Is this
the best way?
** **
** **
** **
** **
** **
**[image: http://www.clemetparks.com/images/esig/cmp-ms-90x122.png]**Stephen
Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243
s...@clevelandmetroparks.com
clevelandmetroparks.com http://www.clemetparks.com/
** **
** **
** **
** **
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
image001.png___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Keeping vertices from lines in order... .
Ah, and I see it's a 1.5 feature. Thank you.
Steve
http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) 635-3243
s...@clevelandmetroparks.com
http://www.clemetparks.com/ clevelandmetroparks.com
From: nicky...@gmail.com [mailto:nicky...@gmail.com] On Behalf Of Nicolas
Ribot
Sent: Thursday, February 09, 2012 4:40 PM
To: s...@clevelandmetroparks.com; PostGIS Users Discussion
Subject: Re: [postgis-users] Keeping vertices from lines in order... .
Hi,
The new st_dumppoints() function may be useful in your case. It returns an
array of (path, point) that represents the line vertices:
select st_dumppoints('LINESTRING(0 0, 1 1, 2 2)'::geometry);
st_dumppoints
--
({1},010100)
({2},010100F03FF03F)
({3},0101400040)
(3 rows)
Nicolas
On 9 February 2012 21:57, Stephen V. Mather s...@clevelandmetroparks.com
wrote:
Hi All,
If I'm converting from a line to its vertices, I use
something like this (ala the postgis docs):
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
)
FROM ms_trails_test AS foo;
Now, if I want to make sure I keep all those vertices in order for later, I
better give them an id, so here's my original cludge:
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
),
generate_series(1, ST_NPoints(the_geom)) + foo.gid * 10
FROM ms_trails_test AS foo;
Which is fine for most cases, but not really a general solution. .
Better yet, I'll just keep my original gid as the feature id and have a
separate vertex id:
SELECT
ST_PointN(
the_geom,
generate_series(1, ST_NPoints(the_geom))
),
gid as feature_id,
generate_series(1, ST_NPoints(the_geom)) as vertex_id
FROM ms_trails_test AS foo;
Thus, I can play with the points and reassemble them back into lines later.
But, it seems inefficient to run generate_series twice. Is this the best
way?
http://www.clemetparks.com/images/esig/cmp-ms-90x122.pngStephen Mather
Geographic Information Systems (GIS) Manager
(216) tel:%28216%29%20635-3243 635-3243
s...@clevelandmetroparks.com
http://www.clemetparks.com/ clevelandmetroparks.com
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
image001.png___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] error: conversion to non-scalar type requested
On 02/09/2012 01:13 PM, Tam, Edison (IS) wrote:
Hi,
I try to build the PostGIS from source. I followed the instruction until step
13: Compiling LibXML2. For some reason the following error shows up:
testThreads.c: In function 'main':
testThreads.c:110:6: error: conversion to non-scalar type requested
However, if I compile it under Cygwin, it would not happen. It only appears in
MinGW.
Does anyone has any ideas?
Thank you!
Edison
Edison,
Do what is suggested at the following page under the libxml2 section for
Windows.
http://threedepict.sourceforge.net/compiling.html
{{{
Ran ./configure
-- Failure on libtoolT (I ignored this).
Ran make
- Failed on testThreads.c. In that file, on line 110: tid[i]=
(pthread_t)-1; should be
tid[i].p= NULL;
tid[i].x=-1;
as pthread_t is a struct under windows ( see Pthreads faq, Q11)
looking in pthread.h:
typedef struct {
void p; /* Pointer to actual object */
unsigned int x; /* Extra information - reuse count
etc */
} ptw32_handle_t;
Ran make install
}}}
-bborie
--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Installing raster in POSTGIS 2.0
Hello, On Ubuntu 11.04 - the Natty Narwhal. I have tried installing various versions of POSTGIS(postgis-2.0.0alpha3SVN, postgis-2.0.0SVN, etc). but somehow the raster functions doesn't get installed. Please help. Please give me reliable link from where i can proceed step by step in installing POSTGIS 2.0 with raster capabilities. thanks Nikhil Morajkar Research Engineer, Geo-Spatial Information Science Engineering (GISE), Advance Research Lab, Indian Institute of Technology, Bombay (IITB) -- View this message in context: http://postgis.17.n6.nabble.com/Installing-raster-in-POSTGIS-2-0-tp4378919p4382328.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
