[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] postgis-users Digest, Vol 239, Issue 7 , Postgis Raster determine exact hull
From: "Regina Obe" <lr () pcorp ! us>
Date: 2022-01-13 0:13:22
Message-ID: 000701d80812$611bb930$23532b90$ () pcorp ! us
[Download RAW message or body]
This is a multipart message in MIME format.
[Attachment #2 (multipart/alternative)]
This is a multipart message in MIME format.
Curious what was the performance for ST_Polygon with ST_reclass?
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] On Behalf Of \
Richard Huesken
Sent: Wednesday, January 12, 2022 4:05 PM
To: postgis-users@lists.osgeo.org
Subject: Re: [postgis-users] postgis-users Digest, Vol 239, Issue 7 , Postgis Raster \
determine exact hull
Thanks Marcin and Regina.
I tried and combined both options. For a particular raster, the st_reclass is much \
faster (33 milliseconds) compared to just using the st_polygon (4.5 seconds) . I \
included these examples for other users (the st_reclass syntax is a bit harder to \
understand) .
-- FAST
select (st_dumpaspolygons(st_reclass(r.raster_data, 1, '(-10000 - 9999):1', '4BUI', \
0) )).* from spc_tile_rasters r
where r.id <http://r.id/> = 20818
select st_polygon(st_reclass(r.raster_data, 1, '(-10000 - 9999):1', '4BUI', 0) )
from spc_tile_rasters r
where r.id <http://r.id/> = 20818
-- SLOW
select st_polygon(r.raster_data)
from spc_tile_rasters r
where r.id <http://r.id/> = 20818
Kind regards,
Richard.
Op di 11 jan. 2022 om 21:00 schreef <postgis-users-request@lists.osgeo.org \
<mailto:postgis-users-request@lists.osgeo.org> >:
Send postgis-users mailing list submissions to
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.osgeo.org/mailman/listinfo/postgis-users
or, via email, send a message with subject or body 'help' to
postgis-users-request@lists.osgeo.org \
<mailto:postgis-users-request@lists.osgeo.org>
You can reach the person managing the list at
postgis-users-owner@lists.osgeo.org \
<mailto:postgis-users-owner@lists.osgeo.org>
When replying, please edit your Subject line so it is more specific
than "Re: Contents of postgis-users digest..."
Today's Topics:
1. Postgis Raster determine exact hull (Richard Huesken)
2. Line segment and its variation over space (Shaozhong SHI)
3. Computing overall trend presented by a 3D line (Shaozhong SHI)
4. Any function to compute line trend and identify segment
running in flat areas (Shaozhong SHI)
5. Re: hard upgrade from 1.5 (Sandro Santilli)
6. PostGIS problem after updating from 3.1.4 to 3.2.0 (Calle Hedberg)
7. Re: PostGIS problem after updating from 3.1.4 to 3.2.0
(Regina Obe)
8. Re: PostGIS problem after updating from 3.1.4 to 3.2.0
(Regina Obe)
9. Re: Postgis Raster determine exact hull (Marcin Mionskowski)
10. Re: Postgis Raster determine exact hull (Regina Obe)
11. Re: PostGIS problem after updating from 3.1.4 to 3.2.0
(Calle Hedberg)
12. Re: How best to create and use associative array type in
Postgres? (Shaozhong SHI)
13. Using Spike finder in PostGIS? (Shaozhong SHI)
14. Re: hard upgrade from 1.5 (Nathan Wagner)
15. Re: hard upgrade from 1.5 (Paul Ramsey)
----------------------------------------------------------------------
Message: 1
Date: Mon, 10 Jan 2022 21:26:40 +0100
From: Richard Huesken <richard.huesken@gmail.com <mailto:richard.huesken@gmail.com> >
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Subject: [postgis-users] Postgis Raster determine exact hull
Message-ID:
<CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q@mail.gmail.com \
<mailto:CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
hi,
I'm using postgis 3.1 and I'm looking for the best way to obtain the exact
hull of a raster (excluding the nodata points). The st_minconvexhull uses
the MBR of the raster coverage, and is therefore quite fast. The result is
however not as accurate as I require.
I constructed some sql that uses st_pixelaspolygons and then does a
st_union. However, My typical raster has 256x256 points, and with several
100s of rasters this is quite slow.
Are there more clever (and faster!) ways to get the exact hull of a raster?
Thanks in advance,
Richard.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/ada538a6/attachment-0001.html>
------------------------------
Message: 2
Date: Mon, 10 Jan 2022 23:38:46 +0000
From: Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] Line segment and its variation over space
Message-ID:
<CA+i5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ@mail.gmail.com \
<mailto:CA%2Bi5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
3D Line segments can be used for delineating riverine systems. In nature,
some rivers run in steep gradients and others in flat areas.
In geocomputation, rules are needed in order to compute lines running in
steep gradients and lines in flat areas.
Surely, there are ways to make computed decision on which lines running in
flat areas.
How to devise and implement such rules is of interest.
Any enlightening recommendations and suggestions?
Regards,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/7d8b3022/attachment-0001.html>
------------------------------
Message: 3
Date: Mon, 10 Jan 2022 23:56:48 +0000
From: Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] Computing overall trend presented by a 3D
line
Message-ID:
<CA+i5JwZ2v6wN2Yj-d7EHkZhqQb6+ttgYEDQpDtyjCAmWb1cFxw@mail.gmail.com \
<mailto:CA%2Bi5JwZ2v6wN2Yj-d7EHkZhqQb6%2BttgYEDQpDtyjCAmWb1cFxw@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
3D lines can be used to delineated natural phenomena. There are various
ways to compute the overall trend of a 3D line to determine whether the
line is running downward or upwards.
What are the best ways to compute this in PostGIS?
Regards,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/39b61815/attachment-0001.html>
------------------------------
Message: 4
Date: Tue, 11 Jan 2022 00:07:08 +0000
From: Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] Any function to compute line trend and
identify segment running in flat areas
Message-ID:
<CA+i5JwZbGH+U35D8wT4OYvBJ0WHBn4PDAgVES_pwqfBYzpnsvQ@mail.gmail.com \
<mailto:CA%2Bi5JwZbGH%2BU35D8wT4OYvBJ0WHBn4PDAgVES_pwqfBYzpnsvQ@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
A line may run steeply downslope and then over flat areas.
Any generic function to determine so?
Input: geometry and relative overall gradient
Output: the segment running steeply, the segment running in flat area
Any recommendations and suggestions?
Regards,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/6851a119/attachment-0001.html>
------------------------------
Message: 5
Date: Tue, 11 Jan 2022 01:18:27 +0100
From: Sandro Santilli <strk@kbt.io <mailto:strk@kbt.io> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <YdzM00goGhQyBFpM@c19>
Content-Type: text/plain; charset=us-ascii
On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote:
> > On Jan 10, 2022, at 10:01 AM, Nathan Wagner <nw@hydaspes.if.org \
> > <mailto:nw@hydaspes.if.org> > wrote:
>
> > So, why exactly is a hard upgrade needed from 1.5 to 2.5?
>
> Because the pg_dump, pre-2.0 would include all the function definitions
I think the correct answere here is: because the internal
representation of GEOMETRY type changed. That's really the only reason
why one would *need* the "hard upgrade" procedure.
Dropping old functions should be handled just fine by "soft upgrade"
procedure. Filtering out all the function definition is ONLY needed
during an "hard upgrade" of a database in which PostGIS was enabled
via the enabler script (postgis.sql) rather than the CREATE EXTENSION
syntax.
Out of curiosity: since you're going to copy the data, why do you stop
at 2.5 rather than going straight to 3.x ?
--strk;
Libre GIS consultant/developer
https://strk.kbt.io/services.html
------------------------------
Message: 6
Date: Tue, 11 Jan 2022 03:53:10 +0100
From: Calle Hedberg <calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] PostGIS problem after updating from 3.1.4 to
3.2.0
Message-ID:
<CAPB4dVgTL9TawB_f+hkJm1DUKvMhaa-+bSyL7s+uta2r4m7KMQ@mail.gmail.com \
<mailto:CAPB4dVgTL9TawB_f%2BhkJm1DUKvMhaa-%2BbSyL7s%2Buta2r4m7KMQ@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
Hi,
I just updated postgresql 13 and pg 14 (running on the D-drive under
Windows 10 64 bits) from 3.14 to 3.2.0. (in order to install 3.2, I had to
first remove 3.1.4 - then I installed 3.2.0 using Stackbuilder run as
administrator).
When running "create extension postgis;" in pgAdmin, I get as expected a
message that extension postgis already exists. But it actually does not
exist/start up - if I run e.g. "select postgis_full_version();", it returns
ERROR: could not access file "$libdir/postgis-3": No such file or directory
CONTEXT: SQL statement "SELECT public.postgis_lib_version()" PL/pgSQL
function postgis_full_version() line 26 at SQL statement SQL state: 58P01
If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar
message:
ERROR: could not access file "$libdir/postgis-3": No such file or directory
CONTEXT: PL/pgSQL function _postgis_drop_function_if_needed(text,text) line
6 at FOR over SELECT rows SQL state: 58P01
I can force the issue by dropping the postgis extension and recreate it,
but then I have to use drop extension postgis cascade and that command will
wipe out the geometry fields in the database (dropping ext postgis on the
template postgres db work fine, but that db does not have any geometry
fields).
I have tried to re-start pg, reboot the machine, and googling the issue, to
no avail.
I can see that postgis 3.2.0 has been installed:
D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2
I see the error message states it cannot find postgis-3 - but there IS no
such file or directory, as you can see the directory is actually called
postgis-3.2 . But I don't know if that's a bug or what...
Any suggestions - or will I have to dump all my databases and then
re-install pg 13 and pg14 afresh?
Best regards
Calle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/cc9c1328/attachment-0001.html>
------------------------------
Message: 7
Date: Mon, 10 Jan 2022 22:20:48 -0500
From: "Regina Obe" <lr@pcorp.us <mailto:lr@pcorp.us> >
To: <calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >, "'PostGIS Users \
Discussion'"
<postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4
to 3.2.0
Message-ID: <000001d8069a$3b9af190$b2d0d4b0$@pcorp.us <http://pcorp.us> >
Content-Type: text/plain; charset="utf-8"
Hmm okay it looks like I forgot to take off the minor version in my release so all \
the libs
Have -3.2 on them instead of -3. I guess I didn?t notice cause I usually just \
install the new version over the old.
And then run
SELECT postgis_extensions_upgrade();
Though I?m still surprised it?s giving an error as I thought we fixed that issue a \
long time ago to handle a case where the lib file has been removed.
So that seems like a reemerging old bug.
That said , while I?m making a new package. Can you do the following:
First try if:
-- works without doing anything else
SELECT postgis_extensions_upgrade();
If the above still gives you an error, do the following
Reinstall PostGIS 3.1.4
Reinstall PostGIS 3.2.0
Then run
SELECT postgis_extensions_upgrade();
In each of your databases.
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
<mailto:postgis-users-bounces@lists.osgeo.org> ] On Behalf Of Calle \
Hedberg
Sent: Monday, January 10, 2022 9:53 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0
Hi,
I just updated postgresql 13 and pg 14 (running on the D-drive under Windows 10 64 \
bits) from 3.14 to 3.2.0. (in order to install 3.2, I had to first remove 3.1.4 - \
then I installed 3.2.0 using Stackbuilder run as administrator).
When running "create extension postgis;" in pgAdmin, I get as expected a message that \
extension postgis already exists. But it actually does not exist/start up - if I run \
e.g. "select postgis_full_version();", it returns
ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: \
SQL statement "SELECT public.postgis_lib_version()" PL/pgSQL function \
postgis_full_version() line 26 at SQL statement SQL state: 58P01
If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar message:
ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: \
PL/pgSQL function _postgis_drop_function_if_needed(text,text) line 6 at FOR over \
SELECT rows SQL state: 58P01
I can force the issue by dropping the postgis extension and recreate it, but then I \
have to use drop extension postgis cascade and that command will wipe out the \
geometry fields in the database (dropping ext postgis on the template postgres db \
work fine, but that db does not have any geometry fields).
I have tried to re-start pg, reboot the machine, and googling the issue, to no avail.
I can see that postgis 3.2.0 has been installed:
D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2
I see the error message states it cannot find postgis-3 - but there IS no such file \
or directory, as you can see the directory is actually called postgis-3.2 . But I \
don't know if that's a bug or what...
Any suggestions - or will I have to dump all my databases and then re-install pg 13 \
and pg14 afresh?
Best regards
Calle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/fee23c1a/attachment-0001.html>
------------------------------
Message: 8
Date: Mon, 10 Jan 2022 22:21:34 -0500
From: "Regina Obe" <lr@pcorp.us <mailto:lr@pcorp.us> >
To: <calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >, "'PostGIS Users \
Discussion'"
<postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4
to 3.2.0
Message-ID: <000501d8069a$56905760$03b10620$@pcorp.us <http://pcorp.us> >
Content-Type: text/plain; charset="utf-8"
Side note I?ve ticketed the issue here:
https://trac.osgeo.org/postgis/ticket/5045
and will update once I release a new package
From: Regina Obe [mailto:lr@pcorp.us <mailto:lr@pcorp.us> ]
Sent: Monday, January 10, 2022 10:21 PM
To: 'calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> ' \
<calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >; 'PostGIS Users \
Discussion' <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: RE: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0
Hmm okay it looks like I forgot to take off the minor version in my release so all \
the libs
Have -3.2 on them instead of -3. I guess I didn?t notice cause I usually just \
install the new version over the old.
And then run
SELECT postgis_extensions_upgrade();
Though I?m still surprised it?s giving an error as I thought we fixed that issue a \
long time ago to handle a case where the lib file has been removed.
So that seems like a reemerging old bug.
That said , while I?m making a new package. Can you do the following:
First try if:
-- works without doing anything else
SELECT postgis_extensions_upgrade();
If the above still gives you an error, do the following
Reinstall PostGIS 3.1.4
Reinstall PostGIS 3.2.0
Then run
SELECT postgis_extensions_upgrade();
In each of your databases.
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
<mailto:postgis-users-bounces@lists.osgeo.org> ] On Behalf Of Calle \
Hedberg
Sent: Monday, January 10, 2022 9:53 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> <mailto:postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> > >
Subject: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0
Hi,
I just updated postgresql 13 and pg 14 (running on the D-drive under Windows 10 64 \
bits) from 3.14 to 3.2.0. (in order to install 3.2, I had to first remove 3.1.4 - \
then I installed 3.2.0 using Stackbuilder run as administrator).
When running "create extension postgis;" in pgAdmin, I get as expected a message that \
extension postgis already exists. But it actually does not exist/start up - if I run \
e.g. "select postgis_full_version();", it returns
ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: \
SQL statement "SELECT public.postgis_lib_version()" PL/pgSQL function \
postgis_full_version() line 26 at SQL statement SQL state: 58P01
If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar message:
ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: \
PL/pgSQL function _postgis_drop_function_if_needed(text,text) line 6 at FOR over \
SELECT rows SQL state: 58P01
I can force the issue by dropping the postgis extension and recreate it, but then I \
have to use drop extension postgis cascade and that command will wipe out the \
geometry fields in the database (dropping ext postgis on the template postgres db \
work fine, but that db does not have any geometry fields).
I have tried to re-start pg, reboot the machine, and googling the issue, to no avail.
I can see that postgis 3.2.0 has been installed:
D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2
I see the error message states it cannot find postgis-3 - but there IS no such file \
or directory, as you can see the directory is actually called postgis-3.2 . But I \
don't know if that's a bug or what...
Any suggestions - or will I have to dump all my databases and then re-install pg 13 \
and pg14 afresh?
Best regards
Calle
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/65f13cd5/attachment-0001.html>
------------------------------
Message: 9
Date: Tue, 11 Jan 2022 06:42:48 +0100
From: Marcin Mionskowski <mionskowskimarcin@gmail.com \
<mailto:mionskowskimarcin@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] Postgis Raster determine exact hull
Message-ID:
<CAH_vRsHeXxxnak=n+nr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw@mail.gmail.com \
<mailto:n%2Bnr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw@mail.gmail.com> >
Content-Type: text/plain; charset="utf-8"
Hi,
Try to reclassify the raster first so that all "non NA" values are equal
(e.g. 1), then do ST_DumpAsPolygons.
Regards,
Marcin
pon., 10 sty 2022 o 21:27 Richard Huesken <richard.huesken@gmail.com \
<mailto:richard.huesken@gmail.com> > napisa?(a):
> hi,
>
> I'm using postgis 3.1 and I'm looking for the best way to obtain the exact
> hull of a raster (excluding the nodata points). The st_minconvexhull uses
> the MBR of the raster coverage, and is therefore quite fast. The result is
> however not as accurate as I require.
>
> I constructed some sql that uses st_pixelaspolygons and then does a
> st_union. However, My typical raster has 256x256 points, and with several
> 100s of rasters this is quite slow.
>
> Are there more clever (and faster!) ways to get the exact hull of a raster?
>
> Thanks in advance,
>
> Richard.
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/e288e782/attachment-0001.html>
------------------------------
Message: 10
Date: Tue, 11 Jan 2022 02:02:45 -0500
From: "Regina Obe" <lr@pcorp.us <mailto:lr@pcorp.us> >
To: "'PostGIS Users Discussion'" <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] Postgis Raster determine exact hull
Message-ID: <001701d806b9$3c78dd60$b56a9820$@pcorp.us <http://pcorp.us> >
Content-Type: text/plain; charset="utf-8"
You could also try using ST_Polygon
It will treat all non NA as the same resulting in a polygon or multipolygon.
https://postgis.net/docs/RT_ST_Polygon.html
From: postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
<mailto:postgis-users-bounces@lists.osgeo.org> ] On Behalf Of Marcin \
Mionskowski
Sent: Tuesday, January 11, 2022 12:43 AM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] Postgis Raster determine exact hull
Hi,
Try to reclassify the raster first so that all "non NA" values are equal (e.g. 1), \
then do ST_DumpAsPolygons.
Regards,
Marcin
pon., 10 sty 2022 o 21:27 Richard Huesken <richard.huesken@gmail.com \
<mailto:richard.huesken@gmail.com> <mailto:richard.huesken@gmail.com \
<mailto:richard.huesken@gmail.com> > > napisa?(a):
hi,
I'm using postgis 3.1 and I'm looking for the best way to obtain the exact hull of a \
raster (excluding the nodata points). The st_minconvexhull uses the MBR of the raster \
coverage, and is therefore quite fast. The result is however not as accurate as I \
require.
I constructed some sql that uses st_pixelaspolygons and then does a st_union. \
However, My typical raster has 256x256 points, and with several 100s of rasters this \
is quite slow.
Are there more clever (and faster!) ways to get the exact hull of a raster?
Thanks in advance,
Richard.
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> \
<mailto:postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> > \
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/1541b17f/attachment-0001.html>
------------------------------
Message: 11
Date: Tue, 11 Jan 2022 12:23:43 +0100
From: Calle Hedberg <calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >
To: Regina Obe <lr@pcorp.us <mailto:lr@pcorp.us> >
Cc: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4
to 3.2.0
Message-ID:
<CAPB4dVi=6izkAJCEV4vqRHD-fexX4mu7w_wV_Pp8X54=KANF-w@mail.gmail.com \
<mailto:KANF-w@mail.gmail.com> >
Content-Type: text/plain; charset="utf-8"
Dear Regina,
Just running SELECT postgis_extensions_upgrade(); did not work.
I reinstalled PostGIS 3.1.4, and verified that it was functional.
I then reinstalled 3.2.0 on top of that, this time successfully (not sure
why I had to remove 3.1.4 the first time, but...), and then ran
SELECT postgis_extensions_upgrade();
SELECT postgis_full_version();
on all databases, and that worked OK.
So I will do the same for PG14 and ditto on my other two systems. It's a
bit time consuming since I have 150-200 databases in total, so if you can
fix that bug so that there is no need to run the extension upgrade command
on every db it would be great.. I've got one pg10 and one pg12 installation
too to cater for some backward compatibility and to provide an upgrade path
for old databases, but I'm leaving those on 3.0 / 3.1
Thanks again for the rapid response and the clear instructions.
Best regards
Calle
On Tue, 11 Jan 2022 at 04:21, Regina Obe <lr@pcorp.us <mailto:lr@pcorp.us> > wrote:
> Side note I?ve ticketed the issue here:
>
>
>
> https://trac.osgeo.org/postgis/ticket/5045
>
>
>
> and will update once I release a new package
>
>
>
>
>
> *From:* Regina Obe [mailto:lr@pcorp.us <mailto:lr@pcorp.us> ]
> *Sent:* Monday, January 10, 2022 10:21 PM
> *To:* 'calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> ' \
> <calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com> >; 'PostGIS Users \
> Discussion' <postgis-users@lists.osgeo.org \
> <mailto:postgis-users@lists.osgeo.org> >
> *Subject:* RE: [postgis-users] PostGIS problem after updating from 3.1.4
> to 3.2.0
>
>
>
> Hmm okay it looks like I forgot to take off the minor version in my
> release so all the libs
>
>
>
> Have -3.2 on them instead of -3. I guess I didn?t notice cause I usually
> just install the new version over the old.
>
> And then run
>
>
>
> SELECT postgis_extensions_upgrade();
>
>
>
> Though I?m still surprised it?s giving an error as I thought we fixed that
> issue a long time ago to handle a case where the lib file has been removed.
>
> So that seems like a reemerging old bug.
>
>
>
> That said , while I?m making a new package. Can you do the following:
>
>
>
> First try if:
>
> -- works without doing anything else
>
> SELECT postgis_extensions_upgrade();
>
>
>
> If the above still gives you an error, do the following
>
>
>
> Reinstall PostGIS 3.1.4
>
> Reinstall PostGIS 3.2.0
>
> Then run
>
>
>
> SELECT postgis_extensions_upgrade();
>
>
>
> In each of your databases.
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
> <mailto:postgis-users-bounces@lists.osgeo.org> \
> <postgis-users-bounces@lists.osgeo.org \
> <mailto:postgis-users-bounces@lists.osgeo.org> >] *On Behalf Of \
> *Calle Hedberg
> *Sent:* Monday, January 10, 2022 9:53 PM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org \
> <mailto:postgis-users@lists.osgeo.org> >
> *Subject:* [postgis-users] PostGIS problem after updating from 3.1.4 to
> 3.2.0
>
>
>
> Hi,
>
>
>
> I just updated postgresql 13 and pg 14 (running on the D-drive under
> Windows 10 64 bits) from 3.14 to 3.2.0. (in order to install 3.2, I had to
> first remove 3.1.4 - then I installed 3.2.0 using Stackbuilder run as
> administrator).
>
>
>
> When running "create extension postgis;" in pgAdmin, I get as expected a
> message that extension postgis already exists. But it actually does not
> exist/start up - if I run e.g. "select postgis_full_version();", it returns
>
>
> ERROR: could not access file "$libdir/postgis-3": No such file or
> directory CONTEXT: SQL statement "SELECT public.postgis_lib_version()"
> PL/pgSQL function postgis_full_version() line 26 at SQL statement SQL
> state: 58P01
>
>
>
> If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar
> message:
>
> ERROR: could not access file "$libdir/postgis-3": No such file or
> directory CONTEXT: PL/pgSQL function
> _postgis_drop_function_if_needed(text,text) line 6 at FOR over SELECT rows
> SQL state: 58P01
>
>
>
> I can force the issue by dropping the postgis extension and recreate it,
> but then I have to use drop extension postgis cascade and that command will
> wipe out the geometry fields in the database (dropping ext postgis on the
> template postgres db work fine, but that db does not have any geometry
> fields).
>
>
>
> I have tried to re-start pg, reboot the machine, and googling the issue,
> to no avail.
>
>
>
> I can see that postgis 3.2.0 has been installed:
>
> D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2
>
>
>
> I see the error message states it cannot find postgis-3 - but there IS no
> such file or directory, as you can see the directory is actually called
> postgis-3.2 . But I don't know if that's a bug or what...
>
>
>
> Any suggestions - or will I have to dump all my databases and then
> re-install pg 13 and pg14 afresh?
>
>
>
> Best regards
>
> Calle
>
>
>
--
*Carl-Anders (Calle) Hedberg*
HISP
Researcher & Technical Specialist
Health Information Systems Programme ? South Africa
Cell: +47 41461011 (Norway)
Iridium SatPhone: +8816-315-19119 (usually OFF)
E-mail1: calle@hisp.org <mailto:calle@hisp.org>
E-mail2: calle.hedberg@gmail.com <mailto:calle.hedberg@gmail.com>
Skype: calle_hedberg
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/3222bf6b/attachment-0001.html>
------------------------------
Message: 12
Date: Tue, 11 Jan 2022 16:04:10 +0000
From: Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] How best to create and use associative
array type in Postgres?
Message-ID:
<CA+i5JwaUUeEL5vPj+-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w@mail.gmail.com \
<mailto:CA%2Bi5JwaUUeEL5vPj%2B-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
Hi, Regina,
This looks offering some clarity and simplicity.
I was told that hstore can also work as associative array. Does it offer
clarity and simplicity?
Regards,
Shao
On Sat, 8 Jan 2022 at 04:20, Regina Obe <lr@pcorp.us <mailto:lr@pcorp.us> > wrote:
> Oh forgot one more very useful operator, the subtraction operator.
> Removes a key/value from the list:
>
>
>
> SELECT '{"color": "blue", "height_m": 10}'::jsonb - 'color'
>
>
>
> Returns:
>
> {"height_m": 10}
>
>
>
>
>
> *From:* Regina Obe [mailto:lr@pcorp.us <mailto:lr@pcorp.us> ]
> *Sent:* Friday, January 7, 2022 11:18 PM
> *To:* 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org \
> <mailto:postgis-users@lists.osgeo.org> >
> *Subject:* RE: [postgis-users] How best to create and use associative
> array type in Postgres?
>
>
>
> David,
>
>
>
> Not sure what you are asking? There are many functions for jsonb and even
> more the newer your PostgreSQL is.
>
> Take a look at -
> https://www.postgresql.org/docs/current/functions-json.html
>
>
>
>
>
> You can pull arrays by index but not really key/values by index (because
> jsonb reorders keys/values for efficiency). So order shouldn?t matter in
> jsonb as the same level keys are unique.
>
> The trick of using the concatenation operator (||) to update keys values
> works, because the last entry for a key wins, and any key not in the list
> gets replaced by the last one. So I guess your popping idea
>
>
>
> Take for example:
>
>
>
> SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color":
> "red"}'::jsonb || '{"width_m": 5}';
>
>
>
> Returns:
>
> {"color": "red", "width_m": 5, "height_m": 10}
>
>
>
> Note how the entry width_m was added, but not the order you specified it,
> and that the color was changed from blue to red.
>
>
>
> Now if you wanted to get a set of all the key value pairs, you?d use
> jsonb_each_text (to get value as text) or jsonb_each to get the value as a
> jsonb.
>
>
>
> Here is an example:
>
> WITH a AS (SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color":
> "red"}'::jsonb || '{"width_m": 5}' AS data)
>
> SELECT kv.*
>
> FROM a, jsonb_each_text(a.data) AS kv;
>
>
>
> Returns:
>
> color red
>
> width_m 5
>
> height_m 10
>
>
>
> Now lets do this with PostGIS J
>
> WITH a AS (
>
> SELECT ST_AsGeoJSON(ST_MakeLine( ARRAY[ST_Point(1,2), ST_Point(3,4),
> ST_Point(-9,1)]))::jsonb AS data
>
> )
>
> SELECT kv.key, kv.value, kv.value->2->>0 AS last_x
>
> FROM a, jsonb_each(a.data) AS kv;
>
>
>
> key | value | last_x
>
> -------------+---------------------------+--------
>
> type | "LineString" |
>
> coordinates | [[1, 2], [3, 4], [-9, 1]] | -9
>
> (2 rows)
>
>
>
>
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
> <mailto:postgis-users-bounces@lists.osgeo.org> \
> <postgis-users-bounces@lists.osgeo.org \
> <mailto:postgis-users-bounces@lists.osgeo.org> >] *On Behalf Of \
> *Shaozhong SHI
> *Sent:* Friday, January 7, 2022 9:25 PM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org \
> <mailto:postgis-users@lists.osgeo.org> >
> *Subject:* Re: [postgis-users] How best to create and use associative
> array type in Postgres?
>
>
>
> Hi, Regina,
>
>
>
> That is interesting!
>
>
>
> How to add new entries to the dictionary. E.g., weight: 40?
>
>
>
> Can the dictionary to serve as a collection of paired key, value set, so
> that we can accumulate data to be processed?
>
>
>
> Then, we can deal with the first, then 2nd and so on in turn?
>
>
>
> Or, we can do things like pip and pop? Namely, when we have dealt with
> the first key, value pair, it will be out the dictionary, so that we can be
> sure that we are dealing with each key, value pair in turn?
>
>
>
> Alternatively, can we fetch each key, value pair by its index or position?
>
>
>
> Regards,
>
>
>
> David
>
>
>
> On Fri, 7 Jan 2022 at 21:19, Regina Obe <lr@pcorp.us <mailto:lr@pcorp.us> > wrote:
>
> Use JSONB datatype.
>
>
>
> CREATE TABLE test(id integer, data jsonb);
>
> TRUNCATE tABLE test;
>
> INSERT INTO test(id, data)
>
> VALUES (1, '{"color": "red", "height_m": 10}');
>
>
>
> -- PG14 or higher ? you can used subscript feature
>
> UPDATE test SET data['color'] = to_jsonb('blue'::text),
>
> data['height_m'] = to_jsonb(10), data['width_m'] = to_jsonb(2)
>
> WHERE id = 1;
>
>
>
> -- PG14 or lower
>
> UPDATE test SET data = jsonb_set(data, ARRAY['color'],
> to_jsonb('blue'::text), true)
>
> WHERE id = 1;
>
>
>
> -- PG14 or lower to set multiple
>
> UPDATE test SET data = data || '{"color": "blue", "height_m": 10}'::jsonb;
>
>
>
> -- To read (all versions)
>
> SELECT data->>'color' AS color, (data->>'height_m')::integer As height_m
>
> FROM test;
>
> *From:* postgis-users [mailto:postgis-users-bounces@lists.osgeo.org \
> <mailto:postgis-users-bounces@lists.osgeo.org> ] *On Behalf Of *Shaozhong SHI
> *Sent:* Wednesday, January 5, 2022 1:30 PM
> *To:* PostGIS Users Discussion <postgis-users@lists.osgeo.org \
> <mailto:postgis-users@lists.osgeo.org> >
> *Subject:* [postgis-users] How best to create and use associative array
> type in Postgres?
>
>
>
>
>
> In Oracle, one can create and use associative array. For instance,
>
> TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);
>
> NODES_WAITING FID_SET;
>
>
>
> How best to create and use associative array type in Postgres?
>
>
>
> Or, what is the best/most efficient equivalent in Postgres?
>
>
>
> Regards,
>
>
>
> David
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/299f405f/attachment-0001.html>
------------------------------
Message: 13
Date: Tue, 11 Jan 2022 16:38:58 +0000
From: Shaozhong SHI <shishaozhong@gmail.com <mailto:shishaozhong@gmail.com> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: [postgis-users] Using Spike finder in PostGIS?
Message-ID:
<CA+i5JwZVJUUcCgRwubeqmE4YOaAL3Q8+_QhHdCQjeeBOO6K_Nw@mail.gmail.com \
<mailto:CA%2Bi5JwZVJUUcCgRwubeqmE4YOaAL3Q8%2B_QhHdCQjeeBOO6K_Nw@mail.gmail.com> \
>
Content-Type: text/plain; charset="utf-8"
Have they ever solved this one?
polygon - Using Spike finder in PostGIS? - Geographic Information Systems
Stack Exchange
<https://gis.stackexchange.com/questions/101525/using-spike-finder-in-postgis>
Is there one that can offer clarity and simplicity?
Regards,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/ab05332d/attachment-0001.html>
------------------------------
Message: 14
Date: Tue, 11 Jan 2022 17:45:16 +0000
From: Nathan Wagner <nw@hydaspes.if.org <mailto:nw@hydaspes.if.org> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <Yd3CLIsGljRePZ2J@granicus.if.org \
<mailto:Yd3CLIsGljRePZ2J@granicus.if.org> >
Content-Type: text/plain; charset=us-ascii
On Tue, Jan 11, 2022 at 01:18:27AM +0100, Sandro Santilli wrote:
> On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote:
> > > On Jan 10, 2022, at 10:01 AM, Nathan Wagner <nw@hydaspes.if.org \
> > > <mailto:nw@hydaspes.if.org> > wrote:
> >
> > > So, why exactly is a hard upgrade needed from 1.5 to 2.5?
> >
> > Because the pg_dump, pre-2.0 would include all the function definitions
>
> I think the correct answere here is: because the internal
> representation of GEOMETRY type changed. That's really the only reason
> why one would *need* the "hard upgrade" procedure.
So, what I guess I'm a bit confused about is what I get out of a select
or copy? What is the difference between the "internal representation"
and what I get from a raw select or copy?
Suppose, for example, I have a table with a geometry column "geom". If
I do a "select geom from table", I get what looks like a hex
representation of a binary value. Is that a hex encoded internal
representation, or some external representation that did not change
between 1.5 and 2.5? Will this value then be converted to the correct
internal representation on the 2.5 side?
Another way to put this is will the following work?
psql -c '\copy (select geom from table) to stdout' -d postgis15 |
psql -c '\copy table (geom) from stdin' -d postgis25
The exact syntax is probably different as that is from memory, but I
trust that the essence of what I'm trying to do is clear.
> Dropping old functions should be handled just fine by "soft upgrade"
> procedure. Filtering out all the function definition is ONLY needed
> during an "hard upgrade" of a database in which PostGIS was enabled
> via the enabler script (postgis.sql) rather than the CREATE EXTENSION
> syntax.
Could this have been done via 'create extension postgis from unpackaged'?
I think that doesn't work for an in-place upgrade because it can't
handle converting the internal representation.
> Out of curiosity: since you're going to copy the data, why do you stop
> at 2.5 rather than going straight to 3.x ?
Client reluctance mostly. The upgrade was also planned before v3 was
out. If it were my DB I'd go to 3.x on pg 14.
--
nw
------------------------------
Message: 15
Date: Tue, 11 Jan 2022 09:57:40 -0800
From: Paul Ramsey <pramsey@cleverelephant.ca <mailto:pramsey@cleverelephant.ca> >
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org \
<mailto:postgis-users@lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <A05C18A9-0191-4360-8F1A-7B5FC0F61B7B@cleverelephant.ca \
<mailto:A05C18A9-0191-4360-8F1A-7B5FC0F61B7B@cleverelephant.ca> >
Content-Type: text/plain; charset=us-ascii
> On Jan 11, 2022, at 9:45 AM, Nathan Wagner <nw@hydaspes.if.org \
> <mailto:nw@hydaspes.if.org> > wrote:
> On Tue, Jan 11, 2022 at 01:18:27AM +0100, Sandro Santilli wrote:
> > On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote:
> > > > On Jan 10, 2022, at 10:01 AM, Nathan Wagner <nw@hydaspes.if.org \
> > > > <mailto:nw@hydaspes.if.org> > wrote:
> > >
> > > > So, why exactly is a hard upgrade needed from 1.5 to 2.5?
> > >
> > > Because the pg_dump, pre-2.0 would include all the function definitions
> >
> > I think the correct answere here is: because the internal
> > representation of GEOMETRY type changed. That's really the only reason
> > why one would *need* the "hard upgrade" procedure.
>
> So, what I guess I'm a bit confused about is what I get out of a select
> or copy? What is the difference between the "internal representation"
> and what I get from a raw select or copy?
>
> Suppose, for example, I have a table with a geometry column "geom". If
> I do a "select geom from table", I get what looks like a hex
> representation of a binary value. Is that a hex encoded internal
> representation, or some external representation that did not change
> between 1.5 and 2.5? Will this value then be converted to the correct
> internal representation on the 2.5 side?
The internal representation is what is written on the disk.
The "canonical form" is what you get when you run "select geom from mytable", or just \
pg_dump the table. The "canonical form" is unchanged from version 1.0 upwards. So you \
can dump a PostGIS 1.0 table and load it into PostGIS 3.2, because the form in the \
dump is understood (in fact you can load a table from PostGIS 0.5, since PostGIS 3.2 \
still accepts the old form on input). The reason you need to "hard upgrade" between \
PostGIS 2 and 3, as Sandro noted, is that the on-disk format changed, so you cannot \
just replace the functions and leave the data in place (which is what the soft \
upgrade process does) you need to actually read it off disk, convert it into the \
canonical format (which is what pg_dump does) then send that data back into the new \
version of PostGIS to be written to disk in the new format. As and end user, you \
never see the on-disk format. You're always getting some transformation of it, \
whether it's WKT, GeoJSON, WKB, or the HEXEWKB that comes out in the dump file or the \
raw "select geom from mytable" output.
> Another way to put this is will the following work?
>
> psql -c '\copy (select geom from table) to stdout' -d postgis15 |
> psql -c '\copy table (geom) from stdin' -d postgis25
Yes, that will work. You're reading out the canonical form and writing it over to the \
new database which will happilty put it back on disk in the new on-disk format.
P.
>
> The exact syntax is probably different as that is from memory, but I
> trust that the essence of what I'm trying to do is clear.
>
> > Dropping old functions should be handled just fine by "soft upgrade"
> > procedure. Filtering out all the function definition is ONLY needed
> > during an "hard upgrade" of a database in which PostGIS was enabled
> > via the enabler script (postgis.sql) rather than the CREATE EXTENSION
> > syntax.
>
> Could this have been done via 'create extension postgis from unpackaged'?
> I think that doesn't work for an in-place upgrade because it can't
> handle converting the internal representation.
>
> > Out of curiosity: since you're going to copy the data, why do you stop
> > at 2.5 rather than going straight to 3.x ?
>
> Client reluctance mostly. The upgrade was also planned before v3 was
> out. If it were my DB I'd go to 3.x on pg 14.
>
> --
> nw
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
Subject: Digest Footer
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
End of postgis-users Digest, Vol 239, Issue 7
*********************************************
[Attachment #5 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type \
content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 \
(filtered medium)"><style><!-- /* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div \
class=WordSection1><p class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Curious what \
was the performance for ST_Polygon with ST_reclass?<o:p></o:p></span></p><p \
class=MsoNormal><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div \
style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div \
style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p \
class=MsoNormal><b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span \
style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users \
[mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Richard \
Huesken<br><b>Sent:</b> Wednesday, January 12, 2022 4:05 PM<br><b>To:</b> \
postgis-users@lists.osgeo.org<br><b>Subject:</b> Re: [postgis-users] postgis-users \
Digest, Vol 239, Issue 7 , Postgis Raster determine exact \
hull<o:p></o:p></span></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><div><p class=MsoNormal>Thanks Marcin \
and Regina.<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I tried and \
combined both options. For a particular raster, the st_reclass is much faster (33 \
milliseconds) compared to just using the st_polygon (4.5 seconds) . I included these \
examples for other users (the st_reclass syntax is a bit harder to understand) \
.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>-- FAST<o:p></o:p></p></div><div><p class=MsoNormal>select \
(st_dumpaspolygons(st_reclass(r.raster_data, 1, '(-10000 - 9999):1', '4BUI', 0) \
)).*<br>from spc_tile_rasters r<br>where <a href="http://r.id/" \
target="_blank">r.id</a> = 20818<br><br>select \
st_polygon(st_reclass(r.raster_data, 1, '(-10000 - 9999):1', '4BUI', 0) )<br>from \
spc_tile_rasters r<br>where <a href="http://r.id/" \
target="_blank">r.id</a> = 20818<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>-- SLOW<br>select \
st_polygon(r.raster_data)<br>from spc_tile_rasters r<br>where <a \
href="http://r.id/" target="_blank">r.id</a> = 20818<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Kind \
regards,<o:p></o:p></p></div><div><p \
class=MsoNormal><o:p> </o:p></p></div><div><p \
class=MsoNormal>Richard.<o:p></o:p></p></div></div><p \
class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Op di 11 jan. 2022 \
om 21:00 schreef <<a \
href="mailto:postgis-users-request@lists.osgeo.org">postgis-users-request@lists.osgeo.org</a>>:<o:p></o:p></p></div><blockquote \
style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in \
6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>Send postgis-users \
mailing list submissions to<br> <a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br><br>To subscribe or unsubscribe \
via the World Wide Web, visit<br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>or, via \
email, send a message with subject or body 'help' to<br> \
<a href="mailto:postgis-users-request@lists.osgeo.org" \
target="_blank">postgis-users-request@lists.osgeo.org</a><br><br>You can reach the \
person managing the list at<br> <a \
href="mailto:postgis-users-owner@lists.osgeo.org" \
target="_blank">postgis-users-owner@lists.osgeo.org</a><br><br>When replying, please \
edit your Subject line so it is more specific<br>than "Re: Contents of \
postgis-users digest..."<br><br><br>Today's Topics:<br><br> 1. \
Postgis Raster determine exact hull (Richard Huesken)<br> 2. Line segment \
and its variation over space (Shaozhong SHI)<br> 3. Computing overall \
trend presented by a 3D line (Shaozhong SHI)<br> 4. Any function to \
compute line trend and identify segment<br> running in flat areas \
(Shaozhong SHI)<br> 5. Re: hard upgrade from 1.5 (Sandro \
Santilli)<br> 6. PostGIS problem after updating from 3.1.4 to 3.2.0 \
(Calle Hedberg)<br> 7. Re: PostGIS problem after updating from 3.1.4 to \
3.2.0<br> (Regina Obe)<br> 8. Re: PostGIS problem \
after updating from 3.1.4 to 3.2.0<br> (Regina Obe)<br> \
9. Re: Postgis Raster determine exact hull (Marcin Mionskowski)<br> 10. \
Re: Postgis Raster determine exact hull (Regina Obe)<br> 11. Re: PostGIS \
problem after updating from 3.1.4 to 3.2.0<br> (Calle \
Hedberg)<br> 12. Re: How best to create and use associative array type \
in<br> Postgres? (Shaozhong SHI)<br> 13. Using Spike finder \
in PostGIS? (Shaozhong SHI)<br> 14. Re: hard upgrade from 1.5 (Nathan \
Wagner)<br> 15. Re: hard upgrade from 1.5 (Paul \
Ramsey)<br><br><br>----------------------------------------------------------------------<br><br>Message: \
1<br>Date: Mon, 10 Jan 2022 21:26:40 +0100<br>From: Richard Huesken <<a \
href="mailto:richard.huesken@gmail.com" \
target="_blank">richard.huesken@gmail.com</a>><br>To: <a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br>Subject: [postgis-users] Postgis \
Raster determine exact hull<br>Message-ID:<br> <<a \
href="mailto:CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q@mail.gmail.com" \
target="_blank">CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="utf-8"<br><br>hi,<br><br>I'm using postgis 3.1 and I'm \
looking for the best way to obtain the exact<br>hull of a raster (excluding the \
nodata points). The st_minconvexhull uses<br>the MBR of the raster coverage, and is \
therefore quite fast. The result is<br>however not as accurate as I require.<br><br>I \
constructed some sql that uses st_pixelaspolygons and then does a<br>st_union. \
However, My typical raster has 256x256 points, and with several<br>100s of rasters \
this is quite slow.<br><br>Are there more clever (and faster!) ways to get the exact \
hull of a raster?<br><br>Thanks in advance,<br><br>Richard.<br>-------------- next \
part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a \
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/ada538a6/attachment-0001.html" \
target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/ad \
a538a6/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: \
2<br>Date: Mon, 10 Jan 2022 23:38:46 +0000<br>From: Shaozhong SHI <<a \
href="mailto:shishaozhong@gmail.com" \
target="_blank">shishaozhong@gmail.com</a>><br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: [postgis-users] \
Line segment and its variation over space<br>Message-ID:<br> \
<<a href="mailto:CA%2Bi5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ@mail.gmail.com" \
target="_blank">CA+i5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="utf-8"<br><br>3D Line segments can be used for \
delineating riverine systems. In nature,<br>some rivers run in steep gradients \
and others in flat areas.<br><br>In geocomputation, rules are needed in order to \
compute lines running in<br>steep gradients and lines in flat areas.<br><br>Surely, \
there are ways to make computed decision on which lines running in<br>flat \
areas.<br><br>How to devise and implement such rules is of interest.<br><br>Any \
enlightening recommendations and \
suggestions?<br><br>Regards,<br><br>David<br>-------------- next part \
--------------<br>An HTML attachment was scrubbed...<br>URL: <<a \
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/7d8b3022/attachment-0001.html" \
target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/7d \
8b3022/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: \
3<br>Date: Mon, 10 Jan 2022 23:56:48 +0000<br>From: Shaozhong SHI <<a \
href="mailto:shishaozhong@gmail.com" \
target="_blank">shishaozhong@gmail.com</a>><br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: [postgis-users] \
Computing overall trend presented by a 3D<br> \
line<br>Message-ID:<br> <<a \
href="mailto:CA%2Bi5JwZ2v6wN2Yj-d7EHkZhqQb6%2BttgYEDQpDtyjCAmWb1cFxw@mail.gmail.com" \
target="_blank">CA+i5JwZ2v6wN2Yj-d7EHkZhqQb6+ttgYEDQpDtyjCAmWb1cFxw@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="utf-8"<br><br>3D lines can be used to delineated \
natural phenomena. There are various<br>ways to compute the overall trend of a \
3D line to determine whether the<br>line is running downward or upwards.<br><br>What \
are the best ways to compute this in \
PostGIS?<br><br>Regards,<br><br>David<br>-------------- next part \
--------------<br>An HTML attachment was scrubbed...<br>URL: <<a \
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/39b61815/attachment-0001.html" \
target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/39 \
b61815/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: \
4<br>Date: Tue, 11 Jan 2022 00:07:08 +0000<br>From: Shaozhong SHI <<a \
href="mailto:shishaozhong@gmail.com" \
target="_blank">shishaozhong@gmail.com</a>><br>To: PostGIS Users Discussion <<a \
href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: [postgis-users] Any \
function to compute line trend and<br> identify segment \
running in flat areas<br>Message-ID:<br> <<a \
href="mailto:CA%2Bi5JwZbGH%2BU35D8wT4OYvBJ0WHBn4PDAgVES_pwqfBYzpnsvQ@mail.gmail.com" \
target="_blank">CA+i5JwZbGH+U35D8wT4OYvBJ0WHBn4PDAgVES_pwqfBYzpnsvQ@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="utf-8"<br><br>A line may run steeply downslope and \
then over flat areas.<br><br>Any generic function to determine \
so?<br><br>Input: geometry and relative overall gradient<br><br>Output: the \
segment running steeply, the segment running in flat area<br><br>Any recommendations \
and suggestions?<br><br>Regards,<br><br>David<br>-------------- next part \
--------------<br>An HTML attachment was scrubbed...<br>URL: <<a \
href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/6851a119/attachment-0001.html" \
target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/68 \
51a119/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: \
5<br>Date: Tue, 11 Jan 2022 01:18:27 +0100<br>From: Sandro Santilli <<a \
href="mailto:strk@kbt.io" target="_blank">strk@kbt.io</a>><br>To: PostGIS Users \
Discussion <<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: Re: [postgis-users] \
hard upgrade from 1.5<br>Message-ID: <YdzM00goGhQyBFpM@c19><br>Content-Type: \
text/plain; charset=us-ascii<br><br>On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul \
Ramsey wrote:<br>> > On Jan 10, 2022, at 10:01 AM, Nathan Wagner <<a \
href="mailto:nw@hydaspes.if.org" target="_blank">nw@hydaspes.if.org</a>> \
wrote:<br>><br>> > So, why exactly is a hard upgrade needed from 1.5 to \
2.5?<br>> <br>> Because the pg_dump, pre-2.0 would include all the function \
definitions<br><br>I think the correct answere here is: because the \
internal<br>representation of GEOMETRY type changed. That's really the only \
reason<br>why one would *need* the "hard upgrade" \
procedure.<br><br>Dropping old functions should be handled just fine by "soft \
upgrade"<br>procedure. Filtering out all the function definition is ONLY \
needed<br>during an "hard upgrade" of a database in which PostGIS was \
enabled<br>via the enabler script (postgis.sql) rather than the CREATE \
EXTENSION<br>syntax.<br><br>Out of curiosity: since you're going to copy the data, \
why do you stop<br>at 2.5 rather than going straight to 3.x \
?<br><br>--strk;<br><br> Libre GIS consultant/developer<br> <a \
href="https://strk.kbt.io/services.html" \
target="_blank">https://strk.kbt.io/services.html</a><br><br><br>------------------------------<br><br>Message: \
6<br>Date: Tue, 11 Jan 2022 03:53:10 +0100<br>From: Calle Hedberg <<a \
href="mailto:calle.hedberg@gmail.com" \
target="_blank">calle.hedberg@gmail.com</a>><br>To: PostGIS Users Discussion \
<<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: [postgis-users] \
PostGIS problem after updating from 3.1.4 to<br> \
3.2.0<br>Message-ID:<br> <<a \
href="mailto:CAPB4dVgTL9TawB_f%2BhkJm1DUKvMhaa-%2BbSyL7s%2Buta2r4m7KMQ@mail.gmail.com" \
target="_blank">CAPB4dVgTL9TawB_f+hkJm1DUKvMhaa-+bSyL7s+uta2r4m7KMQ@mail.gmail.com</a>><br>Content-Type: \
text/plain; charset="utf-8"<br><br>Hi,<br><br>I just updated postgresql 13 \
and pg 14 (running on the D-drive under<br>Windows 10 64 bits) from 3.14 to 3.2.0. \
(in order to install 3.2, I had to<br>first remove 3.1.4 - then I installed 3.2.0 \
using Stackbuilder run as<br>administrator).<br><br>When running "create \
extension postgis;" in pgAdmin, I get as expected a<br>message that extension \
postgis already exists. But it actually does not<br>exist/start up - if I run e.g. \
"select postgis_full_version();", it returns<br><br>ERROR: could not access \
file "$libdir/postgis-3": No such file or directory<br>CONTEXT: SQL \
statement "SELECT public.postgis_lib_version()" PL/pgSQL<br>function \
postgis_full_version() line 26 at SQL statement SQL state: 58P01<br><br>If I run \
"ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a \
similar<br>message:<br>ERROR: could not access file "$libdir/postgis-3": No \
such file or directory<br>CONTEXT: PL/pgSQL function \
_postgis_drop_function_if_needed(text,text) line<br>6 at FOR over SELECT rows SQL \
state: 58P01<br><br>I can force the issue by dropping the postgis extension and \
recreate it,<br>but then I have to use drop extension postgis cascade and that \
command will<br>wipe out the geometry fields in the database (dropping ext postgis on \
the<br>template postgres db work fine, but that db does not have any \
geometry<br>fields).<br><br>I have tried to re-start pg, reboot the machine, and \
googling the issue, to<br>no avail.<br><br>I can see that postgis 3.2.0 has been \
installed:<br>D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2<br><br>I see \
the error message states it cannot find postgis-3 - but there IS no<br>such \
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic