Re: [postgis-users] Help with SQL query?

Of Darrel Maddy Sent: Sunday, November 29, 2015 7:38 PM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Dear Regina, Just to wrap this one up – you were right! I decided to make the network file in arcgis and then

Re: [postgis-users] Help with SQL query?

ct: 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

Re: [postgis-users] Help with SQL query?

] On Behalf Of Darrel Maddy Sent: Sunday, November 29, 2015 10:43 AM To: PostGIS Users Discussion <postgis-users@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 t

Re: [postgis-users] Help with SQL query?

the same. Darrel From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Paragon Corporation Sent: 29 November, 2015 4:08 PM To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Darrel, I'm a bit confused

Re: [postgis-users] Help with SQL query?

To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org<mailto:postgis-users@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 cons

Re: [postgis-users] Help with SQL query?

gis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>> Subject: Re: [postgis-users] Help with SQL query? For completeness, your vector query would then look like CREATE TABLE mymodel.network AS SELECT rid As gid, ST_Polygon(ST_Reclass(rast, 1, '[0-600):0, [600-1]

Re: [postgis-users] Help with SQL query?

:51 To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Dear Regina, Wow that makes a tremendous difference. I used this polygon to extract the values using a modified form of the query you suggested in an earlier post and the

Re: [postgis-users] Help with SQL query?

[mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: Saturday, November 28, 2015 6:43 PM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Oh dear I spoke too soon! I created the polygon as di

Re: [postgis-users] Help with SQL query?

com<mailto:pcr...@yahoo.com>>; postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Dear Brent, Many thanks. The data are tiled (256x256) hence the large number of rows from the original 135 tifs. I did not build any

Re: [postgis-users] Help with SQL query?

eo.org> Subject: Re: [postgis-users] Help with SQL query? Darrel, Oh that's what you are trying to do sorry I didn't recognize that whole CASE thing as a binary check operation until you described the purpose. For the bit operation type stuff it is much faster to define that 0/1 as a geomet

Re: [postgis-users] Help with SQL query?

ers@lists.osgeo.org>; Darrel Maddy <darrel.ma...@newcastle.ac.uk> Subject: Re: [postgis-users] Help with SQL query? Darrel You can use the logical operators in the QGIS raster calculator as a binary conditional : eg: raster a is used to generate a new raster with default 0, but pixel value=1

Re: [postgis-users] Help with SQL query?

ubject: Re: [postgis-users] Help with SQL query? Dear Regina, Many thanks for this suggestion. I ran the query in this form and for one raster it takes 3372s (~55 mins). I guess that is what I had anticipated from the single tile exercise I ran with the alternate algorithm. This

Re: [postgis-users] Help with SQL query?

] On Behalf Of Darrel Maddy Sent: Friday, November 27, 2015 4:09 PM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Dear Regina, Yes, I should have been clearer. To be honest I did not fully understand what I was trying

Re: [postgis-users] Help with SQL query?

eo.org] On Behalf Of Paragon Corporation Sent: Friday, November 27, 2015 10:04 PM To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Darrel, I think the equivalent in PostGIS terminology would be ST_Reclass - <http://p

Re: [postgis-users] Help with SQL query?

ttp://postgis.net From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: Wednesday, November 25, 2015 5:06 PM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>; Brent Wood <pcr...@yahoo.com> Subject: Re: [postgis-users] Help with SQL

Re: [postgis-users] Help with SQL query?

.ma...@newcastle.ac.uk>; postgis-users@lists.osgeo.org Subject: Re: [postgis-users] Help with SQL query? Indexing can improve performance by 100s of x, without them things can be slow. Also, did you tile the images when you imported them? If not, then each iteration is working through all the

[postgis-users] Help with SQL query?

darrel.ma...@newcastle.ac.uk>> Subject: Re: [postgis-users] Help with SQL query? Hi Darrel, This has been a useful thread for others than yourself! Can you post the entire final query once it is working? Otherwise we need to read through all the emails to build it up as you have done with advice fr

Re: [postgis-users] Help with SQL query?

trying. Darrel From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Roxanne Reid-Bennett Sent: 24 November, 2015 12:46 AM To: postgis-users@lists.osgeo.org Subject: Re: [postgis-users] Help with SQL query? On 11/23/2015 12:41 PM, Darrel Maddy wrote: Dear Pierre, I was not l

Re: [postgis-users] Help with SQL query?

12:46 AM *To:* postgis-users@lists.osgeo.org *Subject:* Re: [postgis-users] Help with SQL query? On 11/23/2015 12:41 PM, Darrel Maddy wrote: Dear Pierre, I was not looking for a total solution and I am grateful for the suggestion. Although it may not look like it, I di

Re: [postgis-users] Help with SQL query?

*Sent:* 24 November 2015 12:19 *To:* postgis-users@lists.osgeo.org *Subject:* Re: [postgis-users] Help with SQL query? Hi Darrel, my PostGIS version is too old for testing, but if I read the documentation right, then your expression has to be SQL. And IF THEN ELSE etc. is not SQL as far as I know

Re: [postgis-users] Help with SQL query?

will confirm when it stops! Best wishes Darrel From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Birgit Laggner Sent: 24 November 2015 12:19 To: postgis-users@lists.osgeo.org Subject: Re: [postgis-users] Help with SQL query? Hi Darrel, my PostGIS version is too

Re: [postgis-users] Help with SQL query?

ers [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: 23 November 2015 16:30 To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Dear Pierre and Rasmus, Many thanks for trying to help. Rasmus: I am awa

Re: [postgis-users] Help with SQL query?

boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: Monday, November 23, 2015 2:47 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Help with SQL query? OK I spoke too soon. I tried this: SELECT (ST_SummaryStats(ST_Union(rast))).sum sum FROM (SELECT ST_MapAlgebra(deposition.

Re: [postgis-users] Help with SQL query?

lto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Darrel Maddy *Sent:* Monday, November 23, 2015 2:47 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] Help with SQL query? OK I spoke too soon. I tried this: SELECT (ST_SummaryStats(ST_Union(rast))).sum sum FROM (SELECT ST_MapA

Re: [postgis-users] Help with SQL query?

Good catch Roxanne! Thanks! From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Roxanne Reid-Bennett Sent: Monday, November 23, 2015 7:46 PM To: postgis-users@lists.osgeo.org Subject: Re: [postgis-users] Help with SQL query? On 11/23/2015 12:41 PM, Darrel Maddy wrote

Re: [postgis-users] Help with SQL query?

Regarding filename matching etc. I would just wrap everything in a scripting language of your choice (bash, python, ruby etc.) considering all the GMT programs, including grdmath, are command line tools. -ra On 23 Nov 2015, at 16:27, Rasmus Aveskogh wrote: > > Though this

Re: [postgis-users] Help with SQL query?

Though this is probably possible in PostGIS nowadays I would personally solve such a use case with GMT (http://gmt.soest.hawaii.edu/), for example with grdmath (http://gmt.soest.hawaii.edu/doc/5.2.1/grdmath.html) which lets you perform rather complex computations on multiple grids (for

Re: [postgis-users] Help with SQL query?

gis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: Monday, November 23, 2015 7:20 AM To: PostGIS Users Discussion Subject: [postgis-users] Help with SQL query? Dear all, As you know I am relatively new to postgis and SQL and therefore I have much to learn. However, I am fac

Re: [postgis-users] Help with SQL query?

: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Pierre Racine Sent: 23 November 2015 16:17 To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Subject: Re: [postgis-users] Help with SQL query? Darrel, 1) Create a new raster selecting the right

Re: [postgis-users] Help with SQL query?

rs [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Darrel Maddy Sent: Monday, November 23, 2015 2:47 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Help with SQL query? OK I spoke too soon. I tried this: SELECT (ST_SummaryStats(ST_Union(rast))).sum sum FROM (SELECT