<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/ada538a6/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/7d8b3022/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/39b61815/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/6851a119/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 file or directory, as you can see the directory is actually called<br>postgis-3.2 . But I don't know if that's a bug or what...<br><br>Any suggestions - or will I have to dump all my databases and then<br>re-install pg 13 and pg14 afresh?<br><br>Best regards<br>Calle<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/cc9c1328/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/cc9c1328/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 7<br>Date: Mon, 10 Jan 2022 22:20:48 -0500<br>From: "Regina Obe" <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br>To: <<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>>, "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4<br> to 3.2.0<br>Message-ID: <000001d8069a$3b9af190$b2d0d4b0$@<a href="http://pcorp.us" target="_blank">pcorp.us</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Hmm okay it looks like I forgot to take off the minor version in my release so all the libs<br><br><br><br>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.<br><br>And then run <br><br><br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>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.<br><br>So that seems like a reemerging old bug.<br><br><br><br>That said , while I?m making a new package. Can you do the following:<br><br><br><br>First try if:<br><br>-- works without doing anything else<br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>If the above still gives you an error, do the following <br><br><br><br>Reinstall PostGIS 3.1.4<br><br>Reinstall PostGIS 3.2.0<br><br>Then run<br><br><br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>In each of your databases.<br><br><br><br><br><br><br><br>From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of Calle Hedberg<br>Sent: Monday, January 10, 2022 9:53 PM<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 3.2.0<br><br><br><br>Hi,<br><br><br><br>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).<br><br><br><br>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<br><br><br><br><br>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<br><br><br><br>If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar message:<br><br>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<br><br><br><br>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).<br><br><br><br>I have tried to re-start pg, reboot the machine, and googling the issue, to no avail.<br><br><br><br>I can see that postgis 3.2.0 has been installed:<br><br>D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2<br><br><br><br>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...<br><br><br><br>Any suggestions - or will I have to dump all my databases and then re-install pg 13 and pg14 afresh?<br><br><br><br>Best regards<br><br>Calle<br><br><br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/fee23c1a/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/fee23c1a/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 8<br>Date: Mon, 10 Jan 2022 22:21:34 -0500<br>From: "Regina Obe" <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br>To: <<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>>, "'PostGIS Users Discussion'"<br> <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4<br> to 3.2.0<br>Message-ID: <000501d8069a$56905760$03b10620$@<a href="http://pcorp.us" target="_blank">pcorp.us</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Side note I?ve ticketed the issue here:<br><br><br><br><a href="https://trac.osgeo.org/postgis/ticket/5045" target="_blank">https://trac.osgeo.org/postgis/ticket/5045</a><br><br><br><br>and will update once I release a new package<br><br><br><br><br><br>From: Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>] <br>Sent: Monday, January 10, 2022 10:21 PM<br>To: '<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>' <<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>>; 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: RE: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0<br><br><br><br>Hmm okay it looks like I forgot to take off the minor version in my release so all the libs<br><br><br><br>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.<br><br>And then run <br><br><br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>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.<br><br>So that seems like a reemerging old bug.<br><br><br><br>That said , while I?m making a new package. Can you do the following:<br><br><br><br>First try if:<br><br>-- works without doing anything else<br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>If the above still gives you an error, do the following <br><br><br><br>Reinstall PostGIS 3.1.4<br><br>Reinstall PostGIS 3.2.0<br><br>Then run<br><br><br><br>SELECT postgis_extensions_upgrade();<br><br><br><br>In each of your databases.<br><br><br><br><br><br><br><br>From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of Calle Hedberg<br>Sent: Monday, January 10, 2022 9:53 PM<br>To: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<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 3.2.0<br><br><br><br>Hi,<br><br><br><br>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).<br><br><br><br>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<br><br><br><br><br>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<br><br><br><br>If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar message:<br><br>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<br><br><br><br>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).<br><br><br><br>I have tried to re-start pg, reboot the machine, and googling the issue, to no avail.<br><br><br><br>I can see that postgis 3.2.0 has been installed:<br><br>D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2<br><br><br><br>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...<br><br><br><br>Any suggestions - or will I have to dump all my databases and then re-install pg 13 and pg14 afresh?<br><br><br><br>Best regards<br><br>Calle<br><br><br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/65f13cd5/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220110/65f13cd5/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 9<br>Date: Tue, 11 Jan 2022 06:42:48 +0100<br>From: Marcin Mionskowski <<a href="mailto:mionskowskimarcin@gmail.com" target="_blank">mionskowskimarcin@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: Re: [postgis-users] Postgis Raster determine exact hull<br>Message-ID:<br> <CAH_vRsHeXxxnak=<a href="mailto:n%2Bnr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw@mail.gmail.com" target="_blank">n+nr2NcMiJakAxvK5YnD9iY9UMVxZE2Yeqw@mail.gmail.com</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Hi,<br>Try to reclassify the raster first so that all "non NA" values are equal<br>(e.g. 1), then do ST_DumpAsPolygons.<br>Regards,<br>Marcin<br><br>pon., 10 sty 2022 o 21:27 Richard Huesken <<a href="mailto:richard.huesken@gmail.com" target="_blank">richard.huesken@gmail.com</a>><br>napisa?(a):<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>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/e288e782/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/e288e782/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 10<br>Date: Tue, 11 Jan 2022 02:02:45 -0500<br>From: "Regina Obe" <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</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] Postgis Raster determine exact hull<br>Message-ID: <001701d806b9$3c78dd60$b56a9820$@<a href="http://pcorp.us" target="_blank">pcorp.us</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>You could also try using ST_Polygon<br><br><br><br>It will treat all non NA as the same resulting in a polygon or multipolygon.<br><br><br><br><a href="https://postgis.net/docs/RT_ST_Polygon.html" target="_blank">https://postgis.net/docs/RT_ST_Polygon.html</a><br><br><br><br><br><br><br><br>From: postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] On Behalf Of Marcin Mionskowski<br>Sent: Tuesday, January 11, 2022 12:43 AM<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] Postgis Raster determine exact hull<br><br><br><br>Hi,<br><br>Try to reclassify the raster first so that all "non NA" values are equal (e.g. 1), then do ST_DumpAsPolygons.<br><br>Regards,<br><br>Marcin<br><br><br><br>pon., 10 sty 2022 o 21:27 Richard Huesken <<a href="mailto:richard.huesken@gmail.com" target="_blank">richard.huesken@gmail.com</a> <mailto:<a href="mailto:richard.huesken@gmail.com" target="_blank">richard.huesken@gmail.com</a>> > napisa?(a):<br><br>hi,<br><br><br><br>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.<br><br><br><br>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.<br><br><br><br>Are there more clever (and faster!) ways to get the exact hull of a raster?<br><br><br><br>Thanks in advance,<br><br><br><br>Richard.<br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a> <mailto:<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>> <br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/1541b17f/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/1541b17f/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 11<br>Date: Tue, 11 Jan 2022 12:23:43 +0100<br>From: Calle Hedberg <<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>><br>To: Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>><br>Cc: PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>Subject: Re: [postgis-users] PostGIS problem after updating from 3.1.4<br> to 3.2.0<br>Message-ID:<br> <CAPB4dVi=6izkAJCEV4vqRHD-fexX4mu7w_wV_Pp8X54=<a href="mailto:KANF-w@mail.gmail.com" target="_blank">KANF-w@mail.gmail.com</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Dear Regina,<br><br>Just running SELECT postgis_extensions_upgrade(); did not work.<br><br>I reinstalled PostGIS 3.1.4, and verified that it was functional.<br><br>I then reinstalled 3.2.0 on top of that, this time successfully (not sure<br>why I had to remove 3.1.4 the first time, but...), and then ran<br>SELECT postgis_extensions_upgrade();<br>SELECT postgis_full_version();<br>on all databases, and that worked OK.<br><br>So I will do the same for PG14 and ditto on my other two systems. It's a<br>bit time consuming since I have 150-200 databases in total, so if you can<br>fix that bug so that there is no need to run the extension upgrade command<br>on every db it would be great.. I've got one pg10 and one pg12 installation<br>too to cater for some backward compatibility and to provide an upgrade path<br>for old databases, but I'm leaving those on 3.0 / 3.1<br><br>Thanks again for the rapid response and the clear instructions.<br><br>Best regards<br>Calle<br><br><br>On Tue, 11 Jan 2022 at 04:21, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br><br>> Side note I?ve ticketed the issue here:<br>><br>><br>><br>> <a href="https://trac.osgeo.org/postgis/ticket/5045" target="_blank">https://trac.osgeo.org/postgis/ticket/5045</a><br>><br>><br>><br>> and will update once I release a new package<br>><br>><br>><br>><br>><br>> *From:* Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>]<br>> *Sent:* Monday, January 10, 2022 10:21 PM<br>> *To:* '<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>' <<a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a>>; 'PostGIS Users<br>> Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>> *Subject:* RE: [postgis-users] PostGIS problem after updating from 3.1.4<br>> to 3.2.0<br>><br>><br>><br>> Hmm okay it looks like I forgot to take off the minor version in my<br>> release so all the libs<br>><br>><br>><br>> Have -3.2 on them instead of -3. I guess I didn?t notice cause I usually<br>> just install the new version over the old.<br>><br>> And then run<br>><br>><br>><br>> SELECT postgis_extensions_upgrade();<br>><br>><br>><br>> Though I?m still surprised it?s giving an error as I thought we fixed that<br>> issue a long time ago to handle a case where the lib file has been removed.<br>><br>> So that seems like a reemerging old bug.<br>><br>><br>><br>> That said , while I?m making a new package. Can you do the following:<br>><br>><br>><br>> First try if:<br>><br>> -- works without doing anything else<br>><br>> SELECT postgis_extensions_upgrade();<br>><br>><br>><br>> If the above still gives you an error, do the following<br>><br>><br>><br>> Reinstall PostGIS 3.1.4<br>><br>> Reinstall PostGIS 3.2.0<br>><br>> Then run<br>><br>><br>><br>> SELECT postgis_extensions_upgrade();<br>><br>><br>><br>> In each of your databases.<br>><br>><br>><br>><br>><br>><br>><br>> *From:* postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a><br>> <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>>] *On Behalf Of *Calle Hedberg<br>> *Sent:* Monday, January 10, 2022 9:53 PM<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>><br>><br>><br>> Hi,<br>><br>><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>><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>><br>> ERROR: could not access file "$libdir/postgis-3": No such file or<br>> directory CONTEXT: SQL statement "SELECT public.postgis_lib_version()"<br>> PL/pgSQL function postgis_full_version() line 26 at SQL statement SQL<br>> state: 58P01<br>><br>><br>><br>> If I run "ALTER EXTENSION postgis UPDATE TO "3.2.0"; " I get a similar<br>> message:<br>><br>> ERROR: could not access file "$libdir/postgis-3": No such file or<br>> directory CONTEXT: PL/pgSQL function<br>> _postgis_drop_function_if_needed(text,text) line 6 at FOR over SELECT rows<br>> SQL state: 58P01<br>><br>><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>><br>><br>> I have tried to re-start pg, reboot the machine, and googling the issue,<br>> to no avail.<br>><br>><br>><br>> I can see that postgis 3.2.0 has been installed:<br>><br>> D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2<br>><br>><br>><br>> I see the error message states it cannot find postgis-3 - but there IS no<br>> such file or directory, as you can see the directory is actually called<br>> postgis-3.2 . But I don't know if that's a bug or what...<br>><br>><br>><br>> Any suggestions - or will I have to dump all my databases and then<br>> re-install pg 13 and pg14 afresh?<br>><br>><br>><br>> Best regards<br>><br>> Calle<br>><br>><br>><br><br><br>-- <br><br>*Carl-Anders (Calle) Hedberg*<br><br>HISP<br><br>Researcher & Technical Specialist<br><br>Health Information Systems Programme ? South Africa<br><br>Cell: +47 41461011 (Norway)<br><br>Iridium SatPhone: +8816-315-19119 (usually OFF)<br><br>E-mail1: <a href="mailto:calle@hisp.org" target="_blank">calle@hisp.org</a><br><br>E-mail2: <a href="mailto:calle.hedberg@gmail.com" target="_blank">calle.hedberg@gmail.com</a><br><br>Skype: calle_hedberg<br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/3222bf6b/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/3222bf6b/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 12<br>Date: Tue, 11 Jan 2022 16:04:10 +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: Re: [postgis-users] How best to create and use associative<br> array type in Postgres?<br>Message-ID:<br> <<a href="mailto:CA%2Bi5JwaUUeEL5vPj%2B-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w@mail.gmail.com" target="_blank">CA+i5JwaUUeEL5vPj+-cC-8tuZVPzGt-wpDNnMt-ew3ozRnZC4w@mail.gmail.com</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Hi, Regina,<br><br>This looks offering some clarity and simplicity.<br><br>I was told that hstore can also work as associative array. Does it offer<br>clarity and simplicity?<br><br>Regards,<br><br>Shao<br><br>On Sat, 8 Jan 2022 at 04:20, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br><br>> Oh forgot one more very useful operator, the subtraction operator.<br>> Removes a key/value from the list:<br>><br>><br>><br>> SELECT '{"color": "blue", "height_m": 10}'::jsonb - 'color'<br>><br>><br>><br>> Returns:<br>><br>> {"height_m": 10}<br>><br>><br>><br>><br>><br>> *From:* Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>]<br>> *Sent:* Friday, January 7, 2022 11:18 PM<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] How best to create and use associative<br>> array type in Postgres?<br>><br>><br>><br>> David,<br>><br>><br>><br>> Not sure what you are asking? There are many functions for jsonb and even<br>> more the newer your PostgreSQL is.<br>><br>> Take a look at -<br>> <a href="https://www.postgresql.org/docs/current/functions-json.html" target="_blank">https://www.postgresql.org/docs/current/functions-json.html</a><br>><br>><br>><br>><br>><br>> You can pull arrays by index but not really key/values by index (because<br>> jsonb reorders keys/values for efficiency). So order shouldn?t matter in<br>> jsonb as the same level keys are unique.<br>><br>> The trick of using the concatenation operator (||) to update keys values<br>> works, because the last entry for a key wins, and any key not in the list<br>> gets replaced by the last one. So I guess your popping idea<br>><br>><br>><br>> Take for example:<br>><br>><br>><br>> SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color":<br>> "red"}'::jsonb || '{"width_m": 5}';<br>><br>><br>><br>> Returns:<br>><br>> {"color": "red", "width_m": 5, "height_m": 10}<br>><br>><br>><br>> Note how the entry width_m was added, but not the order you specified it,<br>> and that the color was changed from blue to red.<br>><br>><br>><br>> Now if you wanted to get a set of all the key value pairs, you?d use<br>> jsonb_each_text (to get value as text) or jsonb_each to get the value as a<br>> jsonb.<br>><br>><br>><br>> Here is an example:<br>><br>> WITH a AS (SELECT '{"color": "blue", "height_m": 10}'::jsonb || '{"color":<br>> "red"}'::jsonb || '{"width_m": 5}' AS data)<br>><br>> SELECT kv.*<br>><br>> FROM a, jsonb_each_text(a.data) AS kv;<o:p></o:p></p><p class=MsoNormal>><br>><br>><br>> Returns:<br>><br>> color red<br>><br>> width_m 5<br>><br>> height_m 10<br>><br>><br>><br>> Now lets do this with PostGIS J<br>><br>> WITH a AS (<br>><br>> SELECT ST_AsGeoJSON(ST_MakeLine( ARRAY[ST_Point(1,2), ST_Point(3,4),<br>> ST_Point(-9,1)]))::jsonb AS data<br>><br>> )<br>><br>> SELECT kv.key, kv.value, kv.value->2->>0 AS last_x<br>><br>> FROM a, jsonb_each(a.data) AS kv;<br>><br>><br>><br>> key | value | last_x<br>><br>> -------------+---------------------------+--------<br>><br>> type | "LineString" |<br>><br>> coordinates | [[1, 2], [3, 4], [-9, 1]] | -9<br>><br>> (2 rows)<br>><br>><br>><br>><br>><br>><br>><br>> *From:* postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a><br>> <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>>] *On Behalf Of *Shaozhong SHI<br>> *Sent:* Friday, January 7, 2022 9:25 PM<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] How best to create and use associative<br>> array type in Postgres?<br>><br>><br>><br>> Hi, Regina,<br>><br>><br>><br>> That is interesting!<br>><br>><br>><br>> How to add new entries to the dictionary. E.g., weight: 40?<br>><br>><br>><br>> Can the dictionary to serve as a collection of paired key, value set, so<br>> that we can accumulate data to be processed?<br>><br>><br>><br>> Then, we can deal with the first, then 2nd and so on in turn?<br>><br>><br>><br>> Or, we can do things like pip and pop? Namely, when we have dealt with<br>> the first key, value pair, it will be out the dictionary, so that we can be<br>> sure that we are dealing with each key, value pair in turn?<br>><br>><br>><br>> Alternatively, can we fetch each key, value pair by its index or position?<br>><br>><br>><br>> Regards,<br>><br>><br>><br>> David<br>><br>><br>><br>> On Fri, 7 Jan 2022 at 21:19, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>><br>> Use JSONB datatype.<br>><br>><br>><br>> CREATE TABLE test(id integer, data jsonb);<br>><br>> TRUNCATE tABLE test;<br>><br>> INSERT INTO test(id, data)<br>><br>> VALUES (1, '{"color": "red", "height_m": 10}');<br>><br>><br>><br>> -- PG14 or higher ? you can used subscript feature<br>><br>> UPDATE test SET data['color'] = to_jsonb('blue'::text),<br>><br>> data['height_m'] = to_jsonb(10), data['width_m'] = to_jsonb(2)<br>><br>> WHERE id = 1;<br>><br>><br>><br>> -- PG14 or lower<br>><br>> UPDATE test SET data = jsonb_set(data, ARRAY['color'],<br>> to_jsonb('blue'::text), true)<br>><br>> WHERE id = 1;<br>><br>><br>><br>> -- PG14 or lower to set multiple<br>><br>> UPDATE test SET data = data || '{"color": "blue", "height_m": 10}'::jsonb;<br>><br>><br>><br>> -- To read (all versions)<br>><br>> SELECT data->>'color' AS color, (data->>'height_m')::integer As height_m<br>><br>> FROM test;<br>><br>> *From:* postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>] *On<br>> Behalf Of *Shaozhong SHI<br>> *Sent:* Wednesday, January 5, 2022 1:30 PM<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] How best to create and use associative array<br>> type in Postgres?<br>><br>><br>><br>><br>><br>> In Oracle, one can create and use associative array. For instance,<br>><br>> TYPE FID_MEASURE IS TABLE OF NUMBER INDEX BY VARCHAR2(38);<br>><br>> NODES_WAITING FID_SET;<br>><br>><br>><br>> How best to create and use associative array type in Postgres?<br>><br>><br>><br>> Or, what is the best/most efficient equivalent in Postgres?<br>><br>><br>><br>> Regards,<br>><br>><br>><br>> David<br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>><br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>><br>-------------- next part --------------<br>An HTML attachment was scrubbed...<br>URL: <<a href="http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/299f405f/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/299f405f/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 13<br>Date: Tue, 11 Jan 2022 16:38:58 +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] Using Spike finder in PostGIS?<br>Message-ID:<br> <<a href="mailto:CA%2Bi5JwZVJUUcCgRwubeqmE4YOaAL3Q8%2B_QhHdCQjeeBOO6K_Nw@mail.gmail.com" target="_blank">CA+i5JwZVJUUcCgRwubeqmE4YOaAL3Q8+_QhHdCQjeeBOO6K_Nw@mail.gmail.com</a>><br>Content-Type: text/plain; charset="utf-8"<br><br>Have they ever solved this one?<br><br>polygon - Using Spike finder in PostGIS? - Geographic Information Systems<br>Stack Exchange<br><<a href="https://gis.stackexchange.com/questions/101525/using-spike-finder-in-postgis" target="_blank">https://gis.stackexchange.com/questions/101525/using-spike-finder-in-postgis</a>><br><br>Is there one that can offer clarity and simplicity?<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/ab05332d/attachment-0001.html" target="_blank">http://lists.osgeo.org/pipermail/postgis-users/attachments/20220111/ab05332d/attachment-0001.html</a>><br><br>------------------------------<br><br>Message: 14<br>Date: Tue, 11 Jan 2022 17:45:16 +0000<br>From: Nathan Wagner <<a href="mailto:nw@hydaspes.if.org" target="_blank">nw@hydaspes.if.org</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: <<a href="mailto:Yd3CLIsGljRePZ2J@granicus.if.org" target="_blank">Yd3CLIsGljRePZ2J@granicus.if.org</a>><br>Content-Type: text/plain; charset=us-ascii<br><br>On Tue, Jan 11, 2022 at 01:18:27AM +0100, Sandro Santilli wrote:<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>So, what I guess I'm a bit confused about is what I get out of a select<br>or copy? What is the difference between the "internal representation"<br>and what I get from a raw select or copy?<br><br>Suppose, for example, I have a table with a geometry column "geom". If<br>I do a "select geom from table", I get what looks like a hex<br>representation of a binary value. Is that a hex encoded internal<br>representation, or some external representation that did not change<br>between 1.5 and 2.5? Will this value then be converted to the correct<br>internal representation on the 2.5 side?<br><br>Another way to put this is will the following work?<br><br>psql -c '\copy (select geom from table) to stdout' -d postgis15 |<br>psql -c '\copy table (geom) from stdin' -d postgis25<br><br>The exact syntax is probably different as that is from memory, but I<br>trust that the essence of what I'm trying to do is clear.<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>Could this have been done via 'create extension postgis from unpackaged'?<br>I think that doesn't work for an in-place upgrade because it can't<br>handle converting the internal representation.<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>Client reluctance mostly. The upgrade was also planned before v3 was<br>out. If it were my DB I'd go to 3.x on pg 14.<br><br>-- <br>nw<br><br><br>------------------------------<br><br>Message: 15<br>Date: Tue, 11 Jan 2022 09:57:40 -0800<br>From: Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</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: <<a href="mailto:A05C18A9-0191-4360-8F1A-7B5FC0F61B7B@cleverelephant.ca" target="_blank">A05C18A9-0191-4360-8F1A-7B5FC0F61B7B@cleverelephant.ca</a>><br>Content-Type: text/plain; charset=us-ascii<br><br><br><br>> On Jan 11, 2022, at 9:45 AM, Nathan Wagner <<a href="mailto:nw@hydaspes.if.org" target="_blank">nw@hydaspes.if.org</a>> wrote:<br>> <br>> On Tue, Jan 11, 2022 at 01:18:27AM +0100, Sandro Santilli wrote:<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>> So, what I guess I'm a bit confused about is what I get out of a select<br>> or copy? What is the difference between the "internal representation"<br>> and what I get from a raw select or copy?<br>> <br>> Suppose, for example, I have a table with a geometry column "geom". If<br>> I do a "select geom from table", I get what looks like a hex<br>> representation of a binary value. Is that a hex encoded internal<br>> representation, or some external representation that did not change<br>> between 1.5 and 2.5? Will this value then be converted to the correct<br>> internal representation on the 2.5 side?<br><br>The internal representation is what is written on the disk.<br>The "canonical form" is what you get when you run "select geom from mytable", or just pg_dump the table.<br>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).<br>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.<br>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.<br><br>> Another way to put this is will the following work?<br>> <br>> psql -c '\copy (select geom from table) to stdout' -d postgis15 |<br>> psql -c '\copy table (geom) from stdin' -d postgis25<br><br>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.<br><br>P.<br><br>> <br>> The exact syntax is probably different as that is from memory, but I<br>> trust that the essence of what I'm trying to do is clear.<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>> Could this have been done via 'create extension postgis from unpackaged'?<br>> I think that doesn't work for an in-place upgrade because it can't<br>> handle converting the internal representation.<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>> Client reluctance mostly. The upgrade was also planned before v3 was<br>> out. If it were my DB I'd go to 3.x on pg 14.<br>> <br>> -- <br>> nw<br>> _______________________________________________<br>> postgis-users mailing list<br>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br><br><br><br>------------------------------<br><br>Subject: Digest Footer<br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br><br><br>------------------------------<br><br>End of postgis-users Digest, Vol 239, Issue 7<br>*********************************************<o:p></o:p></p></blockquote></div></div></div></div></body></html>