Re: [postgis-users] Help with ST_Within query issue...

;> |? | >> ?modified???????? | timestamp with time zone |?? >> |? | >> >> gsw=# SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) >> FROM Phenomena_v AS P >> WHERE >> ??? P.acq

Re: [postgis-users] Help with ST_Within query issue...

s scrubbed... URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230111/40fbb315/attachment-0001.htm> -- Message: 2 Date: Wed, 11 Jan 2023 08:35:59 -0800 From: Paul Ramsey To: PostGIS Users Discussion Subject: Re: [postgis-users] Help with ST_Within q

Re: [postgis-users] Help with ST_Within query issue...

Never seen that error before. Does your view reference any foreign tables in another database. My only guess is maybe some confusion with it reading the geometry table from another database. Does a query like: SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) FROM

Re: [postgis-users] Help with ST_Within query issue...

The error on pg10 is interesting and worrying, but at a first approximation your pg14 problem is that you have reversed the coordinate order in your polygon. The order should be longitude/latitude and yours is latitude/longitude. Try ST_GeomFromText('POLYGON((-119.53618 34.67010 , -116.02055

Re: [postgis-users] help with a query

Hi Jonatan, I am glad I could help. Have success with the tweaking! Regards, Birgit Am 26.09.2016 um 14:24 schrieb Jonatan Malaver: Hi Birgit, thank you so much!! your code put me on the right track. I'm still tweaking it a bit since your code assumes only 1 line is attached to the endpoint.

Re: [postgis-users] help with a query

Hi Birgit, thank you so much!! your code put me on the right track. I'm still tweaking it a bit since your code assumes only 1 line is attached to the endpoint. However, the endpoint of one line could break into 2 new starpoints. thanks, jon On Tue, Sep 20, 2016 at 5:38 AM Birgit Laggner

Re: [postgis-users] help with a query

Hi Jonatan, based on the anonymous code block Leknín came up with, I tried to adapt it to your problem: DO $$ DECLARE c_line record; c_gid integer; c_geom geometry; line record; gid integer; geom geometry; i integer; n integer; BEGIN EXECUTE 'SELECT count(*) FROM electric_line' INTO n;

Re: [postgis-users] help with a query

Hello again, I do not have parent line id. All I have is a starting point from where the direction should reference. On Wed, Sep 14, 2016 at 9:09 AM Leknín Řepánek wrote: > On Wed, Sep 14, 2016 at 12:09:23PM +, Jonatan Malaver wrote: > > the reason being is that I

Re: [postgis-users] help with a query

On Wed, Sep 14, 2016 at 12:09:23PM +, Jonatan Malaver wrote: > the reason being is that I do a network analysis by running the following > function: > WITH RECURSIVE flow(gid, geom) AS ( >     SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE > ST_Distance >

Re: [postgis-users] help with a query

the reason being is that I do a network analysis by running the following function: WITH RECURSIVE flow(gid, geom) AS ( SELECT e.gid, e.geom FROM electric_line e, transformers t WHERE ST_Distance(t.geom,ST_StartPoint(e.geom)) <= 0.01 AND t.gid=$1 UNION ALL SELECT n.gid, n.geom FROM

Re: [postgis-users] help with a query

This is the query I have so far: WITH RECURSIVE flow(gid, geom) AS ( SELECT e.gid, e.geom FROM electric_line e WHERE e.gid = 3312 UNION ALL SELECT n.gid, n.geom FROM electric_line n, flow f WHERE ST_DWithin(ST_EndPoint(f.geom),ST_StartPoint(n.geom),0.01) ) UPDATE electric_line

Re: [postgis-users] help with a query

ST_EndPoint, ST_StartPoint, ST_Reverse. On Tue, Sep 13, 2016 at 12:31:07PM +, Jonatan Malaver wrote: > Hello, > >    I'm trying to come up with a query that would check the direction of a > line. > If the end point is not the start point of the next line to update the line by > reversing

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

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?

Darrel, 1) Create a new raster selecting the right pixels with ST_MapAlgebra(raster, raster) 2) Make sure only intersecting rasters are processed by using their upper left corner X and Y coordinates (with ST_UpperLeftX() and UpperLeftY()) 3) Sum the selected pixels with

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