[postgis-users] Ring Self-intersection problem
hello Could you give me an idea to solve this problem ? I have so many self intersection in my map: "Ring Self-intersection at or near point 39.0002 37.8181" regards -- Ahmet Temiz Jeoloji Müh. Afet ve Acil Durum Yönetimi Başkanlığı Planlama ve Zarar Azaltma Dairesi Başkanlığı Bilgi ve CBS grubu Eskişehir Yolu 10. km. Lodumlu / Ankara Tel : 0 312 2872680 / 1535 Ahmet Temiz Geological Eng. Information Systems - GIS Group Disaster and Emergency Management of Presidency ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
Andreas, My way of solving this issue is to do a weighted mean based on the area of the part of the pixel intersecting. ST_Intersection() return a geomval of the true intersecting part. You must weigh the elevation with the area of the intersecting part. I explain how to do this at the end of the tutorial: http://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01 Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Andreas Forø Tollefsen > Sent: Thursday, November 24, 2011 5:14 AM > To: PostGIS Users Discussion > Subject: [postgis-users] Raster pixel value > > Hi, > > I am trying to calculate the average pixel value in a elevation raster inside > quadrate polygons. > However, I am not getting the correct values from my query: > > SELECT gid, AVG(((foo.geomval).val)) as avgmnt FROM (SELECT p.gid, > ST_Intersection(p.cell, r.rast) AS geomval FROM mountain r, priogrid_land p > WHERE ST_Intersects(p.cell, r.rast, ) AND p.gid =186124) AS foo GROUP BY > gid ORDER BY gid; > > The problem here is that the ST_Intersects(geom, rast) takes into > consideration the pixels that is outside, but touches the border of the > quadrate polygons. Then, the average values for each quadrate polygon is > affected by pixels inside other polygons. This will potentially lead to a > flawed result. > So what I want is to be able to calculate the average value for the pixels > INSIDE the polygon excluding those outside. > > How can i restrict the AVG pixel value to be calculated only for pixels that > is > inside the polygon, and not the pixels that touch the outside of the border? > > Thanks! > > Best, > Andreas > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
Update: I think my suspicion is correct. If I do a ST_Summarystats().sum and divide this on 36 my MAX value will be 1. Hence, I think the number of values counted and the number of observations counted is not equal. New query: DROP TABLE IF EXISTS mountain_phil_cell; SELECT a.gid As gid, (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell, b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))).rast, false)).sum / 36 As avgmnt INTO mountain_phil_cell FROM priogrid_land a LEFT JOIN mountain_phil b ON ST_Intersects(a.cell, b.rast) GROUP BY a.gid ORDER BY a.gid; 2011/11/25 Andreas Forø Tollefsen > A small note regarding this issue. > > My problem is that I never get a mean value of 1 even if all pixels inside > the geometry is one. > > Could this be because: 6x6 pixels goes into one polygon when visually > controlling. If each pixel has the value 1, then this will be calculated as > 36 / 36 = 1. However, if it calculates the sum to be 36 and divide by a > number higher than 36 pixels, then the result will always be below 1. > What i am thinking is that while it sums up the pixel values correctly, it > does not count only the 36 pixels, but also neighboring pixels. Therefore: > 1+1+1+1...n36 / Number of pixels higher than 36 will always lead to a > number lower than 1. > > Anyone who knows the functions well could probably answer this. > > Best regards, > Andreas > > > 2011/11/25 Andreas Forø Tollefsen > >> Could this have to do with the tiling of the raster? >> I will try to run the same query with a untiled mountain raster to see if >> that changes anything. >> >> Btw. When loading a tiled postgis raster into qgis it shows up with many >> artifacts and no data areas. The same raster untiled does not show up the >> same way. >> Qgis bug? >> >> Andreas >> >> 2011/11/25 Andreas Forø Tollefsen >> >>> Hi, >>> >>> Thanks for all of the suggestions. I will do some more testing. However, >>> as for suggestion 1 i think the pixel size should be the same as the >>> original raster or am I wrong? >>> >>> Both the mean_mnt_bin raster and the priogrid_land shapefile can be >>> downloaded as zip (2 mb) here: >>> http://gisintersect.com/mean_mnt_bin.zip >>> http://gisintersect.com/priogrid_land.zip >>> >>> Any help on getting the correct values would be very much appreciated. >>> >>> My query: >>> DROP TABLE IF EXISTS mountain_cell; >>> >>> SELECT >>> a.gid As id, >>> (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell, b.rast, >>> '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))).rast, >>> false)).mean As avgmnt >>> INTO mountain_cell >>> FROM >>> priogrid_land a LEFT JOIN >>> mountain b >>> ON ST_Intersects(a.cell, b.rast) >>> GROUP BY a.gid >>> ORDER BY a.gid; >>> >> >> > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
A small note regarding this issue. My problem is that I never get a mean value of 1 even if all pixels inside the geometry is one. Could this be because: 6x6 pixels goes into one polygon when visually controlling. If each pixel has the value 1, then this will be calculated as 36 / 36 = 1. However, if it calculates the sum to be 36 and divide by a number higher than 36 pixels, then the result will always be below 1. What i am thinking is that while it sums up the pixel values correctly, it does not count only the 36 pixels, but also neighboring pixels. Therefore: 1+1+1+1...n36 / Number of pixels higher than 36 will always lead to a number lower than 1. Anyone who knows the functions well could probably answer this. Best regards, Andreas 2011/11/25 Andreas Forø Tollefsen > Could this have to do with the tiling of the raster? > I will try to run the same query with a untiled mountain raster to see if > that changes anything. > > Btw. When loading a tiled postgis raster into qgis it shows up with many > artifacts and no data areas. The same raster untiled does not show up the > same way. > Qgis bug? > > Andreas > > 2011/11/25 Andreas Forø Tollefsen > >> Hi, >> >> Thanks for all of the suggestions. I will do some more testing. However, >> as for suggestion 1 i think the pixel size should be the same as the >> original raster or am I wrong? >> >> Both the mean_mnt_bin raster and the priogrid_land shapefile can be >> downloaded as zip (2 mb) here: >> http://gisintersect.com/mean_mnt_bin.zip >> http://gisintersect.com/priogrid_land.zip >> >> Any help on getting the correct values would be very much appreciated. >> >> My query: >> DROP TABLE IF EXISTS mountain_cell; >> >> SELECT >> a.gid As id, >> (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell, b.rast, >> '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))).rast, >> false)).mean As avgmnt >> INTO mountain_cell >> FROM >> priogrid_land a LEFT JOIN >> mountain b >> ON ST_Intersects(a.cell, b.rast) >> GROUP BY a.gid >> ORDER BY a.gid; >> > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] bmd.hasnodata does not exist
Hi, Sorry for posting a lot these days but I am very busy finalizing a project. Previously, I have been running the following query without problems (in rev.8001). After updating to rev.8242 i am getting a strange error. Could someone please see what is wrong? Is it one of the functions that has changed? Query: SELECT gid, AVG(((foo.geomval).val)) FROM (SELECT p.gid, ST_Intersection(p.cell, r.rast) AS geomval FROM mountain r, priogrid_land p WHERE ST_Intersects(p.cell, r.rast) AND p.gid =219260) AS foo GROUP BY gid ORDER BY gid; Error: ERROR: column bmd.hasnodata does not exist LINE 1: SELECT bmd.hasnodataFROM ST_BandMetaData(ras... ^ QUERY: SELECT bmd.hasnodataFROM ST_BandMetaData(rast, nband) AS bmd CONTEXT: PL/pgSQL function "_st_intersects" line 32 at SQL statement PL/pgSQL function "st_intersection" line 5 at assignment ** Error ** ERROR: column bmd.hasnodata does not exist SQL state: 42703 Context: PL/pgSQL function "_st_intersects" line 32 at SQL statement PL/pgSQL function "st_intersection" line 5 at assignment Thanks for any help. Best regards, Andreas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Undefined symbol "list_length" ? postgis 1.5.3 from FreeBSD ports
Actually, that did the trick - a complete clean, deinstall and reinstall of everything. cheers Ben On 25/11/2011, at 5:34 PM, Sandro Santilli wrote: > On Fri, Nov 25, 2011 at 04:15:18PM +0800, Ben Madin wrote: >> G'day all, >> >> trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on >> FreeBSD7, I am receiving this message : >> >> # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql >> SET >> BEGIN >> psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: >> could not load library "/usr/local/lib/postgresql/postgis.so": dlopen >> (/usr/local/lib/postgresql/postgis.so) failed: >> /usr/local/lib/postgresql/postgis.so: Undefined symbol "list_length" > > Sounds like a mismatch between PostgreSQL headers and PostgreSQL library. > Do you have two versions of PostgreSQL ? > Did you reconfigure postgis source code after upgrading postgresql ? > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Undefined symbol "list_length" ? postgis 1.5.3 from FreeBSD ports
Thanks Sandro, I can only find one installation of postgres (in /usr/local/bin/postgres) There are two clusters running on the machine (and have been for years), and it was upgraded to 9.0 a couple of months ago, and postgis was 1.5.3, but I can't install it. (interestingly, I haven't be able to reinstall pl/r either, which is driving me insane) I'm reinstalling postgres and postgis from scratch (using ports), so I'll let you know what I find cheers Ben On 25/11/2011, at 5:34 PM, Sandro Santilli wrote: > On Fri, Nov 25, 2011 at 04:15:18PM +0800, Ben Madin wrote: >> G'day all, >> >> trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on >> FreeBSD7, I am receiving this message : >> >> # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql >> SET >> BEGIN >> psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: >> could not load library "/usr/local/lib/postgresql/postgis.so": dlopen >> (/usr/local/lib/postgresql/postgis.so) failed: >> /usr/local/lib/postgresql/postgis.so: Undefined symbol "list_length" > > Sounds like a mismatch between PostgreSQL headers and PostgreSQL library. > Do you have two versions of PostgreSQL ? > Did you reconfigure postgis source code after upgrading postgresql ? > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Undefined symbol "list_length" ? postgis 1.5.3 from FreeBSD ports
On Fri, Nov 25, 2011 at 04:15:18PM +0800, Ben Madin wrote: > G'day all, > > trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on > FreeBSD7, I am receiving this message : > > # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql > SET > BEGIN > psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: > could not load library "/usr/local/lib/postgresql/postgis.so": dlopen > (/usr/local/lib/postgresql/postgis.so) failed: > /usr/local/lib/postgresql/postgis.so: Undefined symbol "list_length" Sounds like a mismatch between PostgreSQL headers and PostgreSQL library. Do you have two versions of PostgreSQL ? Did you reconfigure postgis source code after upgrading postgresql ? --strk; () Free GIS & Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
Could this have to do with the tiling of the raster? I will try to run the same query with a untiled mountain raster to see if that changes anything. Btw. When loading a tiled postgis raster into qgis it shows up with many artifacts and no data areas. The same raster untiled does not show up the same way. Qgis bug? Andreas 2011/11/25 Andreas Forø Tollefsen > Hi, > > Thanks for all of the suggestions. I will do some more testing. However, > as for suggestion 1 i think the pixel size should be the same as the > original raster or am I wrong? > > Both the mean_mnt_bin raster and the priogrid_land shapefile can be > downloaded as zip (2 mb) here: > http://gisintersect.com/mean_mnt_bin.zip > http://gisintersect.com/priogrid_land.zip > > Any help on getting the correct values would be very much appreciated. > > My query: > DROP TABLE IF EXISTS mountain_cell; > > SELECT > a.gid As id, > (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell, b.rast, > '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))).rast, > false)).mean As avgmnt > INTO mountain_cell > FROM > priogrid_land a LEFT JOIN > mountain b > ON ST_Intersects(a.cell, b.rast) > GROUP BY a.gid > ORDER BY a.gid; > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster pixel value
Hi, Thanks for all of the suggestions. I will do some more testing. However, as for suggestion 1 i think the pixel size should be the same as the original raster or am I wrong? Both the mean_mnt_bin raster and the priogrid_land shapefile can be downloaded as zip (2 mb) here: http://gisintersect.com/mean_mnt_bin.zip http://gisintersect.com/priogrid_land.zip Any help on getting the correct values would be very much appreciated. My query: DROP TABLE IF EXISTS mountain_cell; SELECT a.gid As id, (ST_SummaryStats((ST_Union(ST_MapAlgebraExpr(ST_AsRaster(a.cell, b.rast, '32BF'), b.rast, 'rast2', '32BF','INTERSECTION','0','0',0))).rast, false)).mean As avgmnt INTO mountain_cell FROM priogrid_land a LEFT JOIN mountain b ON ST_Intersects(a.cell, b.rast) GROUP BY a.gid ORDER BY a.gid; ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Undefined symbol "list_length" ? postgis 1.5.3 from FreeBSD ports
G'day all, trying to install (reinstall) postgis 1.5.3 into postgresql 9.0.4 on FreeBSD7, I am receiving this message : # \i /usr/local/share/postgis/contrib/postgis-1.5/postgis.sql SET BEGIN psql:/usr/local/share/postgis/contrib/postgis-1.5/postgis.sql:59: ERROR: could not load library "/usr/local/lib/postgresql/postgis.so": dlopen (/usr/local/lib/postgresql/postgis.so) failed: /usr/local/lib/postgresql/postgis.so: Undefined symbol "list_length" I've never seen this one before, and Google is not being very helpful... I'm suspecting it is something about the configuration of postgresql, so I'm rebuilding it, but any ideas would be much appreciated. FWIW, this was all working previously, and I'm not quite sure why it has now stopped. cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
