[postgis-users] postgis-users Digest, Vol 239, Issue 7 , Postgis Raster determine exact hull
Regina Obe
lr at pcorp.us
Wed Jan 12 16:13:22 PST 2022
Curious what was the performance for ST_Polygon with ST_reclass?
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Richard Huesken
Sent: Wednesday, January 12, 2022 4:05 PM
To: postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-request at lists.osgeo.org> >:
Send postgis-users mailing list submissions to
postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-request at lists.osgeo.org>
You can reach the person managing the list at
postgis-users-owner at lists.osgeo.org <mailto:postgis-users-owner at 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 at gmail.com <mailto:richard.huesken at gmail.com> >
To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
Subject: [postgis-users] Postgis Raster determine exact hull
Message-ID:
<CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q at mail.gmail.com <mailto:CAMjmB9rZThGB-p7Z7dKCbEqncnb7h8PpFz_JoGoj4FobqmSA-Q at 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 at gmail.com <mailto:shishaozhong at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] Line segment and its variation over space
Message-ID:
<CA+i5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ at mail.gmail.com <mailto:CA%2Bi5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ at 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 at gmail.com <mailto:shishaozhong at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] Computing overall trend presented by a 3D
line
Message-ID:
<CA+i5JwZ2v6wN2Yj-d7EHkZhqQb6+ttgYEDQpDtyjCAmWb1cFxw at mail.gmail.com <mailto:CA%2Bi5JwZ2v6wN2Yj-d7EHkZhqQb6%2BttgYEDQpDtyjCAmWb1cFxw at 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 at gmail.com <mailto:shishaozhong at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at mail.gmail.com <mailto:CA%2Bi5JwZbGH%2BU35D8wT4OYvBJ0WHBn4PDAgVES_pwqfBYzpnsvQ at 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 at kbt.io <mailto:strk at kbt.io> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <YdzM00goGhQyBFpM at 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 at hydaspes.if.org <mailto:nw at 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 at gmail.com <mailto:calle.hedberg at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at mail.gmail.com <mailto:CAPB4dVgTL9TawB_f%2BhkJm1DUKvMhaa-%2BbSyL7s%2Buta2r4m7KMQ at 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 at pcorp.us <mailto:lr at pcorp.us> >
To: <calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> >, "'PostGIS Users Discussion'"
<postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] On Behalf Of Calle Hedberg
Sent: Monday, January 10, 2022 9:53 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at pcorp.us <mailto:lr at pcorp.us> >
To: <calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> >, "'PostGIS Users Discussion'"
<postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at pcorp.us <mailto:lr at pcorp.us> ]
Sent: Monday, January 10, 2022 10:21 PM
To: 'calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> ' <calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> >; 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] On Behalf Of Calle Hedberg
Sent: Monday, January 10, 2022 9:53 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> <mailto:postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at gmail.com <mailto:mionskowskimarcin at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: Re: [postgis-users] Postgis Raster determine exact hull
Message-ID:
<CAH_vRsHeXxxnak=n+nr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw at mail.gmail.com <mailto:n%2Bnr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw at 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 at gmail.com <mailto:richard.huesken at 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 at lists.osgeo.org <mailto:postgis-users at 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 at pcorp.us <mailto:lr at pcorp.us> >
To: "'PostGIS Users Discussion'" <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] On Behalf Of Marcin Mionskowski
Sent: Tuesday, January 11, 2022 12:43 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at gmail.com <mailto:richard.huesken at gmail.com> <mailto:richard.huesken at gmail.com <mailto:richard.huesken at 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 at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> <mailto:postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at gmail.com <mailto:calle.hedberg at gmail.com> >
To: Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> >
Cc: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at mail.gmail.com <mailto:KANF-w at 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 at pcorp.us <mailto:lr at 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 at pcorp.us <mailto:lr at pcorp.us> ]
> *Sent:* Monday, January 10, 2022 10:21 PM
> *To:* 'calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> ' <calle.hedberg at gmail.com <mailto:calle.hedberg at gmail.com> >; 'PostGIS Users
> Discussion' <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>
> <postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> >] *On Behalf Of *Calle Hedberg
> *Sent:* Monday, January 10, 2022 9:53 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at hisp.org <mailto:calle at hisp.org>
E-mail2: calle.hedberg at gmail.com <mailto:calle.hedberg at 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 at gmail.com <mailto:shishaozhong at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at mail.gmail.com <mailto:CA%2Bi5JwaUUeEL5vPj%2B-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w at 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 at pcorp.us <mailto:lr at 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 at pcorp.us <mailto:lr at pcorp.us> ]
> *Sent:* Friday, January 7, 2022 11:18 PM
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>
> <postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> >] *On Behalf Of *Shaozhong SHI
> *Sent:* Friday, January 7, 2022 9:25 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at pcorp.us <mailto:lr at 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 at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> ] *On
> Behalf Of *Shaozhong SHI
> *Sent:* Wednesday, January 5, 2022 1:30 PM
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at 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 at gmail.com <mailto:shishaozhong at gmail.com> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] Using Spike finder in PostGIS?
Message-ID:
<CA+i5JwZVJUUcCgRwubeqmE4YOaAL3Q8+_QhHdCQjeeBOO6K_Nw at mail.gmail.com <mailto:CA%2Bi5JwZVJUUcCgRwubeqmE4YOaAL3Q8%2B_QhHdCQjeeBOO6K_Nw at 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 at hydaspes.if.org <mailto:nw at hydaspes.if.org> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <Yd3CLIsGljRePZ2J at granicus.if.org <mailto:Yd3CLIsGljRePZ2J at 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 at hydaspes.if.org <mailto:nw at 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 at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> >
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Subject: Re: [postgis-users] hard upgrade from 1.5
Message-ID: <A05C18A9-0191-4360-8F1A-7B5FC0F61B7B at cleverelephant.ca <mailto:A05C18A9-0191-4360-8F1A-7B5FC0F61B7B at cleverelephant.ca> >
Content-Type: text/plain; charset=us-ascii
> On Jan 11, 2022, at 9:45 AM, Nathan Wagner <nw at hydaspes.if.org <mailto:nw at 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 at hydaspes.if.org <mailto:nw at 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 at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
Subject: Digest Footer
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
End of postgis-users Digest, Vol 239, Issue 7
*********************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20220112/542453cb/attachment.html>
More information about the postgis-users
mailing list