Re: Update postgis in a replicated PostgreSQL 12 database now with postgis-3
Hi Andrew, replies inline On Mon, Jun 24, 2024 at 05:38:50PM +0100, Andrew Hardy wrote: > We noticed some odd failures executing a SELECT on a table with a point > type column. The error was: 'could not access file "$libdir/postgis-2.5": > No such file or directory' . This means you have some left-over old PostGIS functions in your database, so the upgrade was not complete. What does the following query return ? SELECT postgis_full_version() --strk; signature.asc Description: PGP signature
Re: Questions regarding Postgis 3.4.2 distribution-tarballs and MD5 hash
On Thu, May 30, 2024 at 11:31:47AM +, Steve Underwood via postgis-users wrote: > Wondering if the distribution-tarball being referenced on the > "postgis.net/development/source_code/" web-page was somehow incorrect The tarball link was correct, but the MD5 file was wrong. Please try again and you should get a match now. REF: https://trac.osgeo.org/postgis/ticket/5736 Thanks for reporting the issue. --strk; signature.asc Description: PGP signature
Re: Running clustering queries
On Wed, May 08, 2024 at 05:53:48PM -0400, Regina Obe wrote: > On Wed, May 08, 2024 at 03:01:47PM -0500, Daryl Herzmann wrote: > > > > I stumbled into this issue today. The lack of 9311 in spatial_ref_sys. Is > > a fix > > for this coming or is the spatial_ref_sys just known to be stale and not > > getting > > updated with updated postgis releases? > > Paul and Sandro, > Any thoughts on this. It looks like the latest upgrade of spatial_ref_sys was done in 2019, with commit 4d4891e120667bb137c5d292405938a92cb3e59d My thought is that an upgrade is due... As for HOW spatial_ref_sys should be upgraded, the discussion is still open. At the moment old records are always retained (never updated) and new records are added (based on SRID). Background: https://trac.osgeo.org/postgis/ticket/5024 My proposal was to always upgrade the "PostGIS official database" and allow users override. The presence of `postgis_srs_all()` and friends allow querying the "PROJ official database" but I think a "PostGIS official database" still has a role as PostGIS dumps reference SRS just by SRID number, which doens't have a direct match with PROJ SRS identifiers (lacks an "authority", being implicitly PostGIS itself). I still have the branch in which I've worked to give the PostGIS official database a stable place ( a spatial_ref_sys_postgis table ) if anyone is interested in reviving it. --strk; signature.asc Description: PGP signature
Re: Problem to Upgrade from 3.2.3 to 3.4.0
On Wed, Feb 14, 2024 at 05:09:44PM +0400, Arnaud Vandecasteele wrote: > The default_version is still 3.2.3 and I suppose this is why postgis > doesn't really upgrade. It only keeps the same version. > > Do you know how I could resolve this issue ? Whichever version is installed last determines the default version, so I guess 3.2.3 was installed *after* 3.4.0. You'll have to re-install the newer PostGIS version. Maybe take the chance to directly install 3.4.2 (released on Feb 08, 2024) to get more bug fixes. --strk; signature.asc Description: PGP signature
Re: Change in mailing list configuration
On Thu, Jan 11, 2024 at 07:15:02PM +0100, Sandro Santilli via postgis-users wrote: > We've changed the configuration of this mailing list to respect the > recomendations from the SysAdmin team: > > https://wiki.osgeo.org/wiki/SAC:Mailing_Lists#Configuring_the_mailing_list Hopefully now the config is also *really* changed --strk; signature.asc Description: PGP signature
[postgis-users] Change in mailing list configuration
We've changed the configuration of this mailing list to respect the recomendations from the SysAdmin team: https://wiki.osgeo.org/wiki/SAC:Mailing_Lists#Configuring_the_mailing_list Notable changes are: 1. No more [postgis-users] prefix in email subjects 2. No more added footer 3. Real sender address in the From field 4. DKIM signed emails will not be considered maliciously tampered \o/ For filtering the emails or finding mailing list address to change user preferences you will be able to use the email headers. Many mail user agents should be able to figure this out by themselves, others might need manual configuration. Just as an example, my mail configuration for the PostGIS Developers looks like this (procmail recipe): :0 * ^List-ID:.*postgis-devel.lists.osgeo.org * !^X-List-Administrivia: yes .postgis-devel/ --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html signature.asc Description: PGP signature
Re: [postgis-users] Topology - RemoveUnusedPrimitives workflow
On Wed, Nov 08, 2023 at 10:05:39AM +, Alexandre Neto wrote: > Thanks Sandro, I will. And I will try to PR on the documentation. > > One extra question. When I add a topoGeometry column to a table with a > geometry column, in QGIS, I now fail to be able to edit the table. It seems > that the PostGIS provider forces me to use a key called tid. Nevertheless, I > can't find this column anywhere in the table. The workaround is to load the > layer in QGIS before adding the TopoGeometry column, which is cumbersome. Is > this intended or could it be a bug? Sounds like a bug to me. When you file it make sure to specify which layer load method you use as I'm pretty sure the browser and the postgresql-specific panel behave differently. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Topology - RemoveUnusedPrimitives workflow
On November 7, 2023 12:40:47 PM GMT+01:00, Alexandre Neto
wrote:
>Hi Sandro,
>
>Many thanks for your answer, I think that running the clearTopoGeom in the
>trigger function may work, as the idea is do all this inside a before update
>trigger function row by row, whenever the original geometry is changed.
>
>Thanks,
>
>Alexandre Neto
>
>On Tue Nov 7, 2023, 11:33 AM GMT, Sandro Santilli <mailto:s...@kbt.io> wrote:
>> On Thu, Nov 02, 2023 at 03:26:22PM +, Alexandre Neto via postgis-users
>> wrote:
>>> UPDATE temp.troco SET
>>> topo = totopogeom(geom, 'master_topology',1)
>>
>> [..]
>>> Now I decided to remove one of the linestrings that splited two polygons.
>>>
>>> And I rerun the updated on the topo columns:
>>>
>>> UPDATE temp.troco SET
>>> topo = totopogeom(geom, 'master_topology',1)
>>
>> This call does NOT remove the old TopoGeometries, which are left
>> orphaned (defined but not appearing in any TopoLogy Layer).
>>
>> The returned text from this query should mention this problem:
>>
>> SELECT TopologySummary('master_topology')
>>
>> The clearTopoGeom function can be used to destroy a TopoGeometry,
>> but at this point it would probably be faster for you to do the
>> cleanup with direct DELETE on master_topology.relation WHERE the
>> layer_id/topogeo_id pair are not found in their respective layers
>> (dangerous operation). Suggestion/patches are welcome to improve
>> this situation. I've also noted the example for clearTopoGeom on
>> the manual is misleading:
>>
>> https://postgis.net/docs/clearTopoGeom.html
>>> SELECT RemoveUnusedPrimitives('master_topology');
>>> It returned saying that no edges were removed.
>>
>> It should tell you more if you:
>>
>> set client_min_messages to debug;
>>> My idea was to keep the topology in sync with the lines geometries by
>>> running this steps in trigger functions
>>
>> Maybe you can consider running the clearTopoGeom in a trigger function
>> too (on UPDATE...)
>>
>> --strk;
Feel free to file an enhancement ticket for a RemoveOrphamedTopogeometries
function to clean up the state you found yourself in
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Topology - RemoveUnusedPrimitives workflow
On Thu, Nov 02, 2023 at 03:26:22PM +, Alexandre Neto via postgis-users
wrote:
> UPDATE temp.troco SET
> topo = totopogeom(geom, 'master_topology',1)
[..]
> Now I decided to remove one of the linestrings that splited two polygons.
>
> And I rerun the updated on the topo columns:
>
> UPDATE temp.troco SET
> topo = totopogeom(geom, 'master_topology',1)
This call does NOT remove the old TopoGeometries, which are left
orphaned (defined but not appearing in any TopoLogy Layer).
The returned text from this query should mention this problem:
SELECT TopologySummary('master_topology')
The clearTopoGeom function can be used to destroy a TopoGeometry,
but at this point it would probably be faster for you to do the
cleanup with direct DELETE on master_topology.relation WHERE the
layer_id/topogeo_id pair are not found in their respective layers
(dangerous operation). Suggestion/patches are welcome to improve
this situation. I've also noted the example for clearTopoGeom on
the manual is misleading:
https://postgis.net/docs/clearTopoGeom.html
> SELECT RemoveUnusedPrimitives('master_topology');
> It returned saying that no edges were removed.
It should tell you more if you:
set client_min_messages to debug;
> My idea was to keep the topology in sync with the lines geometries by running
> this steps in trigger functions
Maybe you can consider running the clearTopoGeom in a trigger function
too (on UPDATE...)
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Weblate closed registration
On Fri, Sep 01, 2023 at 12:04:12AM -0400, Regina Obe wrote: > Weblate.osgeo.org requires an osgeo account. > Which you can get here: > https://id.osgeo.org/ldap/create > > You can use it to: > 1.File bug reports in PostGIS bug tracker https://trac.osgeo.org/postgis Also file or comment on bugs in OSGeo bug tracker, like on this for example: https://trac.osgeo.org/osgeo/ticket/2694 :) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3
On Thu, Aug 31, 2023 at 11:14:21AM +, atanga MARCELIUS via postgis-users wrote: > As mentioned earlier I tried the soft upgrade and it failed. Did you also mention how it failed ? The failure below is NOT what you'd get with Regina's instructions > > x=# ALTER EXTENSION postgis UPDATE TO '2.5.5'; > > ERROR: extension "postgis" does not exist The "soft upgrade" Regina is referring to is: (1) without extension to 3.0 - https://postgis.net/docs/en/postgis_administration.html#soft_upgrade_sql_script psql -f postgis-3.0/postgis_upgrade.sql (2) with extension from 3.0 up - https://postgis.net/docs/en/postgis_administration.html#soft_upgrade_extensions psql -c 'SELECT postgis_extensions_upgrade()' --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] DMARC config
On Thu, Aug 24, 2023 at 10:13:39AM +0200, Sandro Santilli wrote: > On Thu, Aug 24, 2023 at 10:01:56AM +0200, Sandro Santilli wrote: > > On Wed, Aug 23, 2023 at 08:38:54PM -0400, Greg Troxel wrote: > > > Sandro Santilli writes: > > > > > > > On Wed, Aug 16, 2023 at 08:46:08AM +0200, Paolo Cavallini wrote: > > > >> Dear admin, > > > >> the list, as many @osgeo were, is misconfigured for its DMARC. I > > > >> suggest to > > > >> change one of the settings. > > > > > > > > Hi Paolo, thanks for the heads up. > > > > I've followed Markus Neteler's recipe here: > > > > https://trac.osgeo.org/osgeo/ticket/2475#comment:43 > > > > > > > > My understanding is that this should ONLY affect the emails coming > > > > from a domain with DMARC Reject/Quarantine Policy so your reply to > > > > this email might have the From munged while mine might not. > > > > > > > > This mail is sent to postgis-users so we can use it also as a test :) > > > > > > test failed because your domain does not sign with dkim. > > > > Test succeeded as it shows From is not changed for me ;) > > But it is incomplete until Paolo replies, I guess. > > > > > In all seriousness, just configure the list not to modify messages, > > > other than adding List-Foo headers, and the problems all go away. > > > > I'm actually ok with this change, users might need to update their > > filters but parsing headers should be easier than subject or footer, > > will bring it up for vote on PSC. > > Still, I think this should be tested by the reporter as I'm really > flying blind when it comes to DMARC. In particular, I wonder why > Mailman does not include a "do not modify subject" and "do not add > footer" as options in the dmarc_moderation_action options list. For reference: osgeo-discuss seem to be always munging the from and adding a Reply-To: original_sender. From where I'm standing (mutt) it seems to be handled very well. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] DMARC config
On Thu, Aug 24, 2023 at 10:01:56AM +0200, Sandro Santilli wrote: > On Wed, Aug 23, 2023 at 08:38:54PM -0400, Greg Troxel wrote: > > Sandro Santilli writes: > > > > > On Wed, Aug 16, 2023 at 08:46:08AM +0200, Paolo Cavallini wrote: > > >> Dear admin, > > >> the list, as many @osgeo were, is misconfigured for its DMARC. I suggest > > >> to > > >> change one of the settings. > > > > > > Hi Paolo, thanks for the heads up. > > > I've followed Markus Neteler's recipe here: > > > https://trac.osgeo.org/osgeo/ticket/2475#comment:43 > > > > > > My understanding is that this should ONLY affect the emails coming > > > from a domain with DMARC Reject/Quarantine Policy so your reply to > > > this email might have the From munged while mine might not. > > > > > > This mail is sent to postgis-users so we can use it also as a test :) > > > > test failed because your domain does not sign with dkim. > > Test succeeded as it shows From is not changed for me ;) > But it is incomplete until Paolo replies, I guess. > > > In all seriousness, just configure the list not to modify messages, > > other than adding List-Foo headers, and the problems all go away. > > I'm actually ok with this change, users might need to update their > filters but parsing headers should be easier than subject or footer, > will bring it up for vote on PSC. Still, I think this should be tested by the reporter as I'm really flying blind when it comes to DMARC. In particular, I wonder why Mailman does not include a "do not modify subject" and "do not add footer" as options in the dmarc_moderation_action options list. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] DMARC config
On Wed, Aug 23, 2023 at 08:38:54PM -0400, Greg Troxel wrote: > Sandro Santilli writes: > > > On Wed, Aug 16, 2023 at 08:46:08AM +0200, Paolo Cavallini wrote: > >> Dear admin, > >> the list, as many @osgeo were, is misconfigured for its DMARC. I suggest to > >> change one of the settings. > > > > Hi Paolo, thanks for the heads up. > > I've followed Markus Neteler's recipe here: > > https://trac.osgeo.org/osgeo/ticket/2475#comment:43 > > > > My understanding is that this should ONLY affect the emails coming > > from a domain with DMARC Reject/Quarantine Policy so your reply to > > this email might have the From munged while mine might not. > > > > This mail is sent to postgis-users so we can use it also as a test :) > > test failed because your domain does not sign with dkim. Test succeeded as it shows From is not changed for me ;) But it is incomplete until Paolo replies, I guess. > In all seriousness, just configure the list not to modify messages, > other than adding List-Foo headers, and the problems all go away. I'm actually ok with this change, users might need to update their filters but parsing headers should be easier than subject or footer, will bring it up for vote on PSC. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Stuck with upgrade from postgresql 9.6 to postgresql 14 due to postgis2.3
On Wed, Aug 23, 2023 at 10:08:23AM +, atanga MARCELIUS wrote: > x=# ALTER EXTENSION postgis UPDATE TO '2.5.5'; > ERROR: extension "postgis" does not exist The above message means that database "x" does not contain the "postgis extension". This does not necessarely mean it doesn't contain postgis objects, if you're coming from a very old install as you seem to be coming from. What does this query return? SELECT postgis_full_version(); And this one ? SELECT oid::regprocedure,probin FROM pg_catalog.pg_proc WHERE proname LIKE 'postgis_%version'; --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Chinese translation
Hi WangDapeng, On Wed, Aug 23, 2023 at 02:00:31PM +0800, Light wrote: > I see that the Chinese translation of the documentation is missing and I > want to contribute. We submit 3-4 files per week. There's some simplified chinese translation on weblate: https://weblate.osgeo.org/languages/zh_Hans/postgis/ Is that what the language you want to translate to ? > Which branch should I use? > I saw KDE, any more detailed instructions? The easiest way to contribute is via weblate, which is the URL above. At the moment the templates for that language are probably outdated as there's no support in the build scripts for it. If you confirm that's the language you want to contribute to I can add the support for it, just let me know what's the recommended suffix for it (-zh or -zh_Hans ?). Best way to handle such addition would be to file a trac ticket for it on https://trac.osgeo.org/postgis You'll need an OSGeo UserID to both file the ticket and contribute the translation via weblate. If you don't yet have one it can be created from https://id.osgeo.org/ldap/create Welcome ! --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] views
On Wed, Aug 23, 2023 at 12:36:58PM +0200, Nicolas Ribot wrote: > (OpenJump is a GIS software that can display such views/tables > containing heterogeneous geometry types, as QGIS does not like them) I believe recent QGIS versions do support views with heterogeneous geometries, not in the same layer but specifying which geometry type you want to load in each layer (multiple layers possibly coming from the same view). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] DMARC config
On Wed, Aug 16, 2023 at 08:46:08AM +0200, Paolo Cavallini wrote: > Dear admin, > the list, as many @osgeo were, is misconfigured for its DMARC. I suggest to > change one of the settings. Hi Paolo, thanks for the heads up. I've followed Markus Neteler's recipe here: https://trac.osgeo.org/osgeo/ticket/2475#comment:43 My understanding is that this should ONLY affect the emails coming from a domain with DMARC Reject/Quarantine Policy so your reply to this email might have the From munged while mine might not. This mail is sent to postgis-users so we can use it also as a test :) --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] PostGIS 3.3.4 released
Patch release 3.3.4 is now available. Details and download links can be found at: http://postgis.net/2023/07/PostGIS-3.3.4-Patch-Release/ Thanks, The PostGIS Development Team ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsGML with empty nprefix parametr makes database into recovery mode
On Wed, May 17, 2023 at 01:14:27PM +0200, Michal Seidl wrote: > this SQL puts my Postgre into recovery mode for some seconds? > The rason seems to be when nprefix parametr is set to ''. Thanks, confirmed and filed as a ticket: https://trac.osgeo.org/postgis/ticket/5384 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Fri, May 12, 2023 at 12:13:29PM +0200, Sandro Santilli wrote: > On Tue, May 09, 2023 at 03:59:13PM +0530, Nikhil Shetty wrote: > > > I installed 3.1.5 but we are still having the same issue when upgrading > > > > dbname=# alter extension postgis update ; > [...] > > ERROR: cannot drop function st_distance(geography,geography) because other > > objects depend on it > > DETAIL: materialized view . depends on function > > Interesting, did you edit the error log to omit and > or is that hiding coming from PostgreSQL itself ? > > This would be worth a ticket to think about ways to handle it, but the > message is pretty clear: PostGIS deprecated a function: > > st_distance(geography, geography) > > But your database is using it in the definition of a materialized view > (which one is hidden). > > We do have code in place to handle deprecations but this specific > function is missing the encantation to be handled properly. Filing > a ticket to https://trac.osgeo.org/postgis will help. I filed the ticket for you: https://trac.osgeo.org/postgis/ticket/5380#ticket Please consider subscribing to it PS: let this conversation only continue on postgis-devel --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Tue, May 09, 2023 at 03:59:13PM +0530, Nikhil Shetty wrote: > I installed 3.1.5 but we are still having the same issue when upgrading > > dbname=# alter extension postgis update ; [...] > ERROR: cannot drop function st_distance(geography,geography) because other > objects depend on it > DETAIL: materialized view . depends on function Interesting, did you edit the error log to omit and or is that hiding coming from PostgreSQL itself ? This would be worth a ticket to think about ways to handle it, but the message is pretty clear: PostGIS deprecated a function: st_distance(geography, geography) But your database is using it in the definition of a materialized view (which one is hidden). We do have code in place to handle deprecations but this specific function is missing the encantation to be handled properly. Filing a ticket to https://trac.osgeo.org/postgis will help. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Tue, Apr 18, 2023 at 12:14:55AM +0530, Nikhil Shetty wrote: > We will try upgrading to 3.1.5 I'd go to 3.1.8 which came out on 2022/11/12 All these patch-level releases really ONLY contain bug fixes, no new features, no new bugs. If you upgrade to 3.1.5 you'll miss all the following fixes, some of which are security releated. Why holding back ? PostGIS 3.1.8 2022/11/12 * Bug and Security Fixes - [security] Add schema qual to upgrade util (Regina Obe) - #5240, ST_DumpPoints crash with empty polygon (Regina Obe) - #4648, [security] Check function ownership at extension packaging time (Sandro Santilli) Thanks to Sven Klemm (Timescale) for the report - #5241, Crash on ST_SnapToGrid with empty multis (Regina Obe) - #5234, Fix 2.5d topology building regression (Sandro Santilli) - #5280, Handle load of dbase character fields with no width specified (Regina Obe) - #5084, Bad rasterization of linestring (Gilles Vuidel) PostGIS 3.1.7 2022/08/18 * Bug Fixes - #5191, Use integer instead of int4 (Regina Obe) - #5139, PostGIS causes to_jsonb to no longer be parallel safe, ST_AsGeoJSON and ST_AsGML are also parallel unsafe (Regina Obe, Paul Ramsey) - #5202, Guard against downgrades (Sandro Santilli) - #5209, #5210, Fix upgrades with CVE-2022-2625 PostgreSQL fix - #5032, Correctly read extent off multi-key GIST indexes (Paul Ramsey) - #5181, Reset proj error state after failed parse (Paul Ramsey) - #5171, Short circuit geodesic distance when inputs equal (Paul Ramsey) - Fix potential buffer overflow in long transaction locks (Paul Ramsey) PostGIS 3.1.6 2022/07/20 * Bug Fixes - #4835, Occasional distance errors in polar area (Paul Ramsey) - #5152, Fix infinite loop with ST_Split (Sandro Santilli) - #5120, Fix not-null result from ST_EstimatedExtent against truncated tables with spatial index (Sandro Santilli) - #5076, Avoid log storm installed with pgaudit enabled (Paul Ramsey) - #5100, Stop using pg_atoi, removed in PG 15 (Laurenz Albe) - #5115, Allow dropping topologies with pending constraints (Sandro Santilli) - #5151, ST_SetPoint with empty geometries (Regina Obe) - #5150, Change signature of AddToSearchPath (Regina Obe) - #5125, Fix search path function (Sandro Santilli) - #5155, More schema qual fixes (Regina Obe) - #5114, Crash with long column names in pgsql2shp (Paul Ramsey) - #4541, ST_ConcaveHull returns a "geometrycollection" type instead of the expected "polygon" (Regina Obe) - #5154, raster ST_Value is undercosted (Regina Obe) --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Mon, Apr 17, 2023 at 04:56:06PM +0200, Sandro Santilli wrote: > On Mon, Apr 17, 2023 at 03:18:50PM +0530, Nikhil Shetty wrote: > > > DETAIL: view x depends on function st_buffer(geometry,double precision) > > DETAIL: function x depends on function > > st_intersection(geometry,geometry) > > DETAIL: view x depends on function st_intersection(geometry,geometry) > > [...] > > > Is there a way to identify the list of objects being dropped? and which all > > views/functions depend on these dropped objects? > > Probably, but you should really consider upgrading to a newer version. > We added the ability to continue the upgrade in these cases by keeping > the "orphaning" the old functions, and having them reported by a > call to `SELECT postgis_full_version()` so that should give you the > list. See https://trac.osgeo.org/postgis/ticket/5033#comment:6 I'll add: according to the NEWS file this improved upgrade support was included in PostGIS version 3.1.5: https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.1.5/NEWS#L10 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Mon, Apr 17, 2023 at 03:18:50PM +0530, Nikhil Shetty wrote: > DETAIL: view x depends on function st_buffer(geometry,double precision) > DETAIL: function x depends on function st_intersection(geometry,geometry) > DETAIL: view x depends on function st_intersection(geometry,geometry) [...] > Is there a way to identify the list of objects being dropped? and which all > views/functions depend on these dropped objects? Probably, but you should really consider upgrading to a newer version. We added the ability to continue the upgrade in these cases by keeping the "orphaning" the old functions, and having them reported by a call to `SELECT postgis_full_version()` so that should give you the list. See https://trac.osgeo.org/postgis/ticket/5033#comment:6 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Error Upgrading PostGIS 2.5.4 to 3.1.2
On Fri, Apr 14, 2023 at 01:29:02PM +0530, Nikhil Shetty wrote:
> geo=# alter extension postgis update to '3.1.2';
> ERROR: could not access file "$libdir/postgis-2.5": No such file or directory
[...]
> We removed the old 2.5.4 packages and installed 3.1.2
>
> ls -l /usr/pgsql-10/lib | grep postgis
>
> -rwxr-xr-x 1 root root 1047128 Jun 29 2021 *postgis*-3.so
> -rwxr-xr-x 1 root root 850416 Jun 29 2021 *postgis*_raster-3.so
> -rwxr-xr-x 1 root root 435656 Jun 29 2021 *postgis*_sfcgal-3.so
> -rwxr-xr-x 1 root root 565008 Jun 29 2021 *postgis*_topology-3.so
A quick workaround should be something along these lines:
for f in $('ls' /usr/pgsql-10/lib/*postgis*-3.so); do
l=$(echo "$f" | sed 's/-3\./2.5./')
ln -vs "$f" "$l"
done
BUT I'm wondering: why would you want to upgrade to 3.1.2 when the
latest PostGIS version in the 3.1 branch is 3.1.8 ?
I'm asking because according to the NEWS file the bug you report
was fixed in 3.1.5:
https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.1.5/NEWS#L12
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Building Heirarchical Topology
On Wed, Feb 08, 2023 at 02:44:54PM -0500, Regina Obe wrote: > Your topo should be a collection of topo elements, not the collection of > primitives that make up the topo. I suggest to use different wording, as "topo" is too vague :) So I'd reword as: Your TopoGeometry should be defined by a collection of child TopoGeometry objects, not primitive topology elements. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Upgrade 12->13 stuck due to postgis / raster issue
On Tue, Jan 24, 2023 at 01:55:44AM -0500, Regina Obe wrote: > Strk, remind me why we have these notices, I think it just confuses people > more than being helpful. Debugging left over, I've changed that in commit 0e97ae29e to: - RAISE NOTICE 'Extension % is not available or not packagable for some reason', rec.name; + RAISE DEBUG 'Skipping % (not in use)', rec.name; --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] error: Directed line merging requires GEOS-3.11 or higher
On Tue, Dec 20, 2022 at 12:02:03PM -0500, Regina Obe wrote: > Sandro and Paul, is there any way we can expose the compiled version of GEOS > and Proj? I don't think there's a speicific function but some functions do include the compiled-in version in the error message, like ST_FrechetDistance does for instance, for builds against GEOS < 3.7.0 and ST_MaximumInscribedCircle for builds against GEOS < 3.9.0. But it's an encoded version (2 digits per minor and patch numbers). It may be useful indeed to have a "compiled-against" function, file a feature request ticket ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Row estimations
On Tue, Dec 13, 2022 at 11:31:05AM -0800, Paul Ramsey wrote: > Just to put a nail in this one: the last time our selectivity estimates for > Contains/Within were good was version 2.1, which used the && operator instead > of hte ~/@ operators in the SQL wrapper functions. I think I was the one chaing from && to ~/@ as they are more correct. If we're going to change the selectivity estimator we should consider using a different name to estimate the different filters, just in case we'll use different implementation for one or the other. For points they are really equal but for polygons estimates could be very different for intersect and full containment (polygons could only touch or have small overlap but very never be nested). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS topology behaviour with polygons
On Wed, Dec 07, 2022 at 11:13:08AM +0100, Douglas Fan wrote: > I am trying to build topology on PostGIS with 4000+ polygons. The input are > not clean, with overlaps and gaps. I have created a procedure to commit > each update statement for each polygon so that I can catch the exception > and continue the process. The result still contain gaps and overlaps that > are smaller than the tolerance and expected to be snapped. Expectance is wrong. It's expected for the resulting dataset to contain gaps and overlaps. Having them in a topological model makes it easier for you to spot them and fix them. > I have also tried to run toTopoGeom in the order of the number of vertices > of each polygon, with the idea that creating polygons with more vertices > may help the snapping process. It doesn't solve the problem and the number > of slivers is almost the same. However I have noticed that sequence of the > polygon to run toTopoGeom changes the result. Yes, order changes result because incoming geoms are snapped to already-loaded ones. But snapping is only done against vertices, not segments, so if you want to increas snapping you should densify your geometries. Check out ST_Segmentize. My suggestion though is to complete the data load process and AFTERWARDS look for gaps/overlaps/slivers and process them. --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Corrupted topology when using totopogeom in 3D topology
On Tue, Sep 06, 2022 at 09:50:04AM +, Alexandre Silva wrote: > Hey Sandro, > > Thanks for your help. > Here's the ticket in case anyone wants to follow this bug: > https://trac.osgeo.org/postgis/ticket/5234 Thank you for your report. The regression should now be fixed in all branches (was introduced in 3.0.2). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Queries slow after PostGIS upgrade
On Tue, Nov 01, 2022 at 10:07:34AM +0100, Chris Mair wrote: > So the problem here was an optimization introduced in Postgres 12, in your > case it did more harm than good: > https://www.postgresql.org/docs/12/release-12.html#id-1.11.6.17.5.3.4 > > Quote: > > Specifically, CTEs are automatically inlined [...] Inlining can be > prevented by specifying MATERIALIZED [...] > Previously, CTEs were never inlined [...] I've been stroke by this issue multiple times. Making the default different from previous versions was probably a mistake here... --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Library function 'pgis_geometry_union_finalfn' was deprecated in PostGIS 3.3.0
Sandro Santilli wrote (please quote with appropriate attribution): > > This means that your current "default" PostGIS version is 2.4.9, > > so "postgis_extensions_upgrade()" tries to upgrade to that version, > > but it's actually a DOWNGRADE instead. On Mon, Oct 03, 2022 at 11:05:53AM +0200, Giuseppe Naponiello wrote: > > very strange: > > # apt policy postgis > postgis: > Installed: 3.3.1+dfsg-1.pgdg110+1 > Candidate: 3.3.1+dfsg-1.pgdg110+1 > I'm using a debian stable from some years, I have always used "apt" to > upgrade my packages so I never worried about version compatibility etc... What does this SQL statement return ? SELECT default_version FROM pg_catalog.pg_available_extensions WHERE name = 'postgis'; What does this shell command return ? grep default_version `pg_config --sharedir`/extension/postgis.control > > My suggestion is to (re)install PostGIS 3.3.1 to ensure it's listed > > as the default version and run `postgis_extensions_upgrade()`. > > Ok, I like it, but could there be some problem during re-installation? Life is full of problems, so sure there could be some. I would not expect any though, in this case, if as you say you always used debian stable over the years and always used "apt" to install your packages. The code above will help you find out if this is really the case. > I have 10 db running and I would like to avoid disasters or users panic !! > What is the correct procedure? Safest procedure is database backups. Knowing the state of your 10 databases would also be useful. We have a nice "postgis" script but I dubt debian is packaging it; the script would allow you to do something like: psql -XtAc 'select datname from pg_database' | xargs postgis status Failing to have that script (ask for it to your distribution!) you can obtain something similar with this: psql -XtAc 'select datname from pg_database' | while read DB; do echo "[$DB]"; psql -XtAc 'select postgis_full_version()' $DB; done --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Library function 'pgis_geometry_union_finalfn' was deprecated in PostGIS 3.3.0
On Fri, Sep 30, 2022 at 09:25:38AM +0200, Giuseppe Naponiello wrote: > > POSTGIS="3.3.1 3786b21" [EXTENSION] PGSQL="130" GEOS="3.9.0-CAPI-1.16.2" > PROJ="7.2.1" GDAL="GDAL 3.2.2, released 2021/03/05" LIBXML="2.9.10" > LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from > "3.1.4 ded6c34" need upgrade) RASTER (raster lib from "2.4.9 r0" need > upgrade) [UNPACKAGED!] (raster procs from "2.4.9" need upgrade) Note the "need upgrade" message. > ERROR: A stored procedure tried to use deprecated C function > 'pgis_geometry_union_finalfn' > DETAIL: Library function 'pgis_geometry_union_finalfn' was deprecated in > PostGIS 3.3.0 > HINT: Consider running: SELECT postgis_extensions_upgrade() Great message! > and if I run this select I have: > > ERROR: extension "postgis" has no update path from version "3.1.4" to > version "2.4.9" This means that your current "default" PostGIS version is 2.4.9, so "postgis_extensions_upgrade()" tries to upgrade to that version, but it's actually a DOWNGRADE instead. Also, it looks like your PostgreSQL thinks the PostGIS extension version you have installed is 3.3.1 but you never really upgraded the scripts to that version (they are stuck at 3.1.4). > Any tips? My suggestion is to (re)install PostGIS 3.3.1 to ensure it's listed as the default version and run `postgis_extensions_upgrade()`. Or you can do this manually by editing postgis.control (but re-installing is less error prone). Note there's a feature request issue for postgis_extensions_upgrade() to accept a target version, which would have helped in your case: https://trac.osgeo.org/postgis/ticket/5052 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Corrupted topology when using totopogeom in 3D topology
Hi Alexandre, I can reproduce the problem you reported with version 3.3.0, could you please file a ticket on trac.osgeo.org/postgis with all the detail ? It sounds like a regression. Use component "Topology" please. I confirm using 2D only works fine. Gory details: the problem likely lays in _lwt_MakeRingShell which was recently changed to NOT use GEOS but rather do things internally, to reduce overhead. The internal implementation is NOT dropping the Z as the geos implementation did. Your filing a ticket will greatly help :) --strk; On Fri, Aug 26, 2022 at 11:14:11AM +, Alexandre Silva wrote: > Hello, > > I'm having some trouble creating a 3D topology using totopogeom method, I > don't know if I'm not using the functions correctly or if there's indeed a > bug, so any help would be appreciated. > > I reduced the problem to an example with two lines. > The first line is added with no errors to the topology but the second one > throws this error "Corrupted topology: ring of edge -3 is geometrically > not-closed". > The second line intersects with the first one, but there's no vertex on the > intersection. > I found two workarounds but both of them have some disadvantages in my point > of view. > The first one was to add manually a vertex on the intersection (this involves > someone doing that work manually). > The other one was to add the start and end point of every line using > topogeo_addpoint before calling totopogeom (this involves remembering to do > this every time that I create a topology and I think it's redundant and > overhead for most cases). > > https://imgur.com/a/FgIVyMO - here is the visual of the data for the error > and non-error approach > https://pastebin.com/CR5dNYSZ - here is a script to emulate the error, with > the two workarounds commented > > Not having much knowledge of the c code base, just looking at the code > surrounding the error > (https://www.postgis.net/docs/doxygen/3.0/d6/d03/lwgeom__topo_8c_source.html), > my wild guess is that when it creates the ring of the newly closed area, and > as there is no vertex on the intersection so no snap made, the ring is closed > on 2D dimension but there is a 3D gap that makes the ring not geometrically > closed. My reasoning for this is that the same data with a 2D topology has no > errors and if I reverse the insertion order (there would be a node on the > intersection), it also works. I can also be completely wrong. > > This error was tested on docker image postgis/postgis:14-3.2-alpine with > postgis version: > POSTGIS="3.2.1 0" [EXTENSION] PGSQL="140" GEOS="3.10.2-CAPI-1.16.0" > PROJ="8.2.0" LIBXML="2.9.13" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 > (Internal)" TOPOLOGY > > There's no error in this version (also running in docker): > POSTGIS="3.0.1 ec2a9aa" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 > 27a5e771" SFCGAL="1.3.6" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL > 2.4.0, released 2018/12/14" LIBXML="2.9.4" LIBJSON="0.12.1" > LIBPROTOBUF="1.3.1" WAGYU="0.4.3 (Internal)" TOPOLOGY RASTER > > Thanks, > Alexandre Silva ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Errors with upgrading due to CVE CVE-2022-2625
On Mon, Aug 15, 2022 at 10:50:27AM +0200, Daniel Gustafsson wrote: > > On 12 Aug 2022, at 18:29, Sandro Santilli wrote: > > On Thu, Aug 11, 2022 at 09:44:52PM +0200, Daniel Gustafsson wrote: > >> > >> I've now upgraded to 3.2.2 and the issue remains. When building and > >> running > >> make installcheck-upgrade against a 14.5 postgres cluster it fails with: > >> > >> NOTICE: Packaging extension postgis > >> ERROR: function _postgis_deprecate(text,text,text) is not a member of > >> extension "postgis" > > > Daniel could you please confirm the issue is resolved as of > > commit 9ceb6968ef780bc7d56e4e46ecf5747f95c2e619 in stable-3.2 branch ? > > The upgrade tests seem to work now when building stable-3.2 from a completely > clean tree with a fresh build and install of PostgreSQL 14. Thanks for confirmation. --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Errors with upgrading due to CVE CVE-2022-2625
On Thu, Aug 11, 2022 at 09:44:52PM +0200, Daniel Gustafsson wrote: > > I've now upgraded to 3.2.2 and the issue remains. When building and running > make installcheck-upgrade against a 14.5 postgres cluster it fails with: > > NOTICE: Packaging extension postgis > ERROR: function _postgis_deprecate(text,text,text) is not a member of > extension "postgis" > DETAIL: An extension is not allowed to replace an object that it does not > own. > CONTEXT: SQL statement "CREATE EXTENSION postgis SCHEMA public VERSION > unpackaged;ALTER EXTENSION postgis UPDATE TO "3.2.2"" Daniel could you please confirm the issue is resolved as of commit 9ceb6968ef780bc7d56e4e46ecf5747f95c2e619 in stable-3.2 branch ? Thanks for reporting this! --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Topology and grade separation.
On Wed, Jul 20, 2022 at 06:00:05PM +1000, Simon SPDBA Greener wrote: > I could run up a quick test but does PostGIS topology have a mechanism for > handling this situation? How does it handle linestrings defined as XYZ? PostGIS Topology *forces* any planar intersection to generate a node. There's no way to represent a river not intersecting a bridge over it. Thee only handling of Z is to carry it around and interpolate it when creating new intersections: it's a 2.5D topology. What you describe may be better implemented with pgRouting. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help with Topology Triggers sought
On Fri, Jul 08, 2022 at 02:50:13PM +1000, Simon Greener wrote: > To handle a change should I first delete the existing topogeom > using clearTopoGeom() which modifies the underlying topology What made you think clearTopoGeomy modifies the underlying topology ? That function ONLY affects TopoGeometry objects, which I usually consider as being one layer ABOVE what I call the (underlying) topology. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help with Topology Triggers sought
On Fri, Jul 08, 2022 at 02:50:13PM +1000, Simon Greener wrote: > QUESTION: If the new linestring that is added to the existing topology > causes an existing underlying edge to change (eg split), > what happens to any other topogeom objects in the table that reference the > edge before it is changed? The TopoGeometry objects composition is updated to reflect the changes in the underlying (primitives) structure so to keep their original aspect (point set) as much as possible (adding a vertex to a line ALWAYS results in a slight movement of the line, being our representation space not infinite). > Should the affected topogeoms be found and updated to reflect any splits to > edges? It's already done by the topology editing function, but not by integrity constraints, so as long as you're adding your linestring with provided function there should be nothing else to do. Specifically such updating will surely happen with: toTopoGeom TopoGeo_addPoint TopoGeo_addLinestring TopoGeo_addPolygon --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] A table of DE-9IE patterns and types of topological relations
On Fri, Apr 15, 2022 at 09:08:09PM +0100, Shaozhong SHI wrote: > This is very interesting. > > However, this needs to be further detailed with a table to explain a > variety of cases and uses. Here you can propose improvements in the workshop: https://gitlab.com/postgis/postgis-workshops/blob/master/postgis-intro/sources/en/de9im.rst Thanks for your time, a fresh pair of eyes always helps improving documentation ! --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Wed, Mar 16, 2022 at 09:20:10AM -0400, Greg Troxel wrote: > Sandro Santilli writes: > > > Another solution proposed was to still keep a single table but NEVER > > automatically upgrade it, providing a function to select "system > > entries" to use for populating the single table, when needed. > > With that, you don't get fixes to system values on upgrade, and I don't > see a way to merge them. You don't get them automatically, but you can refresh to the system values by DELETEing your entries and INSERTing the ones from the system function. In the other solution (the 2 tables) you'd only need to DELETE the shadowing entries you don't need anymore. > Overall, if someone wants a different/extra value then it feels like one > of two cases: > > there is a bug in the postgis data (and a bug in EPSG), and it should > be fixed upstream, and it's workaround until then > > they are doing something local not appropriate for upstream, because > it is a local CRS, or its a wrong thing to counteract some other local > problem as a workaround, etc. > > So it would be extra cool if there were a way to say "print out local > shadowing entries that are not actually different from upstream" so > there's a nice path to recovering from the local entry after the bug is > fixed. In my 2-tables branch I had automatic cleanup of matching entries on upgrade, so you'd always only have different-from-upstream entries in the user (shadowing) table. And those shadowing entries would be reported by postgis_full_version(). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
Thanks Chris for your experience, comment below On Tue, Mar 15, 2022 at 08:27:37PM -0700, Chris Tooley wrote: > so I made sure > I copied the original `proj4text` string and bit the bullet and modified > the table with the following query: > > > update spatial_ref_sys set proj4text = '+proj=aea +lat_1=34 +lat_2=40.5 > +lat_0=0 +lon_0=-120 +x_0=0 +y_0=-400 +ellps=GRS80 > +towgs84=0,0,0,0,0,0,0 +units=m +no_defs' where srid=3310; So what you did was modifying what we call a "system entry" (srid=3310) to fix a (possible) bug you found in PostGIS itself. Now I guess you'll want YOUR version of the proj4text value to survive upgrades. At the moment PostGIS soft upgrades are NOT going to revert your change, but someone considers it a bug: https://trac.osgeo.org/postgis/ticket/5024 Your experience suggests it is NOT necessarely a bug, so a comment in that ticket might be good to have too. Still, PostGIS *hard* upgrades (implying dump/restore) will instead get rid of your updates because srid=3310 is "BETWEEN 3174 AND 3791" as found in `extcondition` column of pg_extension for extname='postgis' and thus the record will NOT be included in the dump. This discrepancy between hard and soft upgrades is what I'd like to see fixed with a new simplified method of dealing with upgrades. The solution I suggested was to keep separate tables for "system entries" and "user entries", so that "user entries" would always "shadow" "system entries" (allowing overrides) and would always be carried between upgrades. Another solution proposed was to still keep a single table but NEVER automatically upgrade it, providing a function to select "system entries" to use for populating the single table, when needed. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] source and target vertex for multiple edges
On Sun, Feb 20, 2022 at 11:08:49AM -0500, Travis Kirstine wrote: > Hi all I may have a relatively simple question but I don't have much > experience with routing. I have a road network with topology built and I'd > like to find the source and target ids from multiple edges. For example: PostGIS Topology is for planar topology, not network topology. There's a separate ISO standard for Network Topology which is currently not implemented in PostGIS. Willing to contribute an implementation ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Calculate accumulative bearing given a zig-zagging line
On Thu, Feb 17, 2022 at 08:21:24AM +, Shaozhong SHI wrote: > Is there a function for calculating accumulative bearing given a > zig-zagging line? > > If we set a segment to travel towards north and its bearing is 0. Next > segment may zig-zag towards 10 degrees. The following one may zig-zag > towards -5 degree. > > Calculating accumulative bearing may assist us to check whether a line does > a U-turn. If accumulative bearing is greater than 180, it is a U-turn. > > Is there such function available? You can use ST_Azimuth, some normalization routine to turn the radians into a signed value and the sum aggregate to "accumulate" it. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis 3.0.3 extension for AWS RDS Postgres 13.3
On Mon, Feb 14, 2022 at 03:33:29PM -0500, Regina Obe wrote: > the workaround is below, but not sure it will work on AWS > RDS as updating system tables might be off limits. > > UPDATE pg_extension SET extversion = 'ANY' > WHERE extname = 'postgis'; Allan: please let us know if AWS RDS allows or not the above UPDATE, as we are planning to use that construct for the postgis_extensions_upgrade() function in PostGIS-3.3.0 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Thu, Feb 10, 2022 at 09:10:38AM -0600, Jim Klassen wrote: > > How do other people handle conflicting srids? That's a good question. For sure at the time PostGIS doesn't give official advice about this. Current state of things is: - PostGIS upgrade via EXTENSION won't touch spatial_ref_sys table - PostGIS upgrade via loading spatial_ref_sys.sql will perform a sequence of INSERT in a few transaction, any transaction attempting to INSERT an already existing SRID will fail and result in no insert at all from that batch - PostGIS upgrades via dump/postgis_restore will DISCARD entries found in dump which are already provided by the table found in the target database. This whole thread is to shape a general unified way to handle those conflicting srids. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Wed, Feb 09, 2022 at 01:28:13PM +, Robert Coup wrote:
> On Fri, 4 Feb 2022 at 17:05, Sandro Santilli wrote:
> > - Do you ever update the table with entries from newer
> > spatial_ref_sys.sql ?
>
> Yes. We keep postgis' spatial_ref_sys synchronised with the Proj DB version
> we're running as best we can to make life simpler.
Did you implement your custom scripts to do so ?
> If we're moving things:
>
> 1. is spatial_ref_sys redundant for anything non-custom? Proj has a full
> database now, why does PostGIS need it's own?
I think if we dropped support for older libproj (< 6.1) we could say all
columns but srid,auth_name,auth_srid would be redundant for non-custom records.
> 2. proj4text should disappear altogether too. If people have existing
> proj4text they want to use... `projinfo -o WKT2:2019 '{my-proj4text}'`
Do you think exposing a PostGIS function for doing that would be
useful ?
> 3. Since CUSTOM:4326 and EPSG:4326 clash - the PK is the number; and
> SRID-is-integer is part of SF-SQL (ie: that's not going to change anytime
> soon) could we at least help people put their custom CRS in the right
> place? Is there a defined custom number range we could use to initialise a
> sequence?
PostGIS SRID is not necessarely the auth_srid, so those two are disjoint.
Helping people put their custom CRS in the right place is the goal of
this thread. There's no officially defined custom number range, just a
proposal from 2012 which didn't get much attention:
https://lists.osgeo.org/pipermail/postgis-devel/2012-February/018440.html
The problem with that proposal is that ranges were defined base on the
state of packaged entries AT THAT TIME. My concern is that we cannot
really predict how bigger the "system entries" may get. For instance,
2315 ESRI entries we added at some point (I think after 2012).
I guess 900,913 "shall be enough for anybody" but you never know, right ?
> I think since Proj has gotten a lot smarter since v4, PostGIS should tend
> towards helping people use it (supporting pipelines, using it's
> DB/transform grids/etc) rather than adding additional abstractions over the
> top.
I agree supporting pipelines will be a good addition. We still want to
map a geometry to its CRS though, which is what spatial_ref_sys table
(or view) is about, don't we ?
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Thu, Feb 10, 2022 at 10:43:29AM -0800, Paul Ramsey wrote: > > On Feb 10, 2022, at 7:10 AM, Jim Klassen wrote: > > How do other people handle conflicting srids? > > My guess is that it's pretty rare, since most people who have defined > custom ranges for operational use have done what you did, and looked at > the ranges in use and given them a wide berth. I think our CI machinery actually revealed we introduced conflicting SRIDs outselves between PostGIS version (I think they were about 73 records with some difference in some of the columns between 2.4 and 2.5). --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Wed, Feb 09, 2022 at 09:55:14AM -0600, Jim Klassen wrote: > I always thought it > was cumbersome that I had to maintain the custom coordinate system in > so many places (PROJ, PostGIS, older versions of GDAL, older versions > of QGIS) when most of them are reaching back to PROJ to do the actual > reprojection anyway. This is a very good point. Starting with libproj6 (I think) PostGIS will only need to pass auth_name/auth_srid to proj so you would at least not need the extra srtext and proj4text values in your spatial_ref_sys records. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
On Fri, Feb 04, 2022 at 04:23:48PM -0600, Jim Klassen wrote: > We've been starting at 20 and using EPSG I shall note 200,000 is also not in the "user range" as proposed in https://lists.osgeo.org/pipermail/postgis-devel/2012-February/018440.html The official PostGIS Manual doesn't currently advertise what ranges should user custom entries be in, so I think it's tipical for users to pick whatever range they guess will remain available over time https://postgis.net/docs/using_postgis_dbmanagement.html#user-spatial-ref-sys Would you be disappointed/surprised by a PostGIS upgrade replacing your custom entries above 20 with some new "official" entry ? What strategy did you set in place to prevent this ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do you use spatial_ref_sys ?
On Fri, Feb 04, 2022 at 06:56:58PM +, Moen, Paul T. wrote: > Sorry, we insert into the spatial_ref_sys table not the spatial_ref_sys.sql > file. > > We assign the SRIDs starting at 90. We have been setting the auth_name > to 'EPSG' and the auth_srid to the same value as the srid. > > Example. > > INSERT INTO spatial_ref_sys(srid, auth_name, auth_srid, srtext, proj4text) > VALUES (6933, 'EPSG', 6933, '', '+proj=cea +lon_0=-100.5 +lat_ts=47.5 > +x_0=200 +y_0=0 +ellps=WGS84 +towgs84=0,0,0,0,0,0,0 +to_meter=0.3048 > +no_defs'); Is the above an actual custom entry of yours ? Because the SRID is NOT higher than 90 as you mentioned. What version of libproj are you using ? Are you aware that given a new enough libproj (>= 6.1) the proj4text in your record might not be used at all ? In the spatial_ref_sys.sql file in current PostGIS 3.3 the EPSG/6933 entry has a different proj4text: +proj=cea +lon_0=0 +lat_ts=30 +x_0=0 +y_0=0 +datum=WGS84 +units=m +no_defs You said you also keep the entries installed by spatial_ref_sys.sql, does it mean at the time you issued the above example INSERT your PostGIS version did NOT include an entry for SRID=6933? --strk; > > On 2/4/22, 12:04 PM, "postgis-users on behalf of Sandro Santilli" > wrote: > > * CAUTION: This email originated from an outside source. Do not click > links or open attachments unless you know they are safe. * > > Thanks for answering Paul, more question inline > > On Fri, Feb 04, 2022 at 05:21:23PM +, Moen, Paul T. wrote: > > > We have 54 custom entries that we store in spatial_ref_sys.sql. > > Do you mean in the "spatial_ref_sys" PostgreSQL table or in the > "spatial_ref_sys.sql" file ? > How do you decide what SRIDs to assign to your custom entries ? > What auth_name and auth_srid do you use ? > > >> - Do you also keep the entries installed by spatial_ref_sys.sql ? > > > > Yes. > > > >> - Do you ever update the table with entries from newer > >> spatial_ref_sys.sql ? > > > > No. > > Would you expect (or like) PostGIS upgrade to also upgrade > thse system entries ? > > How do you upgrade PostGIS ? > > > --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Help shaping the future: how do you use spatial_ref_sys ?
Thanks for answering Paul, more question inline On Fri, Feb 04, 2022 at 05:21:23PM +, Moen, Paul T. wrote: > We have 54 custom entries that we store in spatial_ref_sys.sql. Do you mean in the "spatial_ref_sys" PostgreSQL table or in the "spatial_ref_sys.sql" file ? How do you decide what SRIDs to assign to your custom entries ? What auth_name and auth_srid do you use ? >> - Do you also keep the entries installed by spatial_ref_sys.sql ? > > Yes. > >> - Do you ever update the table with entries from newer >> spatial_ref_sys.sql ? > > No. Would you expect (or like) PostGIS upgrade to also upgrade thse system entries ? How do you upgrade PostGIS ? --strk; Libre GIS consultant/developer https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Help shaping the future: how do your use of spatial_ref_sys ?
Following earlier may from Regina, I'm writing this as a user inquiry, to get help in shaping the future of PostGIS handling of custom CRSs. The question I have for you is: do you keep your custom CRSs in the spatial_ref_sys table ? If so: - How many ? - Do you also keep the entries installed by spatial_ref_sys.sql ? - Do you ever update the table with entries from newer spatial_ref_sys.sql ? Background: The EXTENSION based installs are known to NEVER automatically update the spatial_ref_sys table while the spatial_ref_sys.sql file installed on PostgreSQL contrib dir for each PostGIS version can currently be used to restore the "PostGIS-Standard" entries. I've been working on a change which would allow keeping user entries separated from system entries, with system entries being always upgraded on PostGIS upgrade (automatically when using EXTENSION based installs and manually when using SCRIPT based installs). Your experiences will be useful to decide whether or not to merge such change in for the next PostGIS version. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] hard upgrade from 1.5
On Tue, Jan 11, 2022 at 05:45:16PM +, Nathan Wagner wrote: > 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. You are right: "create extension from unpackaged" is still a form of "soft upgrade" so only works when "soft upgrade" is possible (ie: when data doesn't need to be dumped *before* the upgrade). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] hard upgrade from 1.5
On Mon, Jan 10, 2022 at 10:18:34AM -0800, Paul Ramsey wrote: > > On Jan 10, 2022, at 10:01 AM, Nathan Wagner 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 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] topology.GetFaceByPoint users around ?
I'm rewriting core PostGIS Topology function to speedup topology population, with a focus on finding faces _properly_ containing a point (having the point on the interior, not on the boundary). We currently have a topology.GetFaceByPoint function which I'm tempted to re-use as a signature for the improved function, but that function also allows finding faces having the query point on the boundary, which is NOT what I want. Now the question is: would people really need the current behavior of that function and if so, why ? I fail to understand in which case would anyone want to deal with a tolerance for finding faces, unless they accept multiple faces as answer (a point on a face boundary is ambiguous as to which face should be returned). So: do you use that function ? For what task ? Reference: https://trac.osgeo.org/postgis/ticket/4933 --strk; () Free GIS & Flash consultant/developer /\ https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Error: "Precision reduction requires GEOS-3.9 or higher"
On Sun, Apr 18, 2021 at 02:42:14PM +0200, Marco Boeringa wrote: > Hi Sandro, > > Thanks for the answer, but this just raises a question, why was the > official PostGIS 3.1.1 as available in the > > "http://apt.postgresql.org/pub/repos/apt/focal-pgdg main" > > repository, not compiled with GEOS 3.9.0, if it is supposed to be able > to take advantage of new GEOS 3.9.0 functionality? You should ask the packagers. This is all a community effort, and you can even make a difference by contributing your time to do those builds yourself I think :) > Or would a PostGIS 3.1.1 compiled against GEOS 3.9.0 cause issues on a > default Ubuntu Focal install with just GEOS 3.8.0, due to > incompatibilities? To my knowledge GEOS upgrade never introduce incompatibilities, unless there are packages that are using the C++ API of GEOS (which we've campaigned enough to avoid) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Error: "Precision reduction requires GEOS-3.9 or higher"
On Sat, Apr 17, 2021 at 09:01:33AM +0200, Marco Boeringa wrote: > Before I upgraded libgeos from the ubuntugis repository, > "PostGIS_Full_Version()" showed GEOS 3.8.0. > > So how do I potentially solve this? Or is this upgrade of GEOS only possible > by compiling PostGIS from source yourself (which I would definitely like to > avoid)? No way out of compiling PostGIS from source. It could be done by you or by some third party who could have made a package for your OS available. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis 3.1.0 st_makevalid crashes the PostgreSQL Server
On Mon, Jan 25, 2021 at 10:19:42AM +0300, Fatih Sazan wrote: > When I run my code running on PostGIS version 2.5, I don't get an error but > when I run it on 3.1 it the server gives an error and enters the recovery > mode every time. > Operating System: CentOS Linux 8 > CPE OS Name: cpe:/o:centos:centos:8 > Kernel: Linux 4.18.0-193.28.1.el8_2.x86_64 > Architecture: x86-64 > > postgresql-13 > extension : postgis vers. 3.1.0 This should be fixed by upgrading GEOS. What version of GEOS are you using ? It should be reported by: SELECT postgis_full_version(); --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Contradiction between ST_IsValid() and ST_IsValidReason() ?
On Wed, Dec 23, 2020 at 05:38:23PM +0100, Andreas Neumann wrote: > Hm, seems like it is still not good in master. > > As soon as I add "ST_MakeValid()" in the same SQL query, it goes back to > "weird" mode. Then there must be another memory issue. It would help to have a sample of geoms which can still reproduce the problem. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Contradiction between ST_IsValid() and ST_IsValidReason() ?
On Wed, Dec 23, 2020 at 04:51:07PM +0100, Andreas Neumann wrote: > Hi Strk and Nicolas, > > Thanks for your replies. > > I'm using GEOS 3.9, PostgreSQL 13.1 and Postgis 3.1 (self-compiled). > > The situation is really weird: if I run the same SQL query a second > time, the contradiction is gone and all geometries are labeled as > invalid by ST_IsValidReason(). If I run it many times, the results > change even more, suddenly, the invalid geometries are not even detected > correctly by ST_IsValid(). > > @strk: these invalid geometries contain "NaN" coordinates. > > I will open an issue and submit the geometries. Can you first try to rebuild PostGIS from current master branch ? I've fixed a memory issue somewhat related to "NaN" coordinates --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Contradiction between ST_IsValid() and ST_IsValidReason() ?
On Wed, Dec 23, 2020 at 11:20:07AM +0100, Andreas Neumann wrote: > Hi, > > I have the strange situation that ST_IsValid() claims a geometry is not > valid, but then ST_IsValidReason() says 'Valid Geometry' > > Here is my query: > > SELECT > t_id, > ST_AsText(ST_MakeValid(geometrie)) AS geom_repaired, > ST_AsText(geometrie) AS geom_orig, > ST_IsValidReason(geometrie) > FROM arp_npl.erschlssngsplnung_erschliessung_linienobjekt >WHERE ST_IsValid(geometrie) = False; > > And here parts of the output: > [image: image.png] > > Why is this contradiction - and which one is right? ST_IsValid() or > ST_IsValidReason() ? Sounds like a bug. ST_IsValid should print the invalidity in a NOTICE, it looks like ST_IsValidReason is failing to check that kind of invalidity. Would be useful to file a ticket and attach the offending geometry (best if reduced as much as possible). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Topology creation performance
On Fri, Nov 20, 2020 at 04:55:06PM +, Alexandre Silva wrote: > The slower ordering method results in the whole area being added to the > topology in a layered style (rivers, roads, rural areas, etc.) and after the > first one, there are already some faces with a large area, and the > performance starts dropping rapidly. My suspicion is that this faces are the > culprit of this slowing down. > In a first attempt to fix it I tried deleting the faces after each line was > added, and it improved a little at the start but by the second half it's not > much of a difference. Most likely. I would try splitting these big faces into smaller pieces. A way to do so would be inserting abitrary lines cutting the plane into a grid. This would also split lines, further reducing working set for each further insertion. You could add these lines upfront or during the load, to see how they affect the loading (it's good that you use multiple transactions). What version of PostGIS are you using ? What GEOS version ? > In another attempt, I used the AddEdge method, and it processed all the > lines in about 15 minutes. Nice timing! > Even though this needs the polygonize method to be run afterwards, from what > I could discover it seems that every edge is only processed once, instead of > multiple times. (In a older post > (https://postgis-users.postgis.refractions.narkive.com/Xg3wV8V2/postgis-topology-performance) > this approach seems to be the way to go). The major disadvantage of this > method is that every line needs to be split beforehand, so the AddEdge > doesn't throw an error, but using any other of the existing methods > (toTopogeom and TopoGeo_AddLineString) it doesn't seems to be a way to get > the performance that I get with AddEdge. > > Are my assumptions are correct? And is the AddEdge the way to go or is there > another way? Note that the Polygonize function will NOT properly setup edge-linking (next_right_edge, next_left_edge) so you'd still not end up with a valid topology when only using AddEdge + Polygonize. One improvement that was implemented in spatialite was to allow for TopoGeo_addLinestring to NOT detect the creation of new faces while still doing edge-linking. It still implied constructing an invalid topology but the Polgonize step would then make it valid. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Upgrade Postgres 9.6 to Postgres 13 and Postgis 2.5 to Postgis 3.0 procs need upgrading?
On Mon, Oct 12, 2020 at 02:48:38PM -0700, Willem Buitendyk wrote: > I recently upgraded Postgres and postgis to 13 and 3 respectively. > Everything seems to be working but when I run: > > SELECT PostGIS_Full_Version(); > > I’m seeing this: POSTGIS="3.0.2 2fb2a18" [EXTENSION] PGSQL="96" (procs need > upgrade for use with PostgreSQL "130”) > > I haven’t been able to find a solution for upgrading the procs? I don’t even > know what procs are referring to. SELECT postgis_extensions_upgrade(); --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Compile error during build of 3.0.2...
On Fri, Oct 09, 2020 at 10:16:43AM -0400, Brad Dworak wrote: > gserialized_gist.c:12:10: fatal error: postgres.h: No such file or directory [...] > I'm running Ubuntu 18.04 and have > PostGreSQL 11, 12, and 13 (specifically postgresql-13-postgis-3) installed. You need the development packages: sudo apt-get install postgresql-server-dev-13 --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Find LineString segment index containing closest point?
On Tue, Aug 04, 2020 at 08:46:39AM -0700, Martin Davis wrote: > The ST_ClosestPoint function (and the Linear Referencing functions) do have > the segment index information internally. It would be nice if there was an > extended version of ST_ClosestPoint which returned a record with both the > closest point and the index of the segment containing it. Or perhaps an > extended Linear Referencing function which returns the point, the segment > index, and the fractional distance along the line. I agree such a function would be great to have. I filed a ticket to request that a long time ago: https://trac.osgeo.org/postgis/ticket/892 It's looking for fundings, anyone interested ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] remove duplicate line segments
On Fri, Jun 12, 2020 at 09:18:22AM +, paul.m...@lfv.se wrote:
> Hi,
> If someone could help me with this, I would be grateful.
> I've converted a polygon layer to a line layer. Sometimes the lines segments
> are a duplicate of another line segment (from an adjacent polygon).
> I would like to delete the duplicate lines and only store the one of all
> duplicate line segments. It doesn't matter which one I store.
> I've tried to store the line layer as a topology and erase the edges that are
> the same, but I don't have enough knowledge of topologies. To be honest not
> much knowledge at all.
> I've tried this:
> DELETE FROM topo1.edge_data WHERE edge_id NOT IN (SELECT MAX(edge_id) FROM
> topo1.edge_data GROUP BY geom)
If you're using PostGIS Topology you especially if you don't know what
you're doing ("not much knownedge of topologies") you sould avoid
running direct DELETE on those tables. Rather, use the
topology editing functions:
https://postgis.net/docs/Topology.html#Topology_Editing
For deleting edges you may want to use ST_RemEdgeModFace or
ST_RemEdgeNewFaces, but mind you: if the topology is valid,
what you call "duplicated lines" are not really duplicated,
or they would not be stored twice.
So, first of all make sure your topology is valid:
https://postgis.net/docs/ValidateTopology.html
Then, if it is valid, you may be looking at edges which are
very close togheter but are still different, thus creating
tiny tiny faces that you'll want to remove by removing one
of those close-by edges. Tiny faces you may find using
ST_Area(ST_GetFaceGeometry(...))
> If someone could tell me how to delete "common" line segments I would be very
> happy.
You may find this article useful:
https://strk.kbt.io/blog/2011/11/21/topology-cleaning-with-postgis/
--strk;
() Free GIS & Flash consultant/developer
/\ https://strk.kbt.io/services.html
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] where to report bugs?
On Sat, Jun 06, 2020 at 07:33:59PM +0300, Darafei "Komяpa" Praliaskouski wrote: > Best way to get it fixed is to stomp it into a Github pull request with > minimized version of your case in regress suite. There are different point of views on the matter. Best way to get it fixed is surely fixing it yourself for sure. Once you have a fix there are multiple options to send a pull-request, including sending [git patches via email](https://git-send-email.io) If it's a backtrace you're after this is a great lecture: http://blog.cleverelephant.ca/2008/08/valgrinding-postgis.html --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Broken link
On Fri, Mar 27, 2020 at 03:30:09PM +0100, Paolo Cavallini wrote: > > > Il 27/03/20 14:41, Sandro Santilli ha scritto: > > On Fri, Mar 27, 2020 at 11:22:17AM +0100, Paolo Cavallini wrote: > >> Hi all, > >> sorry for the noise, probably not the best cahnnel to report this, but > >> in https://postgis.net/support/ there is a broken link: > >> http://refcardz.dzone.com/refcardz/essential-postgis > > > > Here's how you do: https://trac.osgeo.org/postgis/ticket/4653#ticket > > (thanks for the report) > > oh, I thought the bugtracker was for executables, not for website One ticket manager for all. If you find the right link you may also open a pull request with the online editor: https://git.osgeo.org/gitea/postgis/postgis.net/src/branch/master/_content/pages/documentation.html --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Broken link
On Fri, Mar 27, 2020 at 11:22:17AM +0100, Paolo Cavallini wrote: > Hi all, > sorry for the noise, probably not the best cahnnel to report this, but > in https://postgis.net/support/ there is a broken link: > http://refcardz.dzone.com/refcardz/essential-postgis Here's how you do: https://trac.osgeo.org/postgis/ticket/4653#ticket (thanks for the report) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
[ please keep postgis-users in the loop ] On Wed, Mar 04, 2020 at 02:01:37PM +, paul.m...@lfv.se wrote: > Thanks Sandro! > I understand a bit more now, I'm quite new at this. > And I have no idea how to: > --> Instead, you'll want to make sure each and every face is assigned to one > (and only one, if you want to avoid overlaps) final geometry. It's just SQL foo :) Had you considered using toTopoGeom instead of ST_CreateTopoGeo ? The former would automatically create the TopoGeometry objects for you (although it won't prevent the same face being assigned to multiple TopoGeometry objects). But you could do the simplification after creating the topology for the full dataset (which should reduce the odds of overlaps): https://strk.kbt.io/blog/2013/03/08/on-the-fly-simplification-of-topologically-defined-geometries/ --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] topology.TopoGeo_AddLineString performance depends very on order of input lines in some cases.
On Wed, Mar 04, 2020 at 07:04:57AM +, Lars Aksel Opsahl wrote:
> When testing adding millions of linestrings by splitting in around 4000
> content based cells, one cell was using hours for adding a few thousands
> lines , but by changing the order and adding the non-closed line strings
> first I reduced the time to around two minutes from many hours for this
> cell. When running I started from an empty Topology for each cell.
Reading your numbers I see:
- Non-closed first, big first: 8h40m
- Big first (no matter closed/non-closed): 0h02m
- Closed first, big first: 0h02m
Correct me if I'm wrong. I'll include your tests below.
Note that "false" comes before "true" with order by on
boolean (ascending, by default).
> select topology.TopoGeo_AddLineString('test_topo_02',geom,0.01) from
> test_topology_05
> order by is_closed, num_points desc;
> Time: 31234035.504 ms (08:40:34.036)
The above adds non-closed first, and each set (non-closed, then
closed) it will add the lines with the most points first.
> select topology.TopoGeo_AddLineString('test_topo_01',geom,0.01) from
> test_topology_05
> order by num_points desc;
> Time: 164403.033 ms (02:44.403)
The above adds with most dense lines first, no matter closed/unclosed.
> select topology.TopoGeo_AddLineString('test_topo_03',geom,0.01) from
> test_topology_05
> order by is_closed desc, num_points desc;
> Time: 131125.236 ms (02:11.125)
The above adds with closed first, and each set (closed, then
non-closed) it will add the lines with most points first.
So, I'd derive from the above that "big first" makes a difference, no
matter closed/non-closed (although I'd expect the contrary, to be
honest).
> But another strange thing was that time could vary a lot for the same
> dataset and order. In this test I did not care about closed or not but
> only line length.
Yes, I'd expect this.
> select topology.TopoGeo_AddLineString('test_topo_01',geom,0.01) from
> test_topology_02 order by num_points desc;
> Time: 57915.860 ms (00:57.916)
>
> select topology.TopoGeo_AddLineString('test_topo_02',geom,0.01) from
> test_topology_02 order by num_points asc;
> Time: 42424.087 ms (00:42.424)
>
> select topology.TopoGeo_AddLineString('test_topo_03',geom,0.01) from
> test_topology_02 order by id asc;
> Time: 738242.464 ms (12:18.242)
> Time: 1197808.563 ms (19:57.809)
> Time: 1003501.683 ms (16:43.502)
>
> The file is here
> https://github.com/larsop/resolve-overlap-and-gap/blob/add_postgis_topology_TopoGeo_addLinestring_thred_grid/src/test/sql/regress/test_topology_02.dump.gz
> .
The cost of each addition is the cost of:
1. Finding all edges and nodes intersecting the new line
2. Noding the new line with existing nodes/edges, split accordingly
3. For each resulting split-segment:
3.1. Finding all edges and nodes intersecting the segment
(to bail out if found an intersecting one)
3.2. Finding edge rings on both sides of the edge
(to determine if a new face is created)
Roughly, the above is the algorithm. Can probably be improved in
some steps, but you can see how the order in which things are
added matters. In general, reducing (by splitting) the length
of lines and (by adding connecting edges) the area of
faces should speed things up. Assuming indexes are correctly used
(is autovacuum on?).
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Wed, Mar 04, 2020 at 07:11:30AM +, paul.m...@lfv.se wrote:
> In both topo1- and topo2.face shows a box where Egypt is located, as for all
> other countries (type of bounding box?).
> In both topo1- and topo2.edge_data shows all countries as well as Egypt.
Ok, so the topology was fully constructed, nothing missing so far.
Additional checks you may run:
1. Visually inspect all face geometries (load them in a new table, or
view, or use QGIS TopologyViewer which does it for you).
[ NOTE: it takes ST_GetFaceGeometry to see the geoms, otherwise,
as you found out, you only see the bounding box ]
2. Run: select * from topology.validatetopology('topo1');
> Egypt is however intersecting an island in topo2.
"Egypt", as you call it, does not exist anymore at this stage.
It's only in your brain. Same goes for what you call "an island".
What exists in this stage is a set of connected edges, nodes and faces.
There's no overlap between faces, no intersection other than sharing edges.
> The island is formed like a banana in topo1 (not intersecting the
> Egypt polygon) and like a triangle in topo2, due to the simplification
> (5000 m). Therefore a headland in the Egypt-polygon is intersecting the
> island in topo2.edge_data.
What you are describing is that a face in the resulting topology is
taking the space which was occupied by both the original Egypt polygon
and the original Island polygon.
Since neither Egypt nor Island exist, it's up to you to decide how
to make them up: do you want to assign that face to Egypt or to
Island? Or you want to further split that face into two, to assign
a slice of it to each of the "features" you want to build ?
The TopoGeometry object is what allows you to build a single entity
out of a set of topology primitives (faces, for example), and is what
you were using in your later query.
> I think that's why I can't create a polygon geometry for Egypt. It is
> the same for Finland (also has an island that becomes intersecting the
> main polygon of Finland in topo2).
You CAN create a geometry, both for Egypt and for Findland, you just
have to decide which faces will take part of those definitions.
> WITH simple_face AS (
>SELECT topology.st_getFaceGeometry('topo2', face_id) AS the_geom
>FROM topo2.face
>WHERE face_id > 0
> )
> UPDATE "simple_countries" d set geom = sf.the_geom
> FROM simple_face sf
> WHERE st_intersects(d.geom, sf.the_geom)
> AND st_area(st_intersection(sf.the_geom, d.geom))/st_area(sf.the_geom) > 0.5;
In the above query you are:
1. extracting the geometries of _all_ faces
2. updating "simple_countries" by assigning to its
"geom" column a _single_ face from the set extracted
in 1, even if multiple faces intersect.
If more than one face interests Egypt, you'll only get one, which
explains why you're missing the whole polygon.
Instead, you'll want to make sure each and every face is assigned
to one (and only one, if you want to avoid overlaps) final geometry.
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Tue, Mar 03, 2020 at 02:30:11PM +, paul.m...@lfv.se wrote:
> Hi,
> I'm not sure of what you mean, Strk.
> I create a topology (topo1) with all geometries from a polygon layer.
> I can see the island and the Egypt polygon in the edge_data, they are not
> intersecting.
>
> Then I create a new topology (topo2) with:
> select topology.ST_CreateTopoGeo('topo2', the_geom) from (
>select ST_Collect(st_simplifyPreserveTopology(geom, 5000)) as the_geom
>from topo1.edge_data) as foo;
Is Egypt still there when you look at topology faces ?
> Here in the topo2.edge.data I can see that the island and the polygon of
> Egypt is intersecting due to the simplifying.
> And when I create a new polygon layer from this edge_data the Egypt polygon
> is left out.
>
> This is done with:
> with simple_face as ( select topology.st_getFaceGeometry('topo2', face_id) as
> the_geom
>from topo2.face
>where face_id > 0 ) update newlayer d set geom = sf.the_geom
> from simple_face sf
> where st_intersects(d.geom, sf.the_geom)
> st_area(st_intersection(sf.the_geom, d.geom))/st_area(sf.the_geom) > 0.5";
I suspect you're just filtering out too many faces, due
to small intersection area..
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] st_union
On Tue, Mar 03, 2020 at 10:12:05AM +, paul.m...@lfv.se wrote: > There was no difference between st_union() and st_unaryunion() when merging > all adjacent polygons to larger polygons. > The st_buffer() method is 3.5 times faster. The reason why ST_Buffer is faster is because it builds the topology of the geometry only once, while ST_Union builds it for each pair of geometries taken in exam. The ST_UnaryUnion version simply chooses which pair of geometries to union in order for the operation to be as effective as possible (drop more edges). There was a reason why we decided NOT to use GEOS Buffer internally for UnaryUnion, probably had to do with robustness, but I forgot the details. Maybe Martin can help here ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Fri, Feb 28, 2020 at 10:35:48AM +, paul.m...@lfv.se wrote: > Thanks, Sandro! > It worked with your LOOP! Great! > The problem I can see on the result (I'm simplifying country polygons) is > that the large polygon of Finland, Germany, Italy and Egypt is missing. Did you get the WARNING for those ? What I suggested was to change the code to note down which geometries did not work on first go, to try at inserting them separately. > I've tried to change the different tolerances (Simplify tolerance and your > loop snap-tolerance). If I use simplifying tolerance 2500 m the missing > polygons are there, but not when using 5000 m. The countries included in the > original layer is from Sweden, Norway and Finland down to Jordan, Egypt, > Libya and Tunisia. > Ref sys =WGS 84_UTM-33N So you already found a way to get them all in ? Tweak that loop to reduce tolerance when needed. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] st_union
On Mon, Mar 02, 2020 at 02:25:52PM +, paul.m...@lfv.se wrote:
> Hi list,
> I would like to do this:
> CREATE TABLE public."Areas_union" AS select
> (st_dump(st_union("the_geom"))).geom from "Areas";
>
> But it takes such a long time to execute, is there a smarter way to do it?
Try this:
CREATE TABLE public."Areas_union" AS select
(st_dump(st_unaryunion(st_collect("the_geom".geom from "Areas";
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Fri, Feb 28, 2020 at 12:29:49PM +, paul.m...@lfv.se wrote: > Hi again, > I can see in the edge data of topo2 that the main polygon of Egypt intersects > with an island next to the main polygon. If I delete the island in the > original layer before running the SQL sequence the main polygon of Egypt is > included in the result layer. Is there any solution of this problem? The above (being unable to include Egypt when island is added) still depends on the tolerance value, right ? There's probably a spatial configuration between those 2 polygons that triggers an excessive snapping with high tolerances. Keep your tolerances low! Or, insert the island in the topology as the last thing, and see where the error ends up being (the whole point of building the topology incrementally rather than in a single step is to be able to deal with each case separately, and spot what's special about it) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets
On Wed, Feb 26, 2020 at 11:10:36AM +, Lars Aksel Opsahl wrote: > Yes I can do that but then I have to remove this extra lines when done. > This should by able to do by checking if two faces that share edge belongs > the same original polygon. Yes, it is a way. > If checkout this code "git fetch origin pull/28/head:batch-topo2" is any > problem for me to compile this code and test it ? The code in that branch is broken, as shown by the bot building it, unfortunately. > Or do I have to do a lot changes in my own code to test it ? Yes, the code would also need to be changed, and won't be performant because that branch only touches ST_CreateTopoGeo, which needs to do everything at once (loading all in memory). --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets
On Wed, Feb 26, 2020 at 09:59:52AM +, Lars Aksel Opsahl wrote: > * The output from pg_stat_statements is below, The only strange thing I > see here is that the query “SELECT edge_id,geom FROM > "test_topo_ar5".edge_data WHERE edge_id IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,” > has length of 599090 It sounds like a big "edge ring", that is a face bound by many edges. You should be able to speed things up by cutting that big face into smaller faces. Since you already made a grid you may start by adding edges of that grid in the topology. Note that every time you add an edge with TopoGeo_addLinestring, the code tries to determine if a new face is created by walking on that "edge ring" (which is done using that query you mention). The Tuscany Region had funded, for spatialite, code to prevent that step while adding linestrings, for doing it at the very end, when all edges are in place. Doing so somewhat speed things up, because at the time in which faces are computed, all edges are in place and thus there each "edge ring" is computed exactly once instead of per-every-edge added. The work was never ported back to PostGIS because it would require defining a policy to leave a topology in invalid state (with faces being formed but not marked as such) and I personally didn't find the time to work on that. An initial work to implement this in ST_CreateTopoGeo (which would not have the problem of leaving the topology in an invalid state, given it all happens in a single transaction) is here: https://git.osgeo.org/gitea/postgis/postgis/pulls/28 --strk; () Free GIS & Flash consultant/developer /\ https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Tue, Feb 25, 2020 at 01:22:39PM +, paul.m...@lfv.se wrote:
> Hi,
> Where do you mean I can play with the tolerance?
In TopoGeo_addLinestring
https://postgis.net/docs/manual-3.1/TopoGeo_AddLineString.html
> This is what I have done before the ST_createTopoGeo
> SELECT topology.CreateTopology('topo1', 4326)";
> SELECT topology.ST_CreateTopoGeo('topo1', ST_Collect(geom)) from
> "countries_first";
> Btw, I'm intending to simplify later on in my SQL command list.
You could do something like this:
DO $$
DECLARE
rec RECORD;
tol FLOAT8;
BEGIN
tol := 0;
FOR rec in SELECT gid, (ST_Dump(geom)).geom FROM countries_first
LOOP
BEGIN
IF GeometryType(rec.geom) = 'POLYGON' THEN
PERFORM topology.TopoGeo_AddPolygon('topo1', rec.geom, tol);
ELSIF GeometryType(rec.geom) = 'LINESTRING' THEN
PERFORM topology.TopoGeo_AddLinestring('topo1', rec.geom, tol);
ELSIF GeometryType(rec.geom) = 'POINT' THEN
PERFORM topology.TopoGeo_AddPoint('topo1', rec.geom, tol);
END IF;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'For geometry % we got exception % (%)', rec.id,
SQLERRM, SQLSTATE;
END;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
You can tweak the above to do something different rather than raising
a WARNING (for example store ID of offending geoms in a table).
Then you can look at the offending geometries in isolation, possibly
tweaking the "tol" variable.
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_CreateTopoGeo
On Tue, Feb 25, 2020 at 12:10:07PM +, paul.m...@lfv.se wrote:
>
> Hi,
> I'm running this command:
> SELECT topology.ST_CreateTopoGeo('topo1',ST_Collect("geom")) from
> "countries_first";
>
> and are getting this message:
> ERROR: Corrupted topology: adjacent edges 71 and -72 bind different face (36
> and 0) CONTEXT: SQL-sats: "SELECT topology.ST_AddEdgeModFace(atopology,
> snode_id, enode_id, rec.geom)" PL/pgSQL-funktion
> topology.st_createtopogeo(character varying,geometry) row 151 at PERFORM SQL
> state: XX000
>
> I've tried running ST_Makevalid on "countries_first" (polygon layer) before
> executing ST_CreateTopoGeo
> Is there anyone who has a clue on what is wrong?
It's probably just a robustness issue.
What ST_CreateTopoGeo does, at the moment, is invoking
TopoGeo_AddPoint for each point and TopoGeo_AddLinestring
for each line in your collected input.
You could do the same yourself, to stop on first failure
and see what's wrong. Most of the times playing a bit with
different tolerances can help get past an hard-to-digest
input.
PS: it helps showing output of `postgis_full_version()` when
reporting issues
--strk;
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] pgsql2shp warning "empty table or unknown spatial ref"
On Mon, Feb 10, 2020 at 06:01:40PM -0500, Diego Vargas wrote: > Hello, I am using pgsql2shp and shp2pgsql to dump and load a table with gis > data, and I’m getting the following message: > > "WARNING: Cannot determine spatial reference (empty table or unknown spatial > ref). No prj file will be generated." > > Now, there is no problem at the load since it seems that the the whole table > is loaded without issue. > Yet, I wonder if should I concern about the warning or not at all… > It means pgsql2shp could not determine the SRID of your geometries. Do they have a SRID defined ? See https://postgis.net/docs/ST_SRID.html --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] error: type "raster" does not exist
On Mon, Jan 13, 2020 at 05:54:50PM -0800, john polo wrote: > error: type "raster" does not exist > > Searching for some help, I came across the note from > https://postgis.net/docs/manual-3.0/raster.html [...] > SELECT postgis_full_version(); [...] > POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " > PROJ="Rel. 5.2.0, September 15th, 2018" LIBXML="2.9.9" LIBJSON="0.12" > LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" It looks like you did not load postgis_raster extension (required since version 3.0.0 of PostGIS: CREATE EXTENSION postgis_raster; Happy mapping ! --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PSC Vote: request for opinion Edit this on Github , Edit this on Gitea
On Thu, Nov 21, 2019 at 07:26:47PM +0100, Sandro Santilli wrote: > That said, -0 for the GitHub button, +1 for the other two :) On double-checking, the Gitea repository mirror, being a mirror, does not online-editing (turning it in a non-mirror would require work). The GitLab one does: https://gitlab.com/postgis/postgis-workshops/blob/master/postgis-intro/sources/en/about_data.rst --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PSC Vote: request for opinion Edit this on Github , Edit this on Gitea
On Thu, Nov 21, 2019 at 01:05:23PM -0500, Regina Obe wrote: > Our workshop page is a great learning page > > https://postgis.net/workshops/postgis-intro/ > I'm proposing we put two links > > "Edit this on Github" > "Edit this on Gitea" > > > We can accept pull requests from both places and I think having both links > would satisfy all audiences. I don't like a free software project advertising a proprietary software based service, and that's what an "Edit this on Github" would be. I just created a mirror on gitlab.com as well: https://gitlab.com/postgis/postgis-workshops Add "Edit this on gitlab.com" ? That said, -0 for the GitHub button, +1 for the other two :) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Query postgis GUC variables on a new connection
On Sun, Oct 27, 2019 at 03:52:37PM -0700, Paul Ramsey wrote: > Until the library loads the system doesn’t know about the GUCs. And querying > the GUCs doesn’t for a library load (the system doesn’t having a binding from > GUC to library). Querying a function, on the other hand, forces a load. > If this really annoys you, add postgis_raster.so to the ld_preload config in > postgresql.conf. For the record, you can also use: LOAD '$libdir/postgis_raster-3.so' Directly in the sql session --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Upgrade issues
On Wed, Sep 04, 2019 at 11:20:33AM -0400, Regina Obe wrote: > We allow people to have custom srids – that bit is to prevent our > SRIDs from being backed up. So the only srids backed up are the ones > not in our list.. That's the list coming with new release, right ? I've been calling for an official SRID range for some time now, it would simplify that configuration by A LOT ! > I think now that srid list is autogenerated by one of our scripts. I forget > which It is utils/create_spatial_ref_sys_config_dump.pl --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] fail to upgrade from 2.1.8 to 2.2.2 or 2.3.3
On Thu, Jun 27, 2019 at 06:55:39PM +0200, olivier.lo...@umontpellier.fr wrote: > > > Le 27 juin 2019 à 17:59, Sandro Santilli a écrit : > > > > On Thu, Jun 27, 2019 at 12:46:03PM +0200, olivier.lo...@umontpellier.fr > > wrote: > > > >> at line 248, substring(upgraded from '[0-9]*\.([0-9]*)\.’) cannot be cast > >> to int since it leads ‘’ because of the « SELECT '2.2'::text as upgraded » > >> at line 242. > >> > >> replacing SELECT '2.2'::text as upgraded with SELECT ‘2.2.2'::text as > >> upgraded solves the issue > >> > >> Looks like there is the same problem with any migration scripts from > >> 2.1.xx > >> > >> Maybe a problem of compilation/debian distribution (since I installed > >> postgis using debian packages) ? > > > > I guess we could just pick the first two numbers and drop the final > > '\.' portion of the regexp... What surprises me is that our CI bots > > testing upgrades did not catch this issue. > > yes and what surprises me is that the statement is wrong whatever the > database it is ran against (since the ‘2.2’ is hard coded) I suggest you file a ticket to https://trac.osgeo.org/postgis as you may have found a serious bug. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] fail to upgrade from 2.1.8 to 2.2.2 or 2.3.3
On Thu, Jun 27, 2019 at 12:46:03PM +0200, olivier.lo...@umontpellier.fr wrote: > at line 248, substring(upgraded from '[0-9]*\.([0-9]*)\.’) cannot be cast to > int since it leads ‘’ because of the « SELECT '2.2'::text as upgraded » at > line 242. > > replacing SELECT '2.2'::text as upgraded with SELECT ‘2.2.2'::text as > upgraded solves the issue > > Looks like there is the same problem with any migration scripts from 2.1.xx > > Maybe a problem of compilation/debian distribution (since I installed postgis > using debian packages) ? I guess we could just pick the first two numbers and drop the final '\.' portion of the regexp... What surprises me is that our CI bots testing upgrades did not catch this issue. Maybe it is only coming from your old 2.1.xx version rather than the versions tested by CI (2.1.9) ? Also, you didn't show the output of: SELECT Postgis_full_version() --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] fail to upgrade from 2.1.8 to 2.2.2 or 2.3.3
On Wed, Jun 26, 2019 at 11:20:59AM +0200, olivier.lo...@umontpellier.fr wrote: > Hi, > > On a postgresql 9.3 / postgis 2.1.8 / debian 8.11, I cannot upgrade postgis > to 2.2.2 (or 2.3.3) : > > # ALTER EXTENSION postgis UPDATE TO '2.2.2'; > WARNING: nonstandard use of escape in a string literal > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. [...] > ERROR: invalid input syntax for integer: « » > > Any idea of what happens ? Note that latest PostGIS version in the 2.2 branch is 2.2.8 (released on 2018/11/21) and latest in the 2.3 branch is 2.3.8 (released on 2019/03/11). Also, latest stable is 2.5.2 (released on 2019/03/11) Before moving on, I'd try *those* upgrades. > How can I get more debug information ? > Is it possible to execute the command step-by-step to see where the > underlying script fails ? Not with the "EXTENSION" mechanism, but you could try the script-way, using psql -f which would give you a line number of the failing statement. Please show the output of `SELECT postgis_full_version()" issued on your 2.1.8 install, and beware that a newer release in the 2.1 branch of PostGIS is also available: PostGIS 2.1.9 (2017/09/19) --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.
On Fri, Jun 07, 2019 at 10:02:23AM +, Lars Aksel Opsahl wrote: > So the problem is how to use tolerances so we get a behavior equal to > the test using meter. > > > We can we define the layer in Postgis Topology with quite big value > because this is just max value as it seems. So we can adjust the tolerance > parameter as we add lines but the problem is that we need to adjust this > parameter depending on where we are and what orientation the line has. For > vertical lines we need a bigger tolerance than for horizontal lines in > Norway. This makes it quite complicated to handle adding new lines. I'm not sure if it'd help but PostGIS Topology has an internal function (not to be relied upon, but could be copied to your own function) to determine "min tolerance" based on absolute coordinate values. That function is meant to deal with non-uniform floating-point resolution. What you're after is a function to deal with non-uniform tolerances. See https://trac.osgeo.org/postgis/browser/trunk/liblwgeom/lwgeom_topo.c?rev=14251#L4866 It used to be done in SQL with previous versions. Could that help ? --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS + pgdump
On Mon, May 20, 2019 at 12:35:40PM +, Zwettler Markus (OIZ) wrote: > You also think the errors are due to the version mismatch. Is that a question ? If it is, my answer is: NO. ERROR: relation "geometry_columns" already exists It means PostGIS is already installed in the target DB, and you're trying to install it again. > We should da a hard upgrade with postgis_restore.pl to avoid this errors. > Correct? You can try, and report errors here, if any. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS + pgdump
On Mon, May 20, 2019 at 07:16:15AM +, Zwettler Markus (OIZ) wrote: > We tried to pg_restore this dump into a database with and without pre-created > postgis extension. We got postgis errors in any case. Check out postgis_restore.pl, to be used in place of pg_restore. Is part of the postgis source, not sure if it is distributed with binary packages. The script was meant specifically to deal with these situations. Read more about its usage here: https://postgis.net/docs/postgis_installation.html#hard_upgrade It needs a "custom format" dump, so if your external source did not provide you with one you're out of luck. --strk; () Free GIS & Flash consultant/developer /\ https://strk.kbt.io/services.html ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS + pgdump
On Thu, May 16, 2019 at 01:09:54PM +, Zwettler Markus (OIZ) wrote: > We did a default PostGIS installation within a PostgreSQL 9.6 database: > > === > create extension if not exists postgis; > create extension if not exists postgis_topology; > create extension if not exists ogr_fdw; > create extension if not exists pgrouting; > === > > This installed PostGIS within the public schema of the database. > (SET SCHEMA is not supported since V2.3 anymore) > > Our customer application was also installed within the public schema. > > When we pg_dump + pg_restore the database we got a lot of errors. At pg_dump or pg_restore time ? What errors did you get ? Do you still have access to the working database ? If so, show us the output of: SELECT postgis_full_version(); --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] pg_dump on upgraded server
On Thu, Apr 04, 2019 at 08:33:47AM +0200, Paolo Cavallini wrote: > solved by forcing install of official deb postgresql-9.4-postgis-2.1 > from there on, things work. so, why does apt remove the needed package? > it seems appropriate to me keeping it until postgresql 9.4 is purged. > am I missing something? I also think Debian packaging of PostGIS would need some improvement, if you can get the maintainers to discuss this on postgis-devel I'd love to fully understand the issue and maybe we can even avoid doing things like dropping minor version number from DLL to work around what seems to me a packaging issue... --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] pg_dump on upgraded server
On Wed, Apr 03, 2019 at 07:34:33PM +0200, Paolo Cavallini wrote: > > > On 03/04/19 19:04, Sandro Santilli wrote: > > Can you show the ALTER EXTENSION command you're issuing and the > > error you're getting ? Also please show the output of > > > > SELECT postgis_full_version(); > > I tried both > ALTER EXTENSION postgis UPDATE; > with something like > the 2.1.4 version is already installed This is unexpected, if 2.3.1 was _really_ installed. Can it be you manually installed 2.1.4 again on top of 2.3.1 ? What version you update to depends on the contents of postgis.control file. > ALTER EXTENSION postgis > UPDATE TO "2.3.1" > with > ERRORE: l'estensione "postgis" non ha un percorso di aggiornamento > dalla versione "2.1.4" alla versione "2.3.1" It could be 2.1.4 was released _after_ 2.3.1 (check the NEWS file). In this case you could try a more recent 2.3.x version of PostGIS. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] pg_dump on upgraded server
On Wed, Apr 03, 2019 at 06:52:40PM +0200, Paolo Cavallini wrote: > On 03/04/19 18:32, Sandro Santilli wrote: > > > In theory, any such function would be replaced by running > > a soft upgrade of PostGIS in that database. > > how do I do a soft upgrade, given that alter extension says the function > is already installed in the database? Can you show the ALTER EXTENSION command you're issuing and the error you're getting ? Also please show the output of SELECT postgis_full_version(); --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
