[postgis-users] Help with SQL query?

[Paragon Corporation]

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

 

 

 

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Darrel Maddy
Sent: Sunday, November 29, 2015 10:43 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Help with SQL query?

 

Dear Regina and Roxanne,

 

Many thanks for your suggestions.  I reran the query to show the breakdown of the sum elements as suggested. 

 

Now I know (or at least think I know) from the points version that there should be around 15500 intersect,  Looking at the table of information there are some odd counts for two rids.

 

rid           count    sum

1779       65536    16.78682

1810       65536    3.576006

 

There are 186 rids in total and although there are duplicate rids there are no duplicate counts that I can see, so I think that is OK. The summation of the sum column gives the high value previously reported.

 

The counts on the two rids shown above make little sense and they do, of course, seem to account for the discrepancy in sums (albeit removing these would overshoot the alternate sum at 0.898929)

 

I’m afraid this does not make me any wiser, especially as if I load the polygon and raster into QGIS from the same postgis tables and do the extraction there ( using grid statistics for polygons and then save the attribute table to a csv), the summation  gives the lower sum and a cell count of 15k which looks right.

 

I must confess I have no idea why the sql query returns these unusual counts and sums. The actual raw data tables appear to be fine.

 

Darrel

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paragon Corporation
Sent: 29 November 2015 03:30
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,

 

Try not summing.  That might give you more information about  what is wrong.  So instead do this:

 

WITH  foo AS (

  SELECT  mymodel.concentrated.rid,  mymodel.networkpoly.gid,  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, (st).*

FROM foo;

 

That might give you multiple records per rid which might be okay since a single tile might be clipped by more than one polygon.  At anyrate, you'll get a lot more stats and be able to target the tile/poly combo that might be at issue.

 

So your query should have

 

rid, gid, count, sum, mean, stddev, min, max  (for each tile/geom combo – which should hopefully give you a clue what went wrong).

 

Hope that helps,

Regina

 

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20151129/514e937f/attachment.html>