[postgis-users] Help with SQL query?

[Darrel Maddy]

Dear Regina,

Just to wrap this one up – you were right!

I decided to make the network file in arcgis and then import that into my postgis db.  I then ran the same query but with the new polygon and the totals were exactly the same as the point file and the data I extracted in QGIS.

It is clear that I need to look at how I created the polygon in postgis (although whatever problems this caused they were ignored by QGIS).  Thankfully I can do what I need to do now (the polygon extraction takes around 25 minutes which is half the time the point file took and that will do !), and I am happy with the workflow.

Many thanks again for all of your help and there is no need to reply. I promise to be quiet for a while now.

Best wishes

Darrel





From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paragon Corporation
Sent: 29 November, 2015 4:08 PM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
Subject: Re: [postgis-users] Help with SQL query?

Darrel,

I'm a bit confused about something.  I thought concentrated was what you were using to determine whether or not to consider a pixel in the deposition raster, so shouldn't the query be:

WITH  foo AS (
  SELECT  mymodel.deposition.rid,  mymodel.networkpoly.gid,  geom,  ST_SummaryStats( ST_Clip(rast, geom) ) As st
            FROM mymodel.deposition INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )
           WHERE filename='10_inci.tif'
)
SELECT sum( (st).sum)
FROM foo;


As your original query – was using deposition.


CREATE TABLE mymodel.networkdep AS

SELECT filename, gid, ST_Value(rast, geom) val

FROM mymodel.deposition, mymodel.network

WHERE ST_Intersects(rast, geom)

ORDER BY gid, rid;


Gathering from the count it looks like all the pixels of those are treated as data when some should be treated as no data.  If it's not an error in the raster you are using,

it would really help to get output of the polygon and also the other stats I mentioned which (st).* outputs  So a query something like:

WITH  foo AS (
  SELECT  mymodel.concentrated.rid,  mymodel.networkpoly.gid,  geom,  ST_SummaryStats( ST_Clip(rast, geom) ) As st
            FROM mymodel.concentrated INNER JOIN mymodel.networkpoly ON ( ST_Intersects(rast,geom) )
           WHERE filename='10_inci.tif'
)
SELECT rid, gid, geom, (st).*
FROM foo;


You might actually want to plot the geom on the map overlaid with the problem tiles (or original raster) you classified for that region.

My guess is something went wrong in the ST_Reclass and all the pixels in those tiles did not get set to 0 (and thus are treated as data doing a summary stats of the problem pre-reclassed tiles and looking at the min,max will give you a clue about that).
This second point may be moot if it's just you meant to put deposition instead of concentration, but good knowledge to keep in mind for future troubleshooting.

Hope that helps,
Regina



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151130/3d827425/attachment.html>