Re: linear reference/dynamic segmentation
Yes, Sergio! We use PostGIS LRS quite extensively, as reflected in some LRS-related videos https://www.youtube.com/linearbench. I am more than happy to have a screen session to share our experience with you. My email is: bo@gisticinc.com Bo On Wed, May 8, 2024 at 10:36 AM SERGIO ACOSTAYLARA < sergio.acostayl...@mtop.gub.uy> wrote: > Thank you Bo Guo. Did you use it? Do you know someone who uses it? Do you > know of any use or success cases? > > > Sergio Acosta y Lara > Departamento de Geomática > Dirección Nacional de Topografía > Ministerio de Transporte y Obras Públicas > URUGUAY > (598)29157933 ints. 20329 > http://geoportal.mtop.gub.uy/ > ------ > *De:* Bo Guo > *Enviado:* miércoles, 8 de mayo de 2024 12:00:20 > *Para:* SERGIO ACOSTAYLARA > *Cc:* postgis-users@lists.osgeo.org > *Asunto:* Re: linear reference/dynamic segmentation > > Yes, Sergio. Postgis has a complete and robust set of LRS > functionalities. > > *Bo * > > > On Wed, May 8, 2024 at 6:21 AM SERGIO ACOSTAYLARA < > sergio.acostayl...@mtop.gub.uy> wrote: > >> Hi. Is there anybody in the list that has any experience using linear >> reference/dynamic segmentation with postgis? We need to show dynamic >> attributes in our geoportal/visualizer. Is this possible to do it with >> postgis? >> >> Thanks in advance >> >> >> Sergio Acosta y Lara >> Departamento de Geomática >> Dirección Nacional de Topografía >> Ministerio de Transporte y Obras Públicas >> URUGUAY >> (598)29157933 ints. 20329 >> http://geoportal.mtop.gub.uy/ >> >
Re: linear reference/dynamic segmentation
Yes, Sergio. Postgis has a complete and robust set of LRS functionalities. *Bo * On Wed, May 8, 2024 at 6:21 AM SERGIO ACOSTAYLARA < sergio.acostayl...@mtop.gub.uy> wrote: > Hi. Is there anybody in the list that has any experience using linear > reference/dynamic segmentation with postgis? We need to show dynamic > attributes in our geoportal/visualizer. Is this possible to do it with > postgis? > > Thanks in advance > > > Sergio Acosta y Lara > Departamento de Geomática > Dirección Nacional de Topografía > Ministerio de Transporte y Obras Públicas > URUGUAY > (598)29157933 ints. 20329 > http://geoportal.mtop.gub.uy/ >
Re: [postgis-users] Unexpected interaction with ST_DumpPoints when creating table from conditional query on a partitioned table.
Matt, this is the expected SQL behavior when group by is used in the selection. See if removing "group by" will return what you need. Bo On Mon, Nov 27, 2023 at 5:15 AM Matt Gibbins via postgis-users < postgis-users@lists.osgeo.org> wrote: > I have encounter an unexpected result when running st_dumppoints() with a > partitioned dataset. > > In this case the partitioned dataset has the following definition for the > master table partitioned by tc_date > > CREATE TABLE maps.routelines ( > > tc_line_id serial4 NOT NULL, > > tc_date date NOT NULL, > > shape_id varchar(10) NOT NULL, > > route_id varchar(10) NOT NULL, > > route_short_name varchar(15) NULL, > > route_long_name varchar(255) NULL, > > route_desc varchar(50) NULL, > > route_type int2 NULL, > > direction_id int2 NOT NULL, > > both_ways bool NULL, > > geom public.geometry NOT NULL, > > CONSTRAINT routelines_pk PRIMARY KEY (tc_date, shape_id) > > ); > > The following query is successful. > > select tc_date,shape_id,direction_id,route_id,(st_dumppoints(geom)).* > > from maps.routelines > > where route_type<=3 > > group by tc_date,shape_id,direction_id,route_id; > > However, when I create a table from maps.routelines as shown below the > query fails with the message 'ERROR: column "routelines_not_school.geom" > must appear in the GROUP BY clause or be used in an aggregate function' > > This is the process which results in the error. > > drop table if exists routelines_not_school; > > create temp table routelines_not_school as select * from maps.routelines > where route_type<=3; -- exclude school bus services > > select tc_date,shape_id,direction_id,route_id,(st_dumppoints(geom)).* > > from routelines_not_school > > where route_type<=3 > > group by tc_date,shape_id,direction_id,route_id; > > This is unexpected. > Any assistance appreciated. > > Matt. > > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis_full_version(): WAGYU="0.5.0 (Internal)" (core procs from "3.3.4 3.3.4" need upgrade)
Hi Regina, Doing "SELECT postgis_extensions_upgrade()" brought all servers on the same Postgis version! Thanks! Bo On Thu, Sep 7, 2023 at 10:27 PM Regina Obe wrote: > I don’t think the complain is about WAGYU. That version info just happens > to sit next to the message. > > > > Have you tried doing > > > > SELECT postgis_extensions_upgrade(); > > > > Already and what does it say? > > > > *From:* postgis-users *On Behalf > Of *Bo Guo > *Sent:* Thursday, September 7, 2023 4:52 PM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] Postgis_full_version(): WAGYU="0.5.0 > (Internal)" (core procs from "3.3.4 3.3.4" need upgrade) > > > > Hi there, > > > > I am trying to get all three environments to have the same version of > Postgis. > > > > Could someone on the list provide any advice on how I may update the WAGYU > core procs on one of the servers to match with the rest? > > > > I appreciate your help! > > > > Cheers, > > > > Bo > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Postgis_full_version(): WAGYU="0.5.0 (Internal)" (core procs from "3.3.4 3.3.4" need upgrade)
Hi there, I am trying to get all three environments to have the same version of Postgis. Could someone on the list provide any advice on how I may update the WAGYU core procs on one of the servers to match with the rest? I appreciate your help! Cheers, Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"
Hi, Regina! I solved the problem. It turned out I missed the step of "Adds pgdg keys and main repo" as referenced in https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS3UbuntuPGSQLApt Thank you again! Bo On Tue, Sep 5, 2023 at 5:52 AM Bo Guo wrote: > Hi, Regina! > > I do not know why the new stall Postgresql 14 with Postgis-3 on the newly > built Ubuntu server 22.04 would have Postgis 3.2.0. This was the command I > used for the postgis install: >sudo apt install postgresql-14-postgis-3 > > I went to the postgis folder (/usr/share/postgresql/14/extension) but > could ONLY see files with version of 3.2.0 and lower. So it appeared that > the installation did not bring postgis to the 3.2.1 or above. Please let > me know alternative ways to install higher version. > > Cheers, > > Bo > > > On Mon, Sep 4, 2023 at 5:06 PM Regina Obe wrote: > >> Bo, >> >> >> >> At a glance, it looks like maybe your PostgreSQL 13 had a 3.2.1 installed >> and your PostgreSQL 14 has 3.2.0 installed (or at least the postgis.control >> file probably has 3.2.0 instead of 3.2.1). >> >> I’d double check to make sure you did install 3.2.1 on your PostgreSQL >> 14. Downgrades are not supported which is essentially what it’s telling >> you. >> >> >> >> >> >> That said, the preferable way for upgrading PostGIS 3 is to use: >> >> >> >> SELECT postgis_extensions_upgrade(); >> >> >> >> Though that will probably result in the same error since it seems your >> PostgreSQL 14 is running with 3.2.0 instead of 3.2.1. >> >> >> >> The ALTER EXTENSION approach has a couple of downsides, that >> postgis_extensions_upgrade can handle >> >> >> >>1. It will only update postgis and not your other extensions like for >>example your postgis_topology. Postgis_extensions_upgrade() will upgrade >>any postgis_ extension. >>2. If you are moving from a PostgreSQL major to another PostgreSQL >>major on same version, you still need to upgrade, but because ALTER >>EXTENSION relies on a version bump, it won’t be able to upgrade. >> >> >> >> If you still see a mismatch after please let us know. I know we’ve made >> improvements in postgis_extensions_upgrade so 3.2.0 might not have all the >> new features. >> >> >> >> Thanks, >> >> Regina >> >> >> >> *From:* postgis-users *On Behalf >> Of *Bo Guo >> *Sent:* Monday, September 4, 2023 7:40 PM >> *To:* PostGIS Users Discussion >> *Subject:* [postgis-users] ERROR: extension "postgis" has no update path >> from version "3.2.1" to version "3.2.0" >> >> >> >> Hi all, >> >> >> >> I do not know how it happened, but after I upgraded from Postgresql 13- >> 14, I had a problem updating extensions, including postgis by using: >> >> >> >> ALTER EXTENSION "postgis" UPDATE; >> >> >> >> Here is the version info: >> >> >> >> SELECT version(); >> >> PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, >> compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit >> >> >> >> SELECT postgis_full_version(); >> >> POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for >> use with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" >> LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" >> (core procs from "3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs >> from "3.2.1 5fae8e5" need upgrade) >> >> >> >> I really appreciate any help you can provide. >> >> >> >> Bo >> >> >> >> >> >> *Bo Guo**, PhD, PE* >> President >> >> 2820 S Alma School Rd #18-671, Chandler, AZ 85286 >> >> p 480-656-9962 c 602-570-4697 >> *www.gisticinc.com <http://www.gisticinc.com>* >> >> *youtube.com/linearbench <http://youtube.com/linearbench>* >> >> >> ___ >> postgis-users mailing list >> postgis-users@lists.osgeo.org >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"
Hi, Regina! I do not know why the new stall Postgresql 14 with Postgis-3 on the newly built Ubuntu server 22.04 would have Postgis 3.2.0. This was the command I used for the postgis install: sudo apt install postgresql-14-postgis-3 I went to the postgis folder (/usr/share/postgresql/14/extension) but could ONLY see files with version of 3.2.0 and lower. So it appeared that the installation did not bring postgis to the 3.2.1 or above. Please let me know alternative ways to install higher version. Cheers, Bo On Mon, Sep 4, 2023 at 5:06 PM Regina Obe wrote: > Bo, > > > > At a glance, it looks like maybe your PostgreSQL 13 had a 3.2.1 installed > and your PostgreSQL 14 has 3.2.0 installed (or at least the postgis.control > file probably has 3.2.0 instead of 3.2.1). > > I’d double check to make sure you did install 3.2.1 on your PostgreSQL > 14. Downgrades are not supported which is essentially what it’s telling > you. > > > > > > That said, the preferable way for upgrading PostGIS 3 is to use: > > > > SELECT postgis_extensions_upgrade(); > > > > Though that will probably result in the same error since it seems your > PostgreSQL 14 is running with 3.2.0 instead of 3.2.1. > > > > The ALTER EXTENSION approach has a couple of downsides, that > postgis_extensions_upgrade can handle > > > >1. It will only update postgis and not your other extensions like for >example your postgis_topology. Postgis_extensions_upgrade() will upgrade >any postgis_ extension. >2. If you are moving from a PostgreSQL major to another PostgreSQL >major on same version, you still need to upgrade, but because ALTER >EXTENSION relies on a version bump, it won’t be able to upgrade. > > > > If you still see a mismatch after please let us know. I know we’ve made > improvements in postgis_extensions_upgrade so 3.2.0 might not have all the > new features. > > > > Thanks, > > Regina > > > > *From:* postgis-users *On Behalf > Of *Bo Guo > *Sent:* Monday, September 4, 2023 7:40 PM > *To:* PostGIS Users Discussion > *Subject:* [postgis-users] ERROR: extension "postgis" has no update path > from version "3.2.1" to version "3.2.0" > > > > Hi all, > > > > I do not know how it happened, but after I upgraded from Postgresql 13- > 14, I had a problem updating extensions, including postgis by using: > > > > ALTER EXTENSION "postgis" UPDATE; > > > > Here is the version info: > > > > SELECT version(); > > PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, > compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit > > > > SELECT postgis_full_version(); > > POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for > use with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" > LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" > (core procs from "3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs > from "3.2.1 5fae8e5" need upgrade) > > > > I really appreciate any help you can provide. > > > > Bo > > > > > > *Bo Guo**, PhD, PE* > President > > 2820 S Alma School Rd #18-671, Chandler, AZ 85286 > > p 480-656-9962 c 602-570-4697 > *www.gisticinc.com <http://www.gisticinc.com>* > > *youtube.com/linearbench <http://youtube.com/linearbench>* > > > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] ERROR: extension "postgis" has no update path from version "3.2.1" to version "3.2.0"
Hi all, I do not know how it happened, but after I upgraded from Postgresql 13- 14, I had a problem updating extensions, including postgis by using: ALTER EXTENSION "postgis" UPDATE; Here is the version info: SELECT version(); PostgreSQL 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit SELECT postgis_full_version(); POSTGIS="3.2.0 c3e3cc0" [EXTENSION] PGSQL="130" (procs need upgrade for use with PostgreSQL "140") GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.12" LIBJSON="0.15" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" (core procs from "3.2.1 5fae8e5" need upgrade) TOPOLOGY (topology procs from "3.2.1 5fae8e5" need upgrade) I really appreciate any help you can provide. Bo *Bo Guo**, PhD, PE* President 2820 S Alma School Rd #18-671, Chandler, AZ 85286 p 480-656-9962 c 602-570-4697 *www.gisticinc.com <http://www.gisticinc.com>* *youtube.com/linearbench <http://youtube.com/linearbench>* ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] st_transform performance after upgrade
Mike, Thanks for your post. Indeed by assigning srid to the geom, we were back in business! *Bo* On Wed, Aug 23, 2023 at 6:25 PM Mike Taves wrote: > On Thu, 24 Aug 2023 at 13:08, Bo Guo wrote: > > But we ran into severe (15 times) performance degradation when making CS > transformation calls such as st_transform (geom, proj4, 4326) > > Note the docs say the geometry ST_Transform(geometry geom, text > from_proj, text to_proj) is not optimized. > > Are you able to assign SRIDs to the geometries and use > ST_Transform(geometry g1, integer srid)? Are there specific SRIDs that > are slower? > ___ > postgis-users mailing list > postgis-users@lists.osgeo.org > https://lists.osgeo.org/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] st_transform performance after upgrade
Hi there, One of our Postgresql Servers was recently upgraded from POSTGIS="3.2.1 5fae8e5" [EXTENSION] PGSQL="130" (procs need upgrade for use with PostgreSQL "140") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY on PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit to POSTGIS="3.3.4 3.3.4" [EXTENSION] PGSQL="140" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY on PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit But we ran into severe (15 times) performance degradation when making CS transformation calls such as st_transform (geom, proj4, 4326) Could someone help me with this? Cheers, *Bo* ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Where to find Postgis-3.x for Postgresql 15
Hi there, I was trying to upgrade Postgres 14 / Pistgis 3 to Postgres 15, but I could not find Postgis for Postgres 15. My operating system is Ubuntu 22.04. Thanks! *Bo Guo* 2820 S Alma School Rd #18-671, Chandler, AZ 85286 p 480-656-9962 c 602-570-4697 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Bizarre behavior with postgis function as part of where clause!
Hi, "offset 0" in CTE worked. I later used CASE-CLAUSE to avoid using CTE and worked as well. Cheers! SELECT ST_GeometryType(geom) gtype, geom FROMtmp_test_geoms WHERECASE WHEN ST_GeometryType(geom) = 'ST_Point' THEN ST_X(geom) < 1 ELSE FALSE END; On 9/5/20 11:14 AM, Regina Obe wrote: Or I forgot to mention, I think the old hack of OFFSET like you have in 3 still works and for backward compatibility would be preferable over using the MATERIALIZED keyword, but less clear in intent So something WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' OFFSET 0) SELECT * FROM foo WHERE ST_X(geom) > 1; *From:*Regina Obe [mailto:l...@pcorp.us] *Sent:* Saturday, September 5, 2020 2:12 PM *To:* 'PostGIS Users Discussion' *Subject:* RE: [postgis-users] Bizarre behavior with postgis function as part of where clause! Have you tried adding a MATERIALIZED to your WITH? In PostgreSQL 12 and above, CTEs are no longer always materialized. e.g. WITH foo AS MATERIALIZED (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point') SELECT * FROM foo WHERE ST_X(geom) > 1; This article describes it a bit https://paquier.xyz/postgresql-2/postgres-12-with-materialize/ *From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Bo Guo *Sent:* Friday, September 4, 2020 11:28 PM *To:* postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> *Subject:* [postgis-users] Bizarre behavior with postgis function as part of where clause! Hi List, I just ran into a pretty bizarre query behavior involving simple queries involving PostGIS functions as part of where clause condition. I have two geometries in table tmp_test_geoms , one point and a line: ST_Point "010100806FE29BC53B2241800FDDE3E9C53341" ST_LineString "0102000500E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341" Both Query 1 and Query 2 fails w/ message "Argument to ST_X() must have type POINT," Query 3 works however 1. SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' AND ST_X(geom) > 1; 2. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point') SELECT * FROM foo WHERE ST_X(geom) > 1; 3. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' LIMIT 10) SELECT * FROM foo WHERE ST_X(geom) > 1; It seems that the query engine evaluates ST_X first before applying the GeometryType filter, even when the filter is in a CTE. It further seems that by adding LIMIT with an arbitrary value at the end of the CTE, the filter is applied first! Wonder if there is a way to give some type of query hint to force the evaluation sequences. Thanks for any suggestions! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users -- Regards, Bo Guo, PhD, PE President Gistic Research, Inc. 2033 E Warner Rd Ste 105 Tempe, AZ 85284 www.gisticinc.com www.youtube.com/linearbench Office: 480-656-9962 Cell: 602-570-4697 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Bizarre behavior with postgis function as part of where clause!
Hi List, I just ran into a pretty bizarre query behavior involving simple queries involving PostGIS functions as part of where clause condition. I have two geometries in table tmp_test_geoms , one point and a line: ST_Point "010100806FE29BC53B2241800FDDE3E9C53341" ST_LineString "0102000500E072B95C215A22412033C40C65963341401C0E074D5A22412097CB056E963341809821E6235E22412033C48C6D963341A027E909BA5F22410087C3E16E963341C0B2AEACD95F224100FCFDFE71963341" Both Query 1 and Query 2 fails w/ message "Argument to ST_X() must have type POINT," Query 3 works however 1. SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' AND ST_X(geom) > 1; 2. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point')SELECT * FROM foo WHERE ST_X(geom) > 1; 3. WITH foo AS (SELECT ST_GeometryType(geom) gtype, geom FROM tmp_test_geoms WHERE ST_GeometryType(geom) = 'ST_Point' LIMIT 10)SELECT * FROM foo WHERE ST_X(geom) > 1; It seems that the query engine evaluates ST_X first before applying the GeometryType filter, even when the filter is in a CTE. It further seems that by adding LIMIT with an arbitrary value at the end of the CTE, the filter is applied first! Wonder if there is a way to give some type of query hint to force the evaluation sequences. Thanks for any suggestions! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL 12 and PostGIS
You are right, Regina. I had installed postgis in the postgres database in addition to own database. - Bo On 12/24/19 10:26 AM, Regina Obe wrote: Yah if you had 2.5.3, you definitely didn’t need to bother with the create view part. I still think you’ve got PostGIS installed in postgres database. Try doing this: \connect postgres DROP EXTENSION postgis; See if it drops – I bet it does. Then run your upgrade again. *From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Bo Guo *Sent:* Monday, December 23, 2019 5:50 PM *To:* postgis-users@lists.osgeo.org *Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS Regina, the saga goes on, but we are getting closer... First of all, I removed a stray "topology" schema from the "postgres" database. With that, I am certain that there is no other postgis installs on the Test Server. Then I ran the postgis extention upgrade in the targeted database (LB) and saw the current version is 2.5.3. Then I recreated the geometry_columns view by modifying the source you provided with the postgis schema prefix "postgis" so the view is not created in the "public" schema. Finally, I ran sudo pg_upgradecluster -m upgrade 11 main with the following console msg Stopping old cluster... Creating new PostgreSQL cluster 12/main ... /usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/12/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... America/Phoenix creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctlcluster 12 main start Ver Cluster Port Status Owner Data directory Log file 12 main 5433 down postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log /usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin -B /usr/lib/postgresql/12/bin -p 5432 -P 5433 -d /etc/postgresql/11/main -D /etc/postgresql/12/main Finding the real data directory for the source cluster ok Finding the real data directory for the target cluster ok Performing Consistency Checks - Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for tables WITH OIDS ok Checking for invalid "sql_identifier" user columns ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade -- Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster postgres *failure* Consult the last few lines of "pg_upgrade_dump_19530.log" for the probable cause of the failure. Failure, exiting Error: pg_upgrade run failed. Logfiles are in /var/log/postgresql/pg_upgradecluster-11-12-main.kFdl Error during clust
Re: [postgis-users] PostgreSQL 12 and PostGIS
es
not exist
LINE 28: "replace"("split_part"("s"."consrc", ::"text...
^
HINT: Perhaps you meant to reference the column "s.conkey" or the
column "s.conbin".
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('64518'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('64517'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('64516'::pg_catalog.oid);
CREATE VIEW "public"."geometry_columns" AS
SELECT ("current_database"())::character varying(256) AS
"f_table_catalog",
"n"."nspname" AS "f_table_schema",
"c"."relname" AS "f_table_name",
"a"."attname" AS "f_geometry_column",
COALESCE("public"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims",
2) AS "coord_dimension",
COALESCE(NULLIF("public"."postgis_typmod_srid"("a"."atttypmod"), 0),
"sr"."srid", 0) AS "srid",
("replace"("replace"(COALESCE(NULLIF("upper"("public"."postgis_typmod_type"("a"."atttypmod")),
'GEOMETRY'::"text"), "st"."t$
FROM (("pg_class" "c"
JOIN "pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND (NOT
"a"."attisdropped"
JOIN "pg_namespace" "n" ON (("c"."relnamespace" = "n"."oid")))
JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid")))
LEFT JOIN ( SELECT "s"."connamespace",
"s"."conrelid",
"s"."conkey",
("replace"("split_part"("s"."consrc", ' = '::"text", 2),
')'::"text", ''::"text"))::integer AS "ndims"
FROM "pg_constraint" "s"
WHERE ("s"."consrc" ~~* '%ndims(% = %'::"text")) "sn" ON
((("sn"."connamespace" = "n"."oid") AND ("sn"."conrelid" = "$
LEFT JOIN ( SELECT "s"."connamespace",
"s"."conrelid",
"s"."conkey",
("replace"("replace"("split_part"("s"."consrc", ' = '::"text", 2),
')'::"text", ''::"text"), '('::"text", ''::"text$
FROM "pg_constraint" "s"
WHERE ("s"."consrc" ~~* '%srid(% = %'::"text")) "sr" ON
((("sr"."connamespace" = "n"."oid") AND ("sr"."conrelid" = "c$
WHERE (("c"."relkind" = ANY (ARRAY['r'::"char", 'v'::"char",
'm'::"char", 'f'::"char", 'p'::"char"])) AND (NOT ("c"."relname"$
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "postgis" ADD VIEW "public"."geometry_columns";
On 12/23/19 12:26 PM, Regina Obe wrote:
That looks good as it’s not showing the scripts procs need upgrade
Message any more
Do you by chance have more than one database with PostGIS installed?
You’d need to run it on every database if you do.
That’s the only thing I can think of that might be causing the issue.
It is possible that the update didn’t happen in 2.5.2 and instead in
2.5.3, but I thought it was in 2.5.2.
That said – you could overwrite the views in your 11 cluster with the
patched ones.
geometry_columns
https://git.osgeo.org/gitea/postgis/postgis/src/tag/2.5.3/postgis/postgis.sql.in#L5608
(make sure to prefix with the schema you have postgis installed in so
doesn’t try to create a new view)
*From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org]
*On Behalf Of *Bo Guo
*Sent:* Monday, December 23, 2019 10:54 AM
*To:* postgis-users@lists.osgeo.org
*Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS
Regina, Since the new cluster was never created so I just upgraded
the extension by running your select statement which returned:
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1
27a5e771&
Re: [postgis-users] PostgreSQL 12 and PostGIS
Regina, Since the new cluster was never created so I just upgraded the
extension by running your select statement which returned:
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1
27a5e771" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3,
released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99"
LIBPROTOBUF="1.2.1" TOPOLOGY RASTER
Not sure if the postgis gets updated though, as my problem persisted
with the same error message.
Cheers,
Bo
On 12/22/19 11:03 PM, Regina Obe wrote:
Okay this one is caused by a change in PostgreSQL 12 catalogs.
Fixed in PostGIS 2.5.2 I believe -
https://git.osgeo.org/gitea/postgis/postgis/raw/tag/2.5.2/NEWS (the
pg_constraint change)
So to upgrade, what you need to do is
1)Reinit your new cluster (as I think at this point your new cluster
is in an unstable state)
2) Restart up your old cluster and run the upgrade on it - SELECT
postgis_extensions_upgrade(); - that will bring it up to 2.5.2 and
fix the views so they are compatible with 12.
3)Then do your migration again
*From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org]
*On Behalf Of *Bo Guo
*Sent:* Monday, December 23, 2019 12:47 AM
*To:* postgis-users@lists.osgeo.org
*Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS
Hi, Regina,
After sucessful upgrade to the Dev server, I started upgrading the
Test server which had the following version profile;
select version();
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
and
select postgis_full_version();
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="100" (procs need upgrade for
use with "110") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.2, 08
September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" (core procs from "2.5.1 r17027"
need upgrade) TOPOLOGY (topology procs from "2.5.1 r17027" need
upgrade) RASTER (raster procs from "2.5.1 r17027" need upgrade)
However the pg_upgradecluster command failed for different reason as
shown in the last few lines in the log file. I thought Dev and Test
servers had been identical in terms of OS and Postgres/PostGIS
install. Look like there was some difference...
pg_restore: creating VIEW "postgis.geography_columns"
pg_restore: creating VIEW "postgis.geometry_columns"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 496; 1259 54951 VIEW geometry_columns postgres
pg_restore: error: could not execute query: ERROR: column s.consrc
does not exist
LINE 28: "replace"("split_part"("s"."consrc", ::"text...
^
HINT: Perhaps you meant to reference the column "s.conkey" or the
column "s.conbin".
Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('54953'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('54952'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('54951'::pg_catalog.oid);
CREATE VIEW "postgis"."geometry_columns" AS
SELECT ("current_database"())::character varying(256) AS
"f_table_catalog",
"n"."nspname" AS "f_table_schema",
"c"."relname" AS "f_table_name",
"a"."attname" AS "f_geometry_column",
COALESCE("postgis"."postgis_typmod_dims"("a"."atttypmod"),
"sn"."ndims", 2) AS "coord_dimension",
COALESCE(NULLIF("postgis"."postgis_typmod_srid"("a"."atttypmod"),
0), "sr"."srid", 0) AS "srid",
("replace"("replace"(COALESCE(NULLIF("upper"("postgis"."postgis_typmod_type"("a"."atttypmod")),
'GEOMETRY'::"text"), "st"."$
FROM (("pg_class" "c"
JOIN "pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND
(NOT "a"."attisdropped"
JOIN "pg_namespace" "n" ON (("c"."relnamespace" = "n"."oid")))
JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid")))
LEFT JOIN ( SELECT "s"."connamespace
Re: [postgis-users] PostgreSQL 12 and PostGIS
Hi, Regina,
After sucessful upgrade to the Dev server, I started upgrading the Test
server which had the following version profile;
select version();
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg16.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
and
select postgis_full_version();
POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="100" (procs need upgrade for
use with "110") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.2, 08
September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" (core procs from "2.5.1 r17027"
need upgrade) TOPOLOGY (topology procs from "2.5.1 r17027" need upgrade)
RASTER (raster procs from "2.5.1 r17027" need upgrade)
However the pg_upgradecluster command failed for different reason as
shown in the last few lines in the log file. I thought Dev and Test
servers had been identical in terms of OS and Postgres/PostGIS install.
Look like there was some difference...
pg_restore: creating VIEW "postgis.geography_columns" pg_restore:
creating VIEW "postgis.geometry_columns" pg_restore: while PROCESSING
TOC: pg_restore: from TOC entry 496; 1259 54951 VIEW geometry_columns
postgres pg_restore: error: could not execute query: ERROR: column
s.consrc does not exist LINE 28: "replace"("split_part"("s"."consrc",
::"text... ^ HINT: Perhaps you meant to reference the column
"s.conkey" or the column "s.conbin". Command was: -- For binary upgrade,
must preserve pg_type oid SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('54953'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('54952'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids SELECT
pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('54951'::pg_catalog.oid);
CREATE VIEW "postgis"."geometry_columns" AS SELECT
("current_database"())::character varying(256) AS "f_table_catalog",
"n"."nspname" AS "f_table_schema", "c"."relname" AS "f_table_name",
"a"."attname" AS "f_geometry_column",
COALESCE("postgis"."postgis_typmod_dims"("a"."atttypmod"), "sn"."ndims",
2) AS "coord_dimension",
COALESCE(NULLIF("postgis"."postgis_typmod_srid"("a"."atttypmod"), 0),
"sr"."srid", 0) AS "srid",
("replace"("replace"(COALESCE(NULLIF("upper"("postgis"."postgis_typmod_type"("a"."atttypmod")),
'GEOMETRY'::"text"), "st"."$ FROM (("pg_class" "c" JOIN
"pg_attribute" "a" ON ((("a"."attrelid" = "c"."oid") AND (NOT
"a"."attisdropped" JOIN "pg_namespace" "n" ON (("c"."relnamespace" =
"n"."oid"))) JOIN "pg_type" "t" ON (("a"."atttypid" = "t"."oid"))) LEFT
JOIN ( SELECT "s"."connamespace", "s"."conrelid", "s"."conkey",
"replace"("split_part"("s"."consrc", ::"text", 2), ')'::"text",
''::"text") AS "type" FROM "pg_constraint" "s" WHERE ("s"."consrc" ~~*
'%geometrytype(% = %'::"text")) "st" ON ((("st"."connamespace" =
"n"."oid") AND ("st"."conrel$ LEFT JOIN ( SELECT "s"."connamespace",
"s"."conrelid", "s"."conkey", ("replace"("split_part"("s"."consrc", ' =
'::"text", 2), ')'::"text", ''::"text"))::integer AS "ndims" FROM
"pg_constraint" "s" WHERE ("s"."consrc" ~~* '%ndims(% = %'::"text"))
"sn" ON ((("sn"."connamespace" = "n"."oid") AND ("sn"."conrelid" = "$
LEFT JOIN ( SELECT "s"."connamespace", "s"."conrelid", "s"."conkey",
("replace"("replace"("split_part"("s"."consrc", ' = '::"text", 2),
')'::"text", ''::"text"), '('::"text", ''::"text$ FROM "pg_constraint"
"
Re: [postgis-users] PostgreSQL 12 and PostGIS
Yeh! Thanks you, Regina!
On 12/22/19 8:54 PM, Regina Obe wrote:
Okay I ran into that issue too.
It happens if you have PostGIS not installed in public.
I have the issue ticketed here – I thought I had fixed this already in
3.0.1 (not yet released yet), but evidentally I haven’t or forgot to
close this ticket.
https://trac.osgeo.org/postgis/ticket/4555
The workaround is to do this:
CREATE EXTENSION postgis_raster FROM unpackaged VERSION "3.0.0" SCHEMA
;
*From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org]
*On Behalf Of *Bo Guo
*Sent:* Sunday, December 22, 2019 10:36 PM
*To:* postgis-users@lists.osgeo.org
*Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS
Thanks, Regina! I am running PostgeSQL 12 now!
However, when I ran *SELECT*PostGIS_Extensions_Upgrade(); the second
time, I have the following msg.
NOTICE: Extension postgis_sfcgal is not available or not packagable
for some reason
NOTICE: Packaging extension postgis_raster
WARNING: 'postgis.gdal_datapath' is already set and cannot be changed
until you reconnect
WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be
changed until you reconnect
WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be
changed until you reconnect
ERROR: function public.st_srid(geometry) does not exist
LINE 1: ..._makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0,
public.ST_... ^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT public.ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0,
0, public.ST_SRID('POINT(0 0)'::geometry))
CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"
PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE
SQL state: 42883
On 12/22/19 8:09 PM, Regina Obe wrote:
NO NO don’t do that. Those set of libraries aren’t compatible
with each other.
The PostGIS 3.0 and 2.5 from 12 are compatible with each other
however.
So
What you want to do is
cp /usr/lib/postgresql/12/lib/postgis-3.so
/usr/lib/postgresql/12/lib/postgis-2.5.so
cp /usr/lib/postgresql/12/lib/postgis_raster-3.so
/usr/lib/postgresql/12/lib/rtpostgis-2.5.so
cp /usr/lib/postgresql/12/lib/postgis_topology-3.so
/usr/lib/postgresql/12/lib/postgis_topology-2.5.so
Then after the upgrade in each of your databases run:
*SELECT*PostGIS_Extensions_Upgrade();
And then run it again to rebundle the raster into it’s own extension
*SELECT*PostGIS_Extensions_Upgrade();
Then if you don’t need raster, you can
DROP EXTENSION postgis_raster;
Once you are done upgrading your databases, you can delete thr
*-2.5.so files you created in the 12 cluster.
*From:*postgis-users
[mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Bo Guo
*Sent:* Sunday, December 22, 2019 10:01 PM
*To:* postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org>
*Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS
Thanks, Regina,
I copied the three 2.5 lib files from/usr/lib/postgresql/11/lib to
/usr/lib/postgresql/12/lib:
guob@yuma:/usr/lib/postgresql/12/lib$ ls -al *post*
-rw-r--r-- 1 root root 456832 Dec 22 19:49 postgis-2.5.so
-rw-r--r-- 1 root root 1351536 Oct 28 21:24 postgis-3.so
-rw-r--r-- 1 root root 847424 Oct 28 21:24 postgis_raster-3.so
-rw-r--r-- 1 root root 364224 Dec 22 19:49 postgis_topology-2.5.so
-rw-r--r-- 1 root root 569152 Oct 28 21:24 postgis_topology-3.so
-rw-r--r-- 1 root root 100264 Nov 12 03:33 postgres_fdw.so
-rw-r--r-- 1 root root 399136 Dec 22 19:50 rtpostgis-2.5.so
However, the three lib files are still in the
loadable_libraries.txt file.
Bo
On 12/22/19 7:42 PM, Regina Obe wrote:
Bo,
Do you have the postgis-2.5.so, rtpostgis-2.5.so,
postgis_topology-2.5.so
files in
/usr/lib/postgresql/11/lib
And
/usr/lib/postgresql/12/lib
Those are the files it's complaining about.
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On
Behalf
Of Bo Guo
Sent: Sunday, December 22, 2019 9:16 PM
To:postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS
Regina and Stefan,
I might have run into a similar issues today as I was trying to upgrade
PostgreSQL 11 w/ PostGIS 2.5 to PostgreSQL 12 w/ PostGIS 3.0.
The pg_upgradecluster command (sudo pg_upgradecluster -m upgrade 11
main)
failed its check for the following required libraries:
could not load library "$libdir/postgis-2.5": ERROR: could not
access
Re: [postgis-users] PostgreSQL 12 and PostGIS
Thanks, Regina! I am running PostgeSQL 12 now!
However, when I ran *SELECT*PostGIS_Extensions_Upgrade(); the second
time, I have the following msg.
NOTICE: Extension postgis_sfcgal is not available or not packagable for
some reason
NOTICE: Packaging extension postgis_raster
WARNING: 'postgis.gdal_datapath' is already set and cannot be changed
until you reconnect
WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be
changed until you reconnect
WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be
changed until you reconnect
ERROR: function public.st_srid(geometry) does not exist
LINE 1: ..._makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0,
public.ST_... ^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT public.ST_makeemptyraster($1, $2, $3, $4, $5, -($5), 0, 0,
public.ST_SRID('POINT(0 0)'::geometry))
CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"
PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE
SQL state: 42883
On 12/22/19 8:09 PM, Regina Obe wrote:
NO NO don’t do that. Those set of libraries aren’t compatible with
each other.
The PostGIS 3.0 and 2.5 from 12 are compatible with each other however.
So
What you want to do is
cp /usr/lib/postgresql/12/lib/postgis-3.so
/usr/lib/postgresql/12/lib/postgis-2.5.so
cp /usr/lib/postgresql/12/lib/postgis_raster-3.so
/usr/lib/postgresql/12/lib/rtpostgis-2.5.so
cp /usr/lib/postgresql/12/lib/postgis_topology-3.so
/usr/lib/postgresql/12/lib/postgis_topology-2.5.so
Then after the upgrade in each of your databases run:
*SELECT*PostGIS_Extensions_Upgrade();
And then run it again to rebundle the raster into it’s own extension
*SELECT*PostGIS_Extensions_Upgrade();
Then if you don’t need raster, you can
DROP EXTENSION postgis_raster;
Once you are done upgrading your databases, you can delete thr
*-2.5.so files you created in the 12 cluster.
*From:*postgis-users [mailto:postgis-users-boun...@lists.osgeo.org]
*On Behalf Of *Bo Guo
*Sent:* Sunday, December 22, 2019 10:01 PM
*To:* postgis-users@lists.osgeo.org
*Subject:* Re: [postgis-users] PostgreSQL 12 and PostGIS
Thanks, Regina,
I copied the three 2.5 lib files from/usr/lib/postgresql/11/lib to
/usr/lib/postgresql/12/lib:
guob@yuma:/usr/lib/postgresql/12/lib$ ls -al *post*
-rw-r--r-- 1 root root 456832 Dec 22 19:49 postgis-2.5.so
-rw-r--r-- 1 root root 1351536 Oct 28 21:24 postgis-3.so
-rw-r--r-- 1 root root 847424 Oct 28 21:24 postgis_raster-3.so
-rw-r--r-- 1 root root 364224 Dec 22 19:49 postgis_topology-2.5.so
-rw-r--r-- 1 root root 569152 Oct 28 21:24 postgis_topology-3.so
-rw-r--r-- 1 root root 100264 Nov 12 03:33 postgres_fdw.so
-rw-r--r-- 1 root root 399136 Dec 22 19:50 rtpostgis-2.5.so
However, the three lib files are still in the loadable_libraries.txt file.
Bo
On 12/22/19 7:42 PM, Regina Obe wrote:
Bo,
Do you have the postgis-2.5.so, rtpostgis-2.5.so, postgis_topology-2.5.so
files in
/usr/lib/postgresql/11/lib
And
/usr/lib/postgresql/12/lib
Those are the files it's complaining about.
From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf
Of Bo Guo
Sent: Sunday, December 22, 2019 9:16 PM
To:postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS
Regina and Stefan,
I might have run into a similar issues today as I was trying to upgrade
PostgreSQL 11 w/ PostGIS 2.5 to PostgreSQL 12 w/ PostGIS 3.0.
The pg_upgradecluster command (sudo pg_upgradecluster -m upgrade 11 main)
failed its check for the following required libraries:
could not load library "$libdir/postgis-2.5": ERROR: could not access
file "$libdir/postgis-2.5": No such file or directory
could not load library "$libdir/rtpostgis-2.5": ERROR: could not
access
file "$libdir/rtpostgis-2.5": No such file or directory
could not load library "$libdir/postgis_topology-2.5": ERROR: could
not
access file "$libdir/postgis_topology-2.5": No such file or directory
I have postgis-2.5 in the /usr/share/postgresql/11/contrib/ folder AND
/usr/share/postgresql/12/contrib/ folder. I also have postgis-3.0 under
/usr/share/postgresql/12/contrib/.
Thanks for your help!
Bo
Gistic Research, Inc.
On 10/4/19 8:16 AM, Regina Obe wrote:
Stefan,
Which distro do you get your PostgreSQL 12 from. Im assuming
apt.postgresql.org
If so you should report on their mailing list, as that would be a packaging
bug,not a PostGIS one and no one on the PostGIS project is on the apt team.
https://www.postgresql.org/list/pgsql-pkg-debian/
Thanks,
Regina
From: postgis-u
Re: [postgis-users] PostgreSQL 12 and PostGIS
Thanks, Regina, I copied the three 2.5 lib files from/usr/lib/postgresql/11/lib to /usr/lib/postgresql/12/lib: guob@yuma:/usr/lib/postgresql/12/lib$ ls -al *post* -rw-r--r-- 1 root root 456832 Dec 22 19:49 postgis-2.5.so -rw-r--r-- 1 root root 1351536 Oct 28 21:24 postgis-3.so -rw-r--r-- 1 root root 847424 Oct 28 21:24 postgis_raster-3.so -rw-r--r-- 1 root root 364224 Dec 22 19:49 postgis_topology-2.5.so -rw-r--r-- 1 root root 569152 Oct 28 21:24 postgis_topology-3.so -rw-r--r-- 1 root root 100264 Nov 12 03:33 postgres_fdw.so -rw-r--r-- 1 root root 399136 Dec 22 19:50 rtpostgis-2.5.so However, the three lib files are still in the loadable_libraries.txt file. Bo On 12/22/19 7:42 PM, Regina Obe wrote: Bo, Do you have the postgis-2.5.so, rtpostgis-2.5.so, postgis_topology-2.5.so files in /usr/lib/postgresql/11/lib And /usr/lib/postgresql/12/lib Those are the files it's complaining about. From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Bo Guo Sent: Sunday, December 22, 2019 9:16 PM To: postgis-users@lists.osgeo.org Subject: Re: [postgis-users] PostgreSQL 12 and PostGIS Regina and Stefan, I might have run into a similar issues today as I was trying to upgrade PostgreSQL 11 w/ PostGIS 2.5 to PostgreSQL 12 w/ PostGIS 3.0. The pg_upgradecluster command (sudo pg_upgradecluster -m upgrade 11 main) failed its check for the following required libraries: could not load library "$libdir/postgis-2.5": ERROR: could not access file "$libdir/postgis-2.5": No such file or directory could not load library "$libdir/rtpostgis-2.5": ERROR: could not access file "$libdir/rtpostgis-2.5": No such file or directory could not load library "$libdir/postgis_topology-2.5": ERROR: could not access file "$libdir/postgis_topology-2.5": No such file or directory I have postgis-2.5 in the /usr/share/postgresql/11/contrib/ folder AND /usr/share/postgresql/12/contrib/ folder. I also have postgis-3.0 under /usr/share/postgresql/12/contrib/. Thanks for your help! Bo Gistic Research, Inc. On 10/4/19 8:16 AM, Regina Obe wrote: Stefan, Which distro do you get your PostgreSQL 12 from. Im assuming apt.postgresql.org If so you should report on their mailing list, as that would be a packaging bug,not a PostGIS one and no one on the PostGIS project is on the apt team. https://www.postgresql.org/list/pgsql-pkg-debian/ Thanks, Regina From: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] On Behalf Of Stefan Wolf Sent: Friday, October 4, 2019 6:19 AM To: postgis-users@lists.osgeo.org Subject: [postgis-users] PostgreSQL 12 and PostGIS Today Ive installed the brand new PostgreSQL 12 on Ubuntu 18.04 apt-get install postgis will install PostGIS 2.5.2, but the PATH is wrong: /usr/share/postgresql/11/ So CREATE EXTENSION postgis will fail. Greetings from Berlin -Stefan Wolf- ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users -- Regards, Bo Guo, PhD, PE President Gistic Research, Inc. 2033 E Warner Rd Ste 105 Tempe, AZ 85284 www.gisticinc.com www.youtube.com/linearbench Office: 480-656-9962 Cell: 602-570-4697 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] PostgreSQL 12 and PostGIS
Regina and Stefan, I might have run into a similar issues today as I was trying to upgrade PostgreSQL 11 w/ PostGIS 2.5 to PostgreSQL 12 w/ PostGIS 3.0. The pg_upgradecluster command (sudo pg_upgradecluster -m upgrade 11 main) failed its check for the following required libraries: could not load library "$libdir/postgis-2.5": ERROR: could not access file "$libdir/postgis-2.5": No such file or directory could not load library "$libdir/rtpostgis-2.5": ERROR: could not access file "$libdir/rtpostgis-2.5": No such file or directory could not load library "$libdir/postgis_topology-2.5": ERROR: could not access file "$libdir/postgis_topology-2.5": No such file or directory I have postgis-2.5 in the /usr/share/postgresql/11/contrib/ folder AND /usr/share/postgresql/12/contrib/ folder. I also have postgis-3.0 under /usr/share/postgresql/12/contrib/. Thanks for your help! Bo Gistic Research, Inc. On 10/4/19 8:16 AM, Regina Obe wrote: Stefan, Which distro do you get your PostgreSQL 12 from. I’m assuming apt.postgresql.org If so you should report on their mailing list, as that would be a packaging bug,not a PostGIS one and no one on the PostGIS project is on the apt team. https://www.postgresql.org/list/pgsql-pkg-debian/ Thanks, Regina *From:* postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] *On Behalf Of *Stefan Wolf *Sent:* Friday, October 4, 2019 6:19 AM *To:* postgis-users@lists.osgeo.org *Subject:* [postgis-users] PostgreSQL 12 and PostGIS Today I’ve installed the brand new PostgreSQL 12 on Ubuntu 18.04 “apt-get install postgis” will install PostGIS 2.5.2, but the PATH is wrong: /usr/share/postgresql/11/… So CREATE EXTENSION postgis will fail. Greetings from Berlin -Stefan Wolf- ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Creating my own Geocoder?
Hi there, There is Tiger Geocoder, and there is OSM Geocoder. I am wondering what it will take to create a geocoder that uses my own data sources? Maybe an eaiser route is to push my own data sources into Tiger data frame and then use the Tiger Geocoder? I appreciate any comments and suggestions? Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Intersection does not preserve M value
FME, which sources from GEOS
(https://cdn.safe.com/resources/fme/FMEDesktop_Legal_Notices_2019.0.pdf),
did not handle the measurement with its Clipper transformer until about
two years ago. Wonder if they achieved this through direct update the
GEOS lib or through post-GEOS enhancement.
On 5/6/19 2:53 PM, Martin Davis wrote:
Not exactly the same thing - I believe that ticket is referring to
just the WKB format handling?
I can make a ticket for measure handling by the overlay operations if
there isn't one already.
On Mon, May 6, 2019 at 11:31 AM Darafei "Komяpa" Praliaskouski
mailto:m...@komzpa.net>> wrote:
It's in backlog for 6 years already I believe:
https://trac.osgeo.org/geos/ticket/466
пн, 6 мая 2019, 19:33 карыстальнік Martin Davis
mailto:mtncl...@gmail.com>> напісаў:
It should be possible to post-process GEOS results to
re-populate the M values if present. Linear interpolation
could be used to estimate values at intra-segment points. We
can put this on the list for future development.
On Mon, May 6, 2019 at 4:48 AM Darafei "Komяpa" Praliaskouski
mailto:m...@komzpa.net>> wrote:
Hi,
GEOS operations do not preserve M, but sometimes preserve
Z. You can try
ST_SwapOrdinates(ST_Intersection(ST_SwapOrdinates(geom,
'zm'),...), 'zm').
On Thu, May 2, 2019 at 5:19 AM Bo Guo
mailto:bo@gisticinc.com>> wrote:
Hi there,
I was trying to cut a portion of the LRS route with an
area using
ST_Intersection. However the resulting route no
longer carries measure
value. The behavior is the same for version 9.6 or
the latest version
11. Anyone has any suggestions?
Thanks!
Bo
___
postgis-users mailing list
postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
___
postgis-users mailing list
postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Bo Guo, PhD, PE
Gistic Research, Inc.
2033 E Warner Rd. Ste 105
Tempe, AZ 85284
www.gisticinc.com
Tel: 480-656-9962
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] ST_Intersection does not preserve M value
Hi there, I was trying to cut a portion of the LRS route with an area using ST_Intersection. However the resulting route no longer carries measure value. The behavior is the same for version 9.6 or the latest version 11. Anyone has any suggestions? Thanks! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] [postgis-devel] Request for ideas to enhance the geometry viewer - GSoC
I have two suggestions: 1. allow user to configure base map, which will show up when a selected collection of geometry objects shares the same known coordinate system. 2. mouse-hover tooltip for the coordinate values of a closet vertex near the mouse. Cheers, Bo On 1/29/19 4:53 PM, Regina Obe wrote: The call for OSGeo GSoC ideas is now out [1]. Frikan and I would like to suggest a project to address some of the issues left in the geometry viewer (available in pgAdmin4), but also include some new functionality. Are there any ideas or suggestions from you, the PostGIS community, for extra functionalities that would improve the usefulness of the geometry viewer? Thank you in advance. Regards Victoria [1] https://lists.osgeo.org/pipermail/soc/2019-January/004200.html [Regina Obe] Victoria, Much thanks to all the work you've done with GSoc. The viewer is pretty nice. I think the only thing at the moment I feel missing is being able to over lay queries. I'm not sure if pgAdmin has plumbing to easily allow that. The other thing I had on my dream list -- not really a viewer thing, is having a loader plugin for pgAdmin4. (I thought I had this ticketed, but can't find it) Right now the gui loader/dumper we have shp2pgsql-gui doesn't hook into pgAdmin4 and as such I'm reducing its mention in the PostGIS in Action 3rd edition. What would be really cool is if a replacement plugin could be put in place perhaps using shp2gpsql, pgsql2pgsql instead. I think the pgAdmin4 piggy-backs on pg_dump and pg_restore for its backup/restore console, so was thinking maybe something similar could be done with the Shp2pgsql / pgsql2shp command lines. That would kill 2 birds too - because shp2pgsql-gui is not shipped with many distributions (only the Windows and I think Boundless (now Planet) (Mac) ship it) So we could in theory scrap shp2gpsql-gui if we build the gui into pgAdmin4, and would allow more newbies (not running windows or mac) to be able to use a gui loader/dumper. In addition, if we are successful with that, I think we may able to reuse similar logic to build an ogr2ogr gui into pgAdmin4. Since a lot of folks have GDAL installed in their local desktop they would be able to leverage this as well. Thus solving some other annoying problem we have of not being able to import or export other formats (using a gui). Thanks, Regina ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis upgrade with Postgresql 9.6 to 11 upgrade
Hi all, Today, I tried a Postgresql 10 to Postgresql 11 on our test server (Ubuntu 16.04) where both Postgresql 10 and 11 clusters are running. Steps: 1. Install the Postgis binaries for Postgresql 11 on the server OS: sudo apt install postgresql-11-postgis-2.5 postgresql-11-postgis-scripts 2. Shutdown the postgresql services 3. Drop the Postgresql 11 cluster: sudo pg_dropcluster 11 main 4. Run the cluster upgrade command sudo pg_upgradecluster -m upgrade 10 main 5. Extension upgrade alter extension postgis update; I got "version "2.5.1" of extension "postgis" is already installed" 6. Check postgis version: select postgis_full_version(); And I got: POSTGIS="2.5.1 r17027" [EXTENSION] PGSQL="100" (procs need upgrade for use with "110") GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" TOPOLOGY RASTER Question & Comment 1. Step 6 resulted in procs need upgrade for use with "110" What am I supposed to do with this? 2. It appears that the cluster upgrade updates the extensions as long as the right versions of extension binaries are installed. Thanks! Bo On 1/25/19 9:48 AM, Paul Ramsey wrote: Just run SELECT postgis_full_version() You should see the right version and libraries referenced. P On Jan 25, 2019, at 8:46 AM, Bo Guo <mailto:bo@gisticinc.com>> wrote: OK. Is there a way to validate the success of a Postgis upgrade? On 1/25/19 9:33 AM, Paul Ramsey wrote: You have two options, or maybe less, depending on where you get your PostgreSQL and PostGIS from. If you get them from some packager or other you might not have the freedom to mix-and-match versions. OPTION: Simple And Lots of Steps - Install Pg11 and PostGIS 2.4.4 for Pg11. - Run your PgSQL pg_upgrade process - Install PostGIS 2.5 for Pg11 - Run the PostGIS ‘ALTER EXTENSION postgis UPDATE’ process - Celegrate OPTION: More harder - Install Pg11 and PostGIS 2.5 for Pg11 - Try and run your pg_upgrade - If you’re lucky, it “just works” - If you’re not lucky, you might have to find the Pg11 install of postgis-2.5.so and make a symlink to it from postgis-2.4.so - If you’re not lucky, you might have to track down and remove old versions of geos that are interfering with newer versions Note that in both options, you do have to install a version of PostGIS that has been built against your new database version. The PostGIS 2.4 you already have installed for Pg9.6 is only serving your 9.6 databases, it does nothing for your new Pg11 databases. ATB, P On Jan 25, 2019, at 8:26 AM, Bo Guo wrote: Hi all, We plan to upgrade PostgreSQL 9.6 (PostGIS "2.4.4 r16526") to PostgreSQL 11 in the next couple of weeks. I am just wondering if there is anything I should be concerned about on the side of Postgis, such as will postgis version upgrade automatically? If not, should I upgrade Postgis after the PostgreSQL update?, etc. Thanks! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Postgis upgrade with Postgresql 9.6 to 11 upgrade
OK. Is there a way to validate the success of a Postgis upgrade? On 1/25/19 9:33 AM, Paul Ramsey wrote: You have two options, or maybe less, depending on where you get your PostgreSQL and PostGIS from. If you get them from some packager or other you might not have the freedom to mix-and-match versions. OPTION: Simple And Lots of Steps - Install Pg11 and PostGIS 2.4.4 for Pg11. - Run your PgSQL pg_upgrade process - Install PostGIS 2.5 for Pg11 - Run the PostGIS ‘ALTER EXTENSION postgis UPDATE’ process - Celegrate OPTION: More harder - Install Pg11 and PostGIS 2.5 for Pg11 - Try and run your pg_upgrade - If you’re lucky, it “just works” - If you’re not lucky, you might have to find the Pg11 install of postgis-2.5.so and make a symlink to it from postgis-2.4.so - If you’re not lucky, you might have to track down and remove old versions of geos that are interfering with newer versions Note that in both options, you do have to install a version of PostGIS that has been built against your new database version. The PostGIS 2.4 you already have installed for Pg9.6 is only serving your 9.6 databases, it does nothing for your new Pg11 databases. ATB, P On Jan 25, 2019, at 8:26 AM, Bo Guo wrote: Hi all, We plan to upgrade PostgreSQL 9.6 (PostGIS "2.4.4 r16526") to PostgreSQL 11 in the next couple of weeks. I am just wondering if there is anything I should be concerned about on the side of Postgis, such as will postgis version upgrade automatically? If not, should I upgrade Postgis after the PostgreSQL update?, etc. Thanks! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users -- Bo Guo, PhD, PE Gistic Research, Inc. 2033 E Warner Rd. Ste 105 Tempe, AZ 85284 www.gisticinc.com Tel: 480-656-9962 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] Postgis upgrade with Postgresql 9.6 to 11 upgrade
Hi all, We plan to upgrade PostgreSQL 9.6 (PostGIS "2.4.4 r16526") to PostgreSQL 11 in the next couple of weeks. I am just wondering if there is anything I should be concerned about on the side of Postgis, such as will postgis version upgrade automatically? If not, should I upgrade Postgis after the PostgreSQL update?, etc. Thanks! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] totopogeom resulting in SQL/MM Spatial exception
OK, I can understand the concept why this is the case now. But if there is any example or reference article on line that would be of great help. Bo On 1/16/19 10:37 AM, Sandro Santilli wrote: On Wed, Jan 16, 2019 at 10:26:12AM -0700, Bo Guo wrote: strk, Here is how it break up the topo geom conversion (l_tolerance is 0.001) LOOP WITH foo AS (SELECT grd_id FROM azgiv.roadcenterlines WHERE grd_topo_geom IS NULL LIMIT l_batch_size) UPDATE azgiv.roadcenterlines SET grd_topo_geom = topology.totopogeom(grd_geom, 'azgiv_topo', l_topo_layer_id, l_tolerance) FROM foo WHERE foo.grd_id = roadcenterlines.grd_id; GET DIAGNOSTICS l_rowcount = ROW_COUNT; EXIT WHEN l_rowcount < l_batch_size; END LOOP; The goal of chunking was to get partial results rather than an all-or-nothing behavior. If you use that plpgsql loop you'll want to catch exceptions and set those TopoGeometries to NULL instead. Then you'll be able to see what the loop was able to convert and what not, and get back to the still-to-be-converted geoms, maybe after cleaning up some of what you got converted already. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] totopogeom resulting in SQL/MM Spatial exception
strk, Here is how it break up the topo geom conversion (l_tolerance is 0.001) LOOP WITH foo AS (SELECT grd_id FROM azgiv.roadcenterlines WHERE grd_topo_geom IS NULL LIMIT l_batch_size) UPDATE azgiv.roadcenterlines SET grd_topo_geom = topology.totopogeom(grd_geom, 'azgiv_topo', l_topo_layer_id, l_tolerance) FROM foo WHERE foo.grd_id = roadcenterlines.grd_id; GET DIAGNOSTICS l_rowcount = ROW_COUNT; EXIT WHEN l_rowcount < l_batch_size; END LOOP; I ran into the following message: ERROR: SQL/MM Spatial exception - point not on edge CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) line 112 at FOR over SELECT rows PL/pgSQL function totopogeom(geometry,character varying,integer,double precision) line 88 at assignment Please let me know if I am going in the right direction Bo On 1/16/19 10:03 AM, Sandro Santilli wrote: On Wed, Jan 16, 2019 at 09:53:28AM -0700, Bo Guo wrote: strk, When I set the tolerance to 0.001 and it went through. Is this a good number to use generally? The smaller tolerance, the better, generally, I'd think. But learn how to spot small faces and edges, to clean them up, and how to load in chunks. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] totopogeom resulting in SQL/MM Spatial exception
strk, When I set the tolerance to 0.001 and it went through. Is this a good number to use generally? Bo On 1/16/19 9:14 AM, Sandro Santilli wrote: On Tue, Jan 15, 2019 at 09:48:30AM -0700, Bo Guo wrote: UPDATE azgiv.roadcenterlines SET grd_topo_geom = topology.totopogeom(grd_geom, 'azgiv_topo', l_topo_layer_id, l_tolerance); I am getting the following error similar to below: ERROR: SQL/MM Spatial exception - geometry crosses edge 566760 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) Wonder if someone could shed some light as to what I need to look for? I suggest you try with a smaller tolerance for a start. Variations to tolerance can help in both directions too. Also try to converting to TopoGeometry in chunks rather than all at once, so you can take the chance to clean up small faces between runs. PS: toTopoGeom _does_ split existing edges --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] totopogeom resulting in SQL/MM Spatial exception
strk, I changed from 0.001 to 0.1 and had the similar error. When I leave out he tolerance value all together, I got the following msg: ERROR: SQL/MM Spatial exception - geometry crosses edge 571239 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) line 112 at FOR over SELECT rows PL/pgSQL function totopogeom(geometry,character varying,integer,double precision) line 88 at assignment SQL state: XX000 I will try to break the update into multiple batches Bo On 1/16/19 9:14 AM, Sandro Santilli wrote: On Tue, Jan 15, 2019 at 09:48:30AM -0700, Bo Guo wrote: UPDATE azgiv.roadcenterlines SET grd_topo_geom = topology.totopogeom(grd_geom, 'azgiv_topo', l_topo_layer_id, l_tolerance); I am getting the following error similar to below: ERROR: SQL/MM Spatial exception - geometry crosses edge 566760 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) Wonder if someone could shed some light as to what I need to look for? I suggest you try with a smaller tolerance for a start. Variations to tolerance can help in both directions too. Also try to converting to TopoGeometry in chunks rather than all at once, so you can take the chance to clean up small faces between runs. PS: toTopoGeom _does_ split existing edges --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users -- Bo Guo, PhD, PE Gistic Research, Inc. 2033 E Warner Rd. Ste 105 Tempe, AZ 85284 www.gisticinc.com Tel: 480-656-9962 ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] totopogeom resulting in SQL/MM Spatial exception
Hello there, I am doing a straight update to create topology geom in a table of line geometries: UPDATE azgiv.roadcenterlines SET grd_topo_geom = topology.totopogeom(grd_geom, 'azgiv_topo', l_topo_layer_id, l_tolerance); I am getting the following error similar to below: ERROR: SQL/MM Spatial exception - geometry crosses edge 566760 CONTEXT: PL/pgSQL function totopogeom(geometry,topogeometry,double precision) Wonder if someone could shed some light as to what I need to look for? I am using the following base version: PostgreSQL 9.6.11 on x86_64-pc-linux-gnu (Ubuntu 9.6.11-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit I am using the following Postgis version: POSTGIS="2.4.4 r16526" PGSQL="96" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" (core procs from "2.4.0 r15853" need upgrade) TOPOLOGY (topology procs from "2.5.1 r17027" need upgrade) RASTER (raster procs from "2.4.0 r15853" need upgrade) TIA Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] topology face geometry vs mbr
Thanks, Tumasgiu. On 1/11/19 1:29 AM, Tumasgiu Rossini wrote: Hi, the face table store indeed the mbr of the faces created by your topology. To access it, you can use the st_getfacegeometry function. https://postgis.net/docs/ST_GetFaceGeometry.html Le jeu. 10 janv. 2019 à 22:01, Bo Guo <mailto:bo@gisticinc.com>> a écrit : Hi there, After building topology on my line feature layer, I found the resulting face table with mbrs instead of actual area geometries bounded by the lines. Could someone tell me what I may do next to get the true area geometry? Thanks! -- Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] Slow construction of GiST index, but better with smaller # of big rows
Wenbo, The law of physics is in play here. I think your approach is creative and valid. I wish Postgis offered grid-base spatial index which grouped geometry BBOXs in grids with user defined levels /sizes. A couple of other thoughts: 1. You may also look in to table partitioning to physically breakup the large table. 2. In addition, depend on how your data is used and whether or not the data is static, vector-tiling/cacheing the geometry on disk (out-side of database) may help. Bo On 1/12/19 9:28 AM, Wenbo Tao wrote: Hello, I was trying to build a GiST index on a geometry column in a table with 1 billion rows. It took an entire week to finish. Then I reduced the number of rows by grouping closer objects into one clump (using some clustering algorithm), and then compressed the clump as one row (the geometry column becomes the bounding box of all objects in that clump). The construction then went way faster -- down to 12 hours. I did this because the query I need to answer is finding all objects whose bbox intersects with a given rectangle. I can now query all clumps whose bbox intersects with the rectangle. So essentially, the index construction is slow for too many rows, but much faster for a smaller # of bigger rows. Any intuition why this is the case would be greatly appreciated! Thank you, Wenbo Tao ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] topology face geometry vs mbr
Hi there, After building topology on my line feature layer, I found the resulting face table with mbrs instead of actual area geometries bounded by the lines. Could someone tell me what I may do next to get the true area geometry? Thanks! -- Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
Re: [postgis-users] topology cleaning up
strk, - If I drop the topology, the topology built on "other" jurisdictions in the same street network layer will be gone. My intention is only to update the topology of the jurisdiction whose street network has been changed. - I will give removing constraints a try and report the result. On Wed, Jan 09, 2019 at 07:59:45AM -0700, Bo Guo wrote: Hi there, My street table has road segment geometries from several jurisdictions. The business rule requires me to build topology not for the entire table, but on a jurisdiction-by-jurisdiction basis. I will need to re-generate the topology when geometry updates happen. I can rebuild new topology, but that leaves old topology in the topo tables. So I want to remove the old topology before building new topology using topology.totopogeom for a jurisdiction. However, the constraints in and among the topology tables prevented me from the delete operation. How about using DropTopology to start with a fresh one ? Since the topology is only managed by the postgis' topology engine, can I just remove some of the constraints? This will not only allow deletion and but improve some operations as well? Removing constraints would likely improve some operations, but I'm not clear which constraints are preventing you from creating a whole new topology as your business rule seems to require. --strk; ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
[postgis-users] topology cleaning up
Hi there, My street table has road segment geometries from several jurisdictions. The business rule requires me to build topology not for the entire table, but on a jurisdiction-by-jurisdiction basis. I will need to re-generate the topology when geometry updates happen. I can rebuild new topology, but that leaves old topology in the topo tables. So I want to remove the old topology before building new topology using topology.totopogeom for a jurisdiction. However, the constraints in and among the topology tables prevented me from the delete operation. Since the topology is only managed by the postgis' topology engine, can I just remove some of the constraints? This will not only allow deletion and but improve some operations as well? Thanks! Bo ___ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
