[postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.

Hi We have a data set with many millions of lines/polygons and we use the postgis topology package and it is working quite well, but update is sometimes going slow. The problem is related to abs(r.element_id) generated when the function topology.ST_ModEdgeSplit(atopology varchar, anedge inte

Re: [postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.

://trac.osgeo.org/postgis/ticket/2083 Thanks Lars Fra: Sandro Santilli [sandro.santi...@gmail.com] på vegne av Sandro Santilli [s...@keybit.net] Sendt: 28. mars 2015 19:09 Til: PostGIS Users Discussion; Lars Aksel Opsahl Emne: Re: [postgis-users] Is the abs(r.element_id) function needed in

Re: [postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big performance decrease.

Fra: Sandro Santilli [sandro.santi...@gmail.com] på vegne av Sandro Santilli [s...@keybit.net] Sendt: 28. mars 2015 21:32 Til: Lars Aksel Opsahl Kopi: PostGIS Users Discussion Emne: Re: SV: [postgis-users] Is the abs(r.element_id) function needed in ST_ModEdgeSplit postgis topology ? It's is causing big

Re: [postgis-users] creating topology data is slow

We are working quite big datasets (about 1 billion points and 20 million lines) that we convert from simple feature to topology. I ran a test yesterday and we added these 20 million lines in about 9 hours with attached attributes. This means heavy parallel pressing and grid usage on a fast datab

Re: [postgis-users] creating topology data is slow

av Sandro Santilli [s...@keybit.net] Sendt: 3. juni 2015 10:49 Til: PostGIS Users Discussion Kopi: Lars Aksel Opsahl Emne: Re: [postgis-users] creating topology data is slow On Wed, Jun 03, 2015 at 05:56:24AM +0000, Lars Aksel Opsahl wrote: > We are working quite big datasets (about 1 billion

Re: [postgis-users] creating topology data is slow

Marc -Message d'origine- De : postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] De la part de Lars Aksel Opsahl Envoyé : 3 juin 2015 01:56 À : PostGIS Users Discussion Objet : Re: [postgis-users] creating topology data is slow We are working quite bi

Re: [postgis-users] creating topology data is slow

i [sandro.santi...@gmail.com] på vegne av Sandro Santilli [s...@keybit.net] Sendt: 3. juni 2015 10:49 Til: PostGIS Users Discussion Kopi: Lars Aksel Opsahl Emne: Re: [postgis-users] creating topology data is slow On Wed, Jun 03, 2015 at 05:56:24AM +0000, Lars Aksel Opsahl wrote: > We are working

Re: [postgis-users] creating topology data is slow

processing became slower when the edge_data table became bigger? Marc -Message d'origine- De : postgis-users-boun...@lists.osgeo.org [mailto:postgis-users-boun...@lists.osgeo.org] De la part de Lars Aksel Opsahl Envoyé : 3 juin 2015 01:56 À : PostGIS Users Discussion Objet : Re: [postgis-

Re: [postgis-users] Geoprocessing & BigData

Hi We have done some testing on this using a single Postgis server. -layer 1 has 7924019 rows with 11 columns and about 1 billion points. -layer 2 has 1088614 rows with 20 columns. Both layers covers all of Norway. I do a “esri” union in a psql function and get a new table with 27852836 row

[postgis-users] A function for “Esri union” union on big tables on github.

Hi There has been different mails about this topic lately. We have now have added the code we use to Github and hopefully somebody can pick up some ideas or just use this function as it is. The basic idea is that you call this function and with 2 tables as input. The following happens in the

[postgis-users] Slow ST_InteriorRingN and ST_intersection (using ST_ DumpRings makes intersection 230 times faster)

Hei I have big polygon with a total of 1471333 points and that contains 52079 interior rings. (https://github.com/larsop/esri_union/blob/master/src/test/sql/regress/esri_union-pre-helling_data_d1.sql ) (its only one row here now because I had to remove the rest to get below 50 Mb size on git

Re: [postgis-users] Slow ST_InteriorRingN and ST_intersection (using ST_ DumpRings makes intersection 230 times faster)

ssion Kopi: Martin Davis Emne: Re: [postgis-users] Slow ST_InteriorRingN and ST_intersection (using ST_ DumpRings makes intersection 230 times faster) On Wed, Feb 24, 2016 at 9:00 AM, Lars Aksel Opsahl wrote: > Then I rewrote ST_intersection to use ST_DumpRings and added a test on them &g

Re: [postgis-users] fuzzy tolerance

Hi One generic problem with adjusting input coordinates is that this mayl not solve the problem with overlapping or invalid polygons, because points in the center of a cell may go to any 4 grid points. For example if you have two simple feature polygons that are suppose to have no gaps or ov

Re: [postgis-users] fuzzy tolerance

Hi again I did a simple test with ST_SnapToGrid(geo, 0.001) on a real dataset that based on farm property border in Norway and intersection of this dataset http://www.skogoglandskap.no/en/subjects/ar5_land_resource_map/subject_view . The result was almost 100.000 invalid polygons of 2.6 milli

Re: [postgis-users] fuzzy tolerance

and using the edges added. The code for this is also found in pgtopo_update_sql but wrapped into different part so it takes some work to extract this code into single function like topo_help_sf_to_topology_case_1 . Hopefully I will get the time to fix this later. Lars ___

[postgis-users] If there any difference for snap to for lines and surfaces in Postgis Topology ?

Hi First I tested with lines and that worked find, the lines added the second time snapped to first lines. The data I tested with are found at. https://github.com/NibioOpenSource/pgtopo_update_sql/blob/develop/src/test/sql/import/data3/edge_1219.

Re: [postgis-users] Spatially ordering data

Hi Here https://github.com/larsop/content_balanced_grid is some generic code that you can use for this. The sql below will return a grid that contains max 4000 touching objects pr cell. If there is low density of objects in a area, the cells for that area will be bigger than the cells in a

Re: [postgis-users] Spatially ordering data

Hi Thanks for testing it. We have used it quite bit and have not seen any scaling problems yet. For a layer with 8 million polygons (about 1 billion points) it takes about one minute to create a content based grid. (Se example further down) We use the && operator to check intersection to av

Re: [postgis-users] DISTINCT geometry to a code

Hi You can do something like this, but this uses a extra help table so it's not the best solution. create table geo_key_map as ( select distinct md5(ST_asBinary(geo)) from test_table); SELECT 8144241 Then you get a table where you add serial column and get counter from 1 to 8144241. If

Re: [postgis-users] [EXTERNAL] Re: PostGIS on Centos 7

Hi On CentOS Linux release 7.4.1708 (Core) this worked without any problems a couple of weeks ago. yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm yum install postgresql10 postgresql10-server postgis24_10 postgis24_10-utils.x86_6

[postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

Hi We are upgrading from Postgres 9.5 to Postgres 11 and have a problem with ST_Union query. It used around 5 minutts on Postgres 9.5 but on the Postgres 11 it takes around a hour. This is two different physical servers. * Postgres 11 server (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz), is

Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

Hi Increased work_mem from 30MB to 1000MB , but that did not help. Total memory on the server is around 98 GB. If I run top on the server, there is 1 CPU running on 100% and there is no io-wait. Here is the new plan for the test 1000 MB. set work_mem TO '1000MB'; SET sl=# show work_mem ;

Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

rs [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Lars Aksel Opsahl Sent: Thursday, April 11, 2019 6:27 AM To: PostGIS Users Discussion Subject: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11 Hi We are upgrading from Po

Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

scussion Subject: Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11 On Thu, Apr 11, 2019 at 3:27 AM Lars Aksel Opsahl mailto:lars.ops...@nibio.no>> wrote: Can it be problem related ST_Union and aggregate ? Can you

Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

ket/867 On Thu, Apr 11, 2019 at 11:07 AM Martin Davis mailto:mtncl...@gmail.com>> wrote: On Thu, Apr 11, 2019 at 3:27 AM Lars Aksel Opsahl mailto:lars.ops...@nibio.no>> wrote: Can it be problem related ST_Union and aggregate ? Can you test the performance of aggregate ST_Union on it&#

Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

Hi Thanks , this was fast code update . Tested the code now but with geos 3.7 and the attached file and I still have the same problem . I assume there is something related to that I tested with geos 3.7. I also had to test om another server with this cpu (Intel(R) Xeon(R) CPU E3-1270 V2 @ 3.5

[postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

Hi We have one issue/problem and that is how to handle Tolerance/SnapTo in a good way when working in degrees. We work on maps that covers all of Norway so we use degrees https://epsg.io/4258 . We also use Postgis Topology, which means that we just have add new lines both for surface and lin

Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

of Norway” planar projection you can use to handle the integrative whole-country work cases? Even if there isn’t, you can always invent one and add it to the spatial_ref_sys table. P On Jun 7, 2019, at 3:02 AM, Lars Aksel Opsahl mailto:lars.ops...@nibio.no>> wrote: Hi We have one issue/problem

Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

ich are updated regularly ? Thanks a lot. Lars From: s...@kbt.io on behalf of Sandro Santilli Sent: Saturday, June 8, 2019 2:06 PM To: PostGIS Users Discussion Cc: Lars Aksel Opsahl Subject: Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees. On

Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

)); st_astext -- POINT(1108142.5503 7787999.99979103) (1 row) Lars From: postgis-users on behalf of Lars Aksel Opsahl Sent: Monday, June 10, 2019 10:50 PM To: PostGIS Users Discussion Cc: Sandro Santilli Subject: Re

Re: [postgis-users] Tolerance/SnapTo in Postgis Topology for meter and degrees.

the keep track of polygon borders and the areas of polygon ? Thanks Lars From: Paul Ramsey Sent: Tuesday, June 11, 2019 5:36 PM To: Lars Aksel Opsahl Cc: PostGIS Users Discussion; Sandro Santilli Subject: Re: [postgis-users] Tolerance/SnapTo in Postgis

[postgis-users] gdal version with PostGIS

Hi Whats the policy for build scripts for postgis for new versions of gdal? When I installed with yum on Centos 7 (X86), I got GDAL 2.3.2 POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" SFCGAL="1.3.1" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.3.2, release

Re: [postgis-users] gdal version with PostGIS

#x27;s metadata. On Mon, Sep 2, 2019 at 10:36 AM Lars Aksel Opsahl mailto:lars.ops...@nibio.no>> wrote: Hi Whats the policy for build scripts for postgis for new versions of gdal? When I installed with yum on Centos 7 (X86), I got GDAL 2.3.2 POSTGIS="2.5.2 r17328"

Re: [postgis-users] breake lines

Hi Another option to handle merging off different simple feature datasets into a single dataset can be Postgis Topology. What we basically do is that we pipe the datasets into Postgis Topology which solves the problems related to breaking up lines and snap to. Then we take the data out as simp

Re: [postgis-users] gdal version with PostGIS

> From: postgis-users on behalf of > Devrim Gündüz > Sent: Tuesday, September 10, 2019 11:48 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] gdal version with PostGIS > > > Hi, > > On Mon, 2019-09-02 at 07:36 +, Lars Aksel Opsahl wr

[postgis-users] Why is && operator causing Parallel to kick in ?

Hi In the first sql below we just use "ST_Intersects(r1.geo,r2.geo)", in the second SQL we add "r1.geo && r2.geo" The second sql executes 3 times faster probably because of parallel seems to kick in. (if we adjust parallell settings we can get the second SQL to run 6. times faster) Why is t

[postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets

Hi I have question about Postgis Topology and what seems like scaling problem when converting simple feature datasets to Postgis Topology. The code I am testing on is here. https://github.com/larsop/resolve-overlap-and-gap/tree/add_postgis_topology_TopoGeo_addLinestring_thred_grid When working

Re: [postgis-users] Postgis Topology and slow topology.TopoGeo_addLinestring on big datasets

t 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,” >&

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

[postgis-users] topology.TopoGeo_AddLineString performance depends very on order of input lines in some cases.

Hi 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 c

Re: [postgis-users] How does PostGIS / PostgreSQL distribute parallel work to cores for GIS type data?

>Hei > >From: postgis-users on behalf of Marco >Boeringa >Sent: Wednesday, March 4, 2020 2:49 PM >To: postgis-users@lists.osgeo.org >Subject: Re: [postgis-users] How does PostGIS / PostgreSQL distribute parallel >work to cores for GIS type data? > >Hi P

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 +0000, 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 , b

Re: [postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5

>From: postgis-users on behalf of Alain >Benard Sent: Monday, November 2, 2020 9:17 AMTo: >postgis-users@lists.osgeo.org Subject: >[postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5 > > >Hi , > >I have a problem with rasterpgsql with

Re: [postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5

> > >From: postgis-users on behalf of >Regina Obe Sent: Wednesday, November 4, 2020 11:05 PMTo: >'PostGIS Users Discussion' Subject: Re: >[postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5 > > >Sounds from the below thread the issue is with proj and the interaction wit

Re: [postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5

>From: postgis-users on behalf of Jorge >Gustavo Rocha Sent: Thursday, November 5, 2020 10:44 AMTo: >postgis-users@lists.osgeo.org Subject: Re: >[postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5 > >Hi Lars, > >I would start by removing the old libproj.so.15. You stil

Re: [postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5

>From: postgis-users on behalf of >Regina Obe Sent: Thursday, November 5, 2020 11:52 AMTo: >'PostGIS Users Discussion' Subject: Re: >[postgis-users] Problem raster2pgsql on Centos 8 - postgres 12 / postgis 2.5 > > > >From your postgis output you are running unreleased version of PostGIS. Is >

Re: [postgis-users] Topology creation performance

> > >From: postgis-users on behalf of >Alexandre Silva Sent: Friday, November 20, >2020 5:55 PMTo: postgis-users@lists.osgeo.org >Subject: [postgis-users] Topology creation >performance > >Hello, > >I'm creating a topology with a large amount of lines (around 165k) and when >adding those line

Re: [postgis-users] Topology creation performance

> > >From: Alexandre Silva Sent: Monday, November >23, 2020 2:38 PMTo: PostGIS Users Discussion ; >Lars Aksel Opsahl Subject: Re: [postgis-users] Topology >creation performance > >On 23/11/20 07:39, Lars Aksel Opsahl wrote: > >Hi and thanks for your input, >

Re: [postgis-users] Topology creation performance

Hi >About big polygons, we have polygons 295834 points and 5042 holes and Postgis >Topology seems to handles it OK, with breaking them up in to smaller parts. Sorry I was missing a word in the sentence above. About big polygons, we have polygons 295834 points and 5042 holes and Postgis Topolog

Re: [postgis-users] Cut extent box2d into small regular boxes, and iterate through each box of features

>From: postgis-users on behalf of Mike >Taves Sent: Sunday, March 27, 2022 10:31 PMTo: PostGIS >Users Discussion Subject: Re: [postgis-users] >Cut extent box2d into small regular boxes, and iterate through each box of >features > >On Sat, 26 Mar 2022 at 02:44

Re: [postgis-users] Cut extent box2d into small regular boxes, and iterate through each box of features

>From: postgis-users on behalf of >Shaozhong SHI Sent: Monday, March 28, 2022 10:25 AMTo: >PostGIS Users Discussion Subject: Re: >[postgis-users] Cut extent box2d into small regular boxes, and iterate through >each box of features > >Hi, Lars, > >No explanation on content-balanced_grid can be

Re: [postgis-users] Cut extent box2d into small regular boxes, and iterate through each box of features

>From: postgis-users on behalf of >Shaozhong SHI Sent: Monday, March 28, 2022 11:42 AMTo: >PostGIS Users Discussion Subject: Re: >[postgis-users] Cut extent box2d into small regular boxes, and iterate through >each box of features >Hi, Lars, > >Would the following alternative work? > >In PostG

Re: [postgis-users] Cut extent box2d into small regular boxes, and iterate through each box of features

>From: postgis-users on behalf of >Shaozhong SHI Sent: Monday, March 28, 2022 3:21 PMTo: >PostGIS Users Discussion Subject: Re: >[postgis-users] Cut extent box2d into small regular boxes, and iterate through >each box of features > >Hi, Lars, > >Your points are very interesting. > >How about t

Re: [postgis-users] Is there a better way for Finding Gaps/Sliver Holes?

>From: postgis-users on behalf of >Shaozhong SHI >Sent: Monday, May 2, 2022 12:22 PM >To: PostGIS Users Discussion >Subject: [postgis-users] Is there a better way for Finding Gaps/Sliver Holes? > >This solution does not appear to be well explained. > >Find Topology Error With PostGIS (khairu-aq

[postgis-users] https://postgis.net/docs/geometry_distance_knn.html and index usage

Hi I have two simple tables. Table "pg_temp_30.g1temp" Column | Type | Collation | Nullable | Default ++---+--+ geo| geometry(Polygon,4258) | | | id |

Re: [postgis-users] https://postgis.net/docs/geometry_distance_knn.html and index usage

referenced: "Index only kicks in >if one of the geometries is a constant (not in a subquery/cte). e.g. >'SRID=3005;POINT(1011102 450541)'::geometry instead of a.geom". Neither value >passed to "<->" in your query is a constant. > >On 6/22/

[postgis-users] Postgis topology survey (FOSS4G 2022 - Firenze)

Hi Nibio.no together with Sandro Santilli has been working on Postgis Topology and ecosystems around for some time, that now are available for many hundred users in Norway. But we miss information about others who are working with/testing Postgis Topology around the world. Here is a very smal

Re: [postgis-users] Topology question

>From: postgis-users on behalf of Simon >Greener >Sent: Wednesday, March 22, 2023 11:20 AM >To: PostGIS Users Discussion >Subject: [postgis-users] Topology question > >I'm proposing PostGIS for a customer with qGIS as an editing tool. The >question of topological editing is to be addressed. Wh

[postgis-users] Postgis Simple Feature silently giving us wrong results

Hi In Postgis we now have problems where we silently get wrong result from ST_Difference. This bug was only found because a tester did inspect the result manually by actually checking the maps generated on the screen. In this case, we have a fixed restricted area (blue), where users can selec

Re: [postgis-users] Postgis Simple Feature silently giving us wrong results

results I haven’t looked thru your examples, but it would be good to know which version of GEOS you are running since ST_Difference ultimately depends on GEOS. What does: SELECT postgis_full_version(), version(); Return on your system? Thanks, Regina From: postgis-users [mailto:postg