We upgraded AWS RDS hosted 9.3 or something like that to 12. If you are on AWS - I can give you pointers to get thru with. They may or may not help in another environment- so private messaging.
Roxanne Just another DBA/dev… Sent from my iPhone > On Jan 11, 2023, at 2:08 PM, Jeffrey Peacock <[email protected]> wrote: > > > Thank you for the help. > > The v10 DB needs to be upgraded to v14/v15. It has always presented a > problem doing that. Will provide those errors next time I attempt. > > Thanks again. > > --J > > >> On 1/11/23 13:00, [email protected] wrote: >> Send postgis-users mailing list submissions to >> [email protected] >> >> To subscribe or unsubscribe via the World Wide Web, visit >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> or, via email, send a message with subject or body 'help' to >> [email protected] >> >> You can reach the person managing the list at >> [email protected] >> >> When replying, please edit your Subject line so it is more specific >> than "Re: Contents of postgis-users digest..." >> >> >> Today's Topics: >> >> 1. Help with ST_Within query issue... (Jeffrey Peacock) >> 2. Re: Help with ST_Within query issue... (Paul Ramsey) >> 3. Re: Help with ST_Within query issue... (Regina Obe) >> >> >> ---------------------------------------------------------------------- >> >> Message: 1 >> Date: Wed, 11 Jan 2023 09:31:59 -0700 >> From: Jeffrey Peacock <[email protected]> >> To: [email protected] >> Subject: [postgis-users] Help with ST_Within query issue... >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset="utf-8"; Format="flowed" >> >> Looking for some insight into why this is happening (2 examples below). >> >> For Postgresql 10: >> >> psql gsw >> psql (14.6 (Ubuntu 14.6-1.pgdg20.04+1), server 10.23 (Ubuntu >> 10.23-1.pgdg20.04+1)) >> Type "help" for help. >> >> gsw=# \dx >> ???????????????????????????????????????? List of installed extensions >> ?????? Name?????? | Version |?? Schema | Description >> >> ------------------+---------+------------+--------------------------------------------------------------------- >> ?plpgsql????????? | 1.0???? | pg_catalog | PL/pgSQL procedural language >> ?postgis????????? | 3.2.1?? | public???? | PostGIS geometry, >> geography, and raster spatial types and functions >> ?postgis_raster?? | 3.2.1?? | public???? | PostGIS raster types and >> functions >> ?postgis_topology | 3.2.1?? | topology?? | PostGIS topology spatial >> types and functions >> ?postgres_fdw???? | 1.0???? | public???? | foreign-data wrapper for >> remote PostgreSQL servers >> ?tablefunc??????? | 1.0???? | public???? | functions that >> manipulate whole tables, including crosstab >> ?uuid-ossp??????? | 1.0???? | public???? | generate universally >> unique identifiers (UUIDs) >> (7 rows) >> >> gsw=# \d Phenomena_v >> ??????????????????????????? View "public.phenomena_v" >> ??????? Column??????? |?????????? Type?????????? | Collation | >> Nullable | Default >> >> ----------------------+--------------------------+-----------+----------+--------- >> ?id_pk??????????????? | bigint?????????????????? | |????????? | >> ?meta_data_fk???????? | bigint?????????????????? | |????????? | >> ?station_id?????????? | character varying(256)?? | |????????? | >> ?sensor_id??????????? | character varying(256)?? | |????????? | >> ?acquisition_time???? | timestamp with time zone | |????????? | >> ?acquisition_duration | bigint?????????????????? | |????????? | >> ?acquisition_period?? | bigint?????????????????? | |????????? | >> ?point??????????????? | geometry(Point,4326)???? | |????????? | >> ?latitude???????????? | double precision???????? | |????????? | >> ?longitude??????????? | double precision???????? | |????????? | >> ?altitude???????????? | double precision???????? | |????????? | >> ?dimension_id???????? | character varying(256)?? | |????????? | >> ?quantity???????????? | character varying(255)?? | |????????? | >> ?units_id???????????? | character varying(256)?? | |????????? | >> ?created????????????? | timestamp with time zone | |????????? | >> ?modified???????????? | timestamp with time zone | |????????? | >> >> gsw=# SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) >> FROM Phenomena_v AS P >> gsw-# WHERE >> gsw-#???????? P.acquisition_time >= '2023-01-08T20:14:43-0700' >> gsw-#???????? AND P.acquisition_time <= '2023-01-09T20:14:43-0700' >> gsw-#???????? AND ST_Within(ST_Transform(P.Point, 4326), >> ST_GeomFromText('POLYGON((34.67010 -119.53618, >> gsw'# 34.67010 -116.02055, >> gsw'# 32.72798 -116.02055, >> gsw'# 32.72798 -119.53618, >> gsw'# 34.67010 -119.53618))',4326)) >> gsw-# = '1'; >> ERROR:? Unknown geometry type: 1025 - Point >> CONTEXT:? parallel worker >> gsw=# >> >> Postgres 14 (There are points inside the polygon): >> >> psql gsw >> psql (14.6 (Ubuntu 14.6-1.pgdg22.04+1)) >> Type "help" for help. >> >> gsw=# \dx >> List of installed extensions >> ?????? Name?????? | Version | Schema?? | Description >> >> ------------------+---------+------------+--------------------------------------------------------------------- >> ?adminpack??????? | 2.1???? | pg_catalog | administrative functions >> for PostgreSQL >> ?pg_freespacemap? | 1.2???? | public???? | examine the free space >> map (FSM) >> ?pgstattuple????? | 1.5???? | public???? | show tuple-level statistics >> ?plpgsql????????? | 1.0???? | pg_catalog | PL/pgSQL procedural language >> ?postgis????????? | 3.2.1?? | public???? | PostGIS geometry, >> geography, and raster spatial types and functions >> ?postgis_raster?? | 3.2.1?? | public???? | PostGIS raster types and >> functions >> ?postgis_topology | 3.2.1?? | topology?? | PostGIS topology spatial >> types and functions >> ?postgres_fdw???? | 1.1???? | public???? | foreign-data wrapper for >> remote PostgreSQL servers >> (8 rows) >> >> gsw=# \d Phenomena_v >> ??????????????????????????? View "public.phenomena_v" >> ??????? Column??????? | Type?????????? | Collation | Nullable | >> Default >> >> ----------------------+--------------------------+-----------+----------+--------- >> ?id_pk??????????????? | bigint?????????????????? |?????????? >> |????????? | >> ?meta_data_fk???????? | bigint?????????????????? |?????????? >> |????????? | >> ?station_id?????????? | character varying(256)?? |?????????? >> |????????? | >> ?sensor_id??????????? | character varying(256)?? |?????????? >> |????????? | >> ?acquisition_time???? | timestamp with time zone |?????????? >> |????????? | >> ?acquisition_duration | bigint?????????????????? |?????????? >> |????????? | >> ?acquisition_period?? | bigint?????????????????? |?????????? >> |????????? | >> ?point??????????????? | geometry(Point,4326)???? |?????????? >> |????????? | >> ?latitude???????????? | double precision???????? |?????????? >> |????????? | >> ?longitude??????????? | double precision???????? |?????????? >> |????????? | >> ?altitude???????????? | double precision???????? |?????????? >> |????????? | >> ?dimension_id???????? | character varying(256)?? |?????????? >> |????????? | >> ?quantity???????????? | character varying(255)?? |?????????? >> |????????? | >> ?units_id???????????? | character varying(256)?? |?????????? >> |????????? | >> ?created????????????? | timestamp with time zone |?????????? >> |????????? | >> ?modified???????????? | timestamp with time zone |?????????? >> |????????? | >> >> gsw=# SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) >> FROM Phenomena_v AS P >> WHERE >> ??????? P.acquisition_time >= '2023-01-08T20:14:43-0700' >> ??????? AND P.acquisition_time <= '2023-01-09T20:14:43-0700' >> ??????? AND ST_Within(ST_Transform(P.Point, 4326), >> ST_GeomFromText('POLYGON((34.67010 -119.53618, >> 34.67010 -116.02055, >> 32.72798 -116.02055, >> 32.72798 -119.53618, >> 34.67010 -119.53618))',4326)) >> = '1'; >> ?latitude | longitude | altitude | st_astext >> ----------+-----------+----------+----------- >> (0 rows) >> >> gsw=# >> >> Thanks in advance. >> >> --J >> >> -------------- next part -------------- >> An HTML attachment was scrubbed... >> URL: >> <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230111/40fbb315/attachment-0001.htm> >> >> ------------------------------ >> >> Message: 2 >> Date: Wed, 11 Jan 2023 08:35:59 -0800 >> From: Paul Ramsey <[email protected]> >> To: PostGIS Users Discussion <[email protected]> >> Subject: Re: [postgis-users] Help with ST_Within query issue... >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset=us-ascii >> >> The error on pg10 is interesting and worrying, but at a first approximation >> your pg14 problem is that you have reversed the coordinate order in your >> polygon. The order should be longitude/latitude and yours is >> latitude/longitude. >> >> Try >> >> ST_GeomFromText('POLYGON((-119.53618 34.67010 , >> -116.02055 34.67010 , >> -116.02055 32.72798, >> -119.53618 32.72798 , >> -119.53618 34.67010))', 4326) >> >> P. >> >>>> On Jan 11, 2023, at 8:31 AM, Jeffrey Peacock <[email protected]> >>>> wrote: >>> >>> POLYGON((34.67010 -119.53618, >> >> >> ------------------------------ >> >> Message: 3 >> Date: Wed, 11 Jan 2023 11:40:52 -0500 >> From: "Regina Obe" <[email protected]> >> To: "'PostGIS Users Discussion'" <[email protected]> >> Subject: Re: [postgis-users] Help with ST_Within query issue... >> Message-ID: <[email protected]> >> Content-Type: text/plain; charset="utf-8" >> >> Never seen that error before. Does your view reference any foreign tables >> in another database. >> >> My only guess is maybe some confusion with it reading the geometry table >> from another database. >> >> >> Does a query like: >> >> >> SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) >> >> FROM Phenomena_v AS P >> >> LIMIT 10; >> >> >> If that doesn?t work what about >> >> >> SELECT P.latitude, P.longitude, P.altitude >> >> FROM Phenomena_v AS P >> >> LIMIT 10; >> >> >> If you can provide the definition of that view, that would be great. >> >> >> Thanks, >> >> Regina >> >> >> >> From: postgis-users [mailto:[email protected]] On Behalf >> Of Jeffrey Peacock >> Sent: Wednesday, January 11, 2023 11:32 AM >> To: [email protected] >> Subject: [postgis-users] Help with ST_Within query issue... >> >> >> Looking for some insight into why this is happening (2 examples below). >> >> For Postgresql 10: >> >> psql gsw >> psql (14.6 (Ubuntu 14.6-1.pgdg20.04+1), server 10.23 (Ubuntu >> 10.23-1.pgdg20.04+1)) >> Type "help" for help. >> >> gsw=# \dx >> List of installed extensions >> Name | Version | Schema | >> Description >> ------------------+---------+------------+--------------------------------------------------------------------- >> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language >> postgis | 3.2.1 | public | PostGIS geometry, geography, and >> raster spatial types and functions >> postgis_raster | 3.2.1 | public | PostGIS raster types and functions >> postgis_topology | 3.2.1 | topology | PostGIS topology spatial types >> and functions >> postgres_fdw | 1.0 | public | foreign-data wrapper for remote >> PostgreSQL servers >> tablefunc | 1.0 | public | functions that manipulate whole >> tables, including crosstab >> uuid-ossp | 1.0 | public | generate universally unique >> identifiers (UUIDs) >> (7 rows) >> >> gsw=# \d Phenomena_v >> View "public.phenomena_v" >> Column | Type | Collation | Nullable | >> Default >> ----------------------+--------------------------+-----------+----------+--------- >> id_pk | bigint | | | >> meta_data_fk | bigint | | | >> station_id | character varying(256) | | | >> sensor_id | character varying(256) | | | >> acquisition_time | timestamp with time zone | | | >> acquisition_duration | bigint | | | >> acquisition_period | bigint | | | >> point | geometry(Point,4326) | | | >> latitude | double precision | | | >> longitude | double precision | | | >> altitude | double precision | | | >> dimension_id | character varying(256) | | | >> quantity | character varying(255) | | | >> units_id | character varying(256) | | | >> created | timestamp with time zone | | | >> modified | timestamp with time zone | | | >> >> gsw=# SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) FROM >> Phenomena_v AS P >> gsw-# WHERE >> gsw-# P.acquisition_time >= '2023-01-08T20:14:43-0700' >> gsw-# AND P.acquisition_time <= '2023-01-09T20:14:43-0700' >> gsw-# AND ST_Within(ST_Transform(P.Point, 4326), >> ST_GeomFromText('POLYGON((34.67010 -119.53618, >> gsw'# >> 34.67010 -116.02055, >> gsw'# >> 32.72798 -116.02055, >> gsw'# >> 32.72798 -119.53618, >> gsw'# >> 34.67010 -119.53618))',4326)) >> gsw-# >> = '1'; >> ERROR: Unknown geometry type: 1025 - Point >> CONTEXT: parallel worker >> gsw=# >> >> Postgres 14 (There are points inside the polygon): >> >> psql gsw >> psql (14.6 (Ubuntu 14.6-1.pgdg22.04+1)) >> Type "help" for help. >> >> gsw=# \dx >> List of installed extensions >> Name | Version | Schema | >> Description >> ------------------+---------+------------+--------------------------------------------------------------------- >> adminpack | 2.1 | pg_catalog | administrative functions for >> PostgreSQL >> pg_freespacemap | 1.2 | public | examine the free space map (FSM) >> pgstattuple | 1.5 | public | show tuple-level statistics >> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language >> postgis | 3.2.1 | public | PostGIS geometry, geography, and >> raster spatial types and functions >> postgis_raster | 3.2.1 | public | PostGIS raster types and functions >> postgis_topology | 3.2.1 | topology | PostGIS topology spatial types >> and functions >> postgres_fdw | 1.1 | public | foreign-data wrapper for remote >> PostgreSQL servers >> (8 rows) >> >> gsw=# \d Phenomena_v >> View "public.phenomena_v" >> Column | Type | Collation | Nullable | >> Default >> ----------------------+--------------------------+-----------+----------+--------- >> id_pk | bigint | | | >> meta_data_fk | bigint | | | >> station_id | character varying(256) | | | >> sensor_id | character varying(256) | | | >> acquisition_time | timestamp with time zone | | | >> acquisition_duration | bigint | | | >> acquisition_period | bigint | | | >> point | geometry(Point,4326) | | | >> latitude | double precision | | | >> longitude | double precision | | | >> altitude | double precision | | | >> dimension_id | character varying(256) | | | >> quantity | character varying(255) | | | >> units_id | character varying(256) | | | >> created | timestamp with time zone | | | >> modified | timestamp with time zone | | | >> >> gsw=# SELECT P.latitude, P.longitude, P.altitude, ST_AsText(P.point) FROM >> Phenomena_v AS P >> WHERE >> P.acquisition_time >= '2023-01-08T20:14:43-0700' >> AND P.acquisition_time <= '2023-01-09T20:14:43-0700' >> AND ST_Within(ST_Transform(P.Point, 4326), >> ST_GeomFromText('POLYGON((34.67010 -119.53618, >> >> 34.67010 -116.02055, >> >> 32.72798 -116.02055, >> >> 32.72798 -119.53618, >> >> 34.67010 -119.53618))',4326)) >> >> = '1'; >> latitude | longitude | altitude | st_astext >> ----------+-----------+----------+----------- >> (0 rows) >> >> gsw=# >> >> Thanks in advance. >> >> --J >> >> >> -------------- next part -------------- >> An HTML attachment was scrubbed... >> URL: >> <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230111/fb412dc9/attachment-0001.htm> >> >> ------------------------------ >> >> Subject: Digest Footer >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >> >> ------------------------------ >> >> End of postgis-users Digest, Vol 251, Issue 1 >> ********************************************* > > > _______________________________________________ > postgis-users mailing list > [email protected] > https://lists.osgeo.org/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
