[postgis-users] ST_Clip
Hello I believe I am in appropriately using the ST_Clip function on a multiband raster. I have a raster with 13 bands and I want to retrieve from the clip bands # 1,3 How do I do that? I tried using: array[1,3] as bnd Didn't work still returns 13 bands. with bnd_num as ( select array[1,3] as bnd ) select p.geoid, p.label, ST_Numbands(ST_CLIP(r.rast,p.geom,b.bnd, True)) from bnd_num b, polygon p inner join modis_igbp_stack r on ST_Intersects(r.rast, p.geom); ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[postgis-users] ST_SummaryStats(ST_Clip(rast, geom)) with tiles
Hi I have a raster from which I'd to compute some stats. The raster have with tiles, so SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.prova -- give me the stats for each tiles SELECT (ST_SummaryStats(ST_Union(rast), 1)).* FROM rastertmp.prova -- give me the stats for all raster map, OK Now I'd like to intersect with a polygon map (ecoregion) and get the stats for each region: SELECT ecoregion_cod, (res).* FROM (SELECT p.ecoregion_cod, ST_SummaryStats(ST_Clip(r.rast,1, p.geom, true)) AS res FROM gis_wd.wd_ecoregiones AS p, rastertmp.prova AS r WHERE ST_Intersects(r.rast, p.geom) GROUP BY ecoregion_cod, res) AS foo; this give me the stats for each region in each tile, but I'd like the overall stats, i.e. the stats for each region in the whole raster. How can I do it? Including ST_Union before clipping didn't wok .. ERROR: aggregate functions are not allowed in GROUP BY Any other way? The only thing I can think off is to do it 'manually' using ST_ValueCount: SELECT ecoregion_cod, SUM(valor * suma)/SUM(suma) AS mean, SUM(suma) AS n, MIN(valor) AS min, MAX(valor) AS max FROM (SELECT ecoregion_cod, (res).value AS valor, SUM((res).count) AS suma FROM (SELECT p.ecoregion_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom, true)) AS res FROM gis_wd.wd_ecoregiones AS p, rastertmp.provanet AS r WHERE ST_Intersects(r.rast, p.geom) ) AS foo GROUP BY ecoregion_cod, valor ) AS foo2 GROUP BY ecoregion_cod ORDER BY ecoregion_cod; In this way I can compute the mean (and min, max), but I cannot compute StdDev from ValueCount. Is there a way? Thanks for any suggestion Regards Juli -- *CIDE, CSIC* | www.uv.es/jgpausas | ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Extension upgrade has no update path 2.1.1 to 2.1.7
Thank you for the reply. I think it is a problem with the installer we use from EnterpriseDB and not a bug with PostGIS. I tried installing PostgreSQL and Postgis using a different installer from KyngChaos. Once everything was installed I compared files in the directories and found many missing files. I assume these files are created when compiling the code? I am using the PostgreSQL/Stackbuilder combination from EnterpriseDB and I believe the installer does not include the appropriate files. I see on PostGIS¹s homepage, there is a recommendation not to use EnterpriseDB, due to mixed reliability. Case in point. We however, do use them and will be contacting them with the problem. I will send an update if anyone is interested when they get this fixed. postgis--2.0.0--2.1.7.sql postgis--2.0.1--2.1.7.sql postgis--2.0.2--2.1.7.sql postgis--2.0.3--2.1.7.sql postgis--2.0.4--2.1.7.sql postgis--2.0.5--2.1.7.sql postgis--2.0.6--2.1.7.sql postgis--2.1.0--2.1.7.sql postgis--2.1.0rc1--2.1.7.sql postgis--2.1.0rc2--2.1.7.sql postgis--2.1.0rc3--2.1.7.sql postgis--2.1.1--2.1.7.sql postgis--2.1.2--2.1.7.sql postgis--2.1.3--2.1.7.sql postgis--2.1.4--2.1.7.sql postgis--2.1.5--2.1.7.sql postgis--2.1.6--2.1.7.sql postgis--2.1.7--2.1.7next.sql postgis--2.1.7next--2.1.7.sql postgis--unpackaged--2.1.7.sql postgis_tiger_geocoder--2.0.0--2.1.7.sql postgis_tiger_geocoder--2.0.1--2.1.7.sql postgis_tiger_geocoder--2.0.2--2.1.7.sql postgis_tiger_geocoder--2.0.3--2.1.7.sql postgis_tiger_geocoder--2.0.4--2.1.7.sql postgis_tiger_geocoder--2.0.5--2.1.7.sql postgis_tiger_geocoder--2.0.6--2.1.7.sql postgis_tiger_geocoder--2.1.0--2.1.7.sql postgis_tiger_geocoder--2.1.0rc1--2.1.7.sql postgis_tiger_geocoder--2.1.0rc2--2.1.7.sql postgis_tiger_geocoder--2.1.0rc3--2.1.7.sql postgis_tiger_geocoder--2.1.1--2.1.7.sql postgis_tiger_geocoder--2.1.2--2.1.7.sql postgis_tiger_geocoder--2.1.3--2.1.7.sql postgis_tiger_geocoder--2.1.4--2.1.7.sql postgis_tiger_geocoder--2.1.5--2.1.7.sql postgis_tiger_geocoder--2.1.6--2.1.7.sql postgis_tiger_geocoder--2.1.7--2.1.7next.sql postgis_tiger_geocoder--2.1.7next--2.1.7.sql postgis_tiger_geocoder--unpackaged--2.1.7.sql postgis_topology--2.0.0--2.1.7.sql postgis_topology--2.0.1--2.1.7.sql postgis_topology--2.0.2--2.1.7.sql postgis_topology--2.0.3--2.1.7.sql postgis_topology--2.0.4--2.1.7.sql postgis_topology--2.0.5--2.1.7.sql postgis_topology--2.0.6--2.1.7.sql postgis_topology--2.1.0--2.1.7.sql postgis_topology--2.1.0rc1--2.1.7.sql postgis_topology--2.1.0rc2--2.1.7.sql postgis_topology--2.1.0rc3--2.1.7.sql postgis_topology--2.1.1--2.1.7.sql postgis_topology--2.1.2--2.1.7.sql postgis_topology--2.1.3--2.1.7.sql postgis_topology--2.1.4--2.1.7.sql postgis_topology--2.1.5--2.1.7.sql postgis_topology--2.1.6--2.1.7.sql postgis_topology--2.1.7--2.1.7next.sql postgis_topology--2.1.7next--2.1.7.sql postgis_topology--unpackaged--2.1.7.sql On 7/22/15, 1:22 AM, Paragon Corporation l...@pcorp.us wrote: Paul, I just checked my build and I have a 2.1.1-2.1.7.sql so whatever the issue is I don't think is a bug in PostGIS. Can you check your share/extension folder of your postgresql install and verify that you do have a file called postgis-2.1.1--2.1.7.sql ? I suspect you don't. If you do have all the postgis extension files you should also have a file called postgis--2.1.7.sql Did you build PostGIS yourself or got from a distribution? Could be a packaging flaw that you need to report to the package maintainer that you are missing extension files. Thanks, Regina http://www.postgis.us http://postgis.net -Original Message- From: postgis-devel-boun...@lists.osgeo.org [mailto:postgis-devel-boun...@lists.osgeo.org] On Behalf Of Sandro Santilli Sent: Tuesday, July 21, 2015 12:11 PM To: Moen, Paul T. pm...@nd.gov Cc: postgis-de...@lists.osgeo.org Subject: Re: [postgis-devel] [postgis-users] Extension upgrade has no update path 2.1.1 to 2.1.7 On Mon, Jul 20, 2015 at 08:25:04PM +, Moen, Paul T. wrote: I am upgrading from postgis 2.1.1 to 2.1.7 using extensions. After entering the following, ALTER EXTENSION postgis UPDATE TO 2.1.7�; I get error ERROR: extension postgis has no update path from version 2.1.1 to version 2.1.7 Sounds like a bug, could you please file it on trac ? http://trac.osgeo.org/postgis/ --strk; ___ postgis-devel mailing list postgis-de...@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel ___ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
