[postgis-users] postgis-users Digest, Vol 239, Issue 7 , Postgis Raster determine exact hull
Richard Huesken
richard.huesken at gmail.com
Wed Jan 12 13:05:24 PST 2022
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 = 20818
select st_polygon(st_reclass(r.raster_data, 1, '(-10000 - 9999):1', '4BUI',
0) )
from spc_tile_rasters r
where r.id = 20818
-- SLOW
select st_polygon(r.raster_data)
from spc_tile_rasters r
where r.id = 20818
Kind regards,
Richard.
Op di 11 jan. 2022 om 21:00 schreef <postgis-users-request at lists.osgeo.org>:
> Send postgis-users mailing list submissions to
> 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
>
> You can reach the person managing the list at
> 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>
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Postgis Raster determine exact hull
> Message-ID:
> <
> 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>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Line segment and its variation over space
> Message-ID:
> <
> CA+i5JwbTn8zE9krExDFNspKVjDa1df8tDdMZ254qRnpZOuyySQ 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>
> To: PostGIS Users Discussion <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>
> 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>
> To: PostGIS Users Discussion <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>
> 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>
> To: PostGIS Users Discussion <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>
> 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>
> To: PostGIS Users Discussion <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>
> 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>
> To: <calle.hedberg at gmail.com>, "'PostGIS Users Discussion'"
> <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>
> 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] On
> Behalf Of Calle Hedberg
> Sent: Monday, January 10, 2022 9:53 PM
> To: PostGIS Users Discussion <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>
> To: <calle.hedberg at gmail.com>, "'PostGIS Users Discussion'"
> <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>
> 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]
> Sent: Monday, January 10, 2022 10:21 PM
> To: 'calle.hedberg at gmail.com' <calle.hedberg at gmail.com>; 'PostGIS Users
> Discussion' <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] 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/65f13cd5/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 9
> Date: Tue, 11 Jan 2022 06:42:48 +0100
> From: Marcin Mionskowski <mionskowskimarcin at gmail.com>
> To: PostGIS Users Discussion <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>
> 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>
> 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
> > 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>
> To: "'PostGIS Users Discussion'" <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] Postgis Raster determine exact hull
> Message-ID: <001701d806b9$3c78dd60$b56a9820$@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] On
> Behalf Of Marcin Mionskowski
> Sent: Tuesday, January 11, 2022 12:43 AM
> To: PostGIS Users Discussion <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> > 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/1541b17f/attachment-0001.html
> >
>
> ------------------------------
>
> Message: 11
> Date: Tue, 11 Jan 2022 12:23:43 +0100
> From: Calle Hedberg <calle.hedberg at gmail.com>
> To: Regina Obe <lr at pcorp.us>
> Cc: PostGIS Users Discussion <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>
> 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> 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]
> > *Sent:* Monday, January 10, 2022 10:21 PM
> > *To:* 'calle.hedberg at gmail.com' <calle.hedberg at gmail.com>; 'PostGIS
> Users
> > Discussion' <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
> > <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>
> > *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
>
> E-mail2: 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>
> To: PostGIS Users Discussion <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>
> 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> 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]
> > *Sent:* Friday, January 7, 2022 11:18 PM
> > *To:* 'PostGIS Users Discussion' <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
> > <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>
> > *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> 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] *On
> > Behalf Of *Shaozhong SHI
> > *Sent:* Wednesday, January 5, 2022 1:30 PM
> > *To:* PostGIS Users Discussion <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
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > 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>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: [postgis-users] Using Spike finder in PostGIS?
> Message-ID:
> <
> CA+i5JwZVJUUcCgRwubeqmE4YOaAL3Q8+_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>
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] hard upgrade from 1.5
> Message-ID: <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>
> 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>
> To: PostGIS Users Discussion <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>
> Content-Type: text/plain; charset=us-ascii
>
>
>
> > On Jan 11, 2022, at 9:45 AM, Nathan Wagner <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>
> 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
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> postgis-users mailing list
> 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/e6d02ef2/attachment.html>
More information about the postgis-users
mailing list