[postgis-users] Help with SQL query?

[Darrel Maddy]

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>
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/1b1daff3/attachment.html>