[postgis-users] Help with ST_Within query issue...
Roxanne Reid-Bennett
rox at tara-lu.com
Wed Jan 11 16:34:59 PST 2023
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 <jeffp.actual at gmail.com> 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, postgis-users-request at lists.osgeo.org wrote:
>> Send postgis-users mailing list submissions to
>> postgis-users at lists.osgeo.org
>>
>> 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
>> postgis-users-request at lists.osgeo.org
>>
>> You can reach the person managing the list at
>> postgis-users-owner at lists.osgeo.org
>>
>> 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 <jeffp.actual at gmail.com>
>> To: postgis-users at lists.osgeo.org
>> Subject: [postgis-users] Help with ST_Within query issue...
>> Message-ID: <74bdf03c-ec18-df5c-e02b-a64748547d37 at gmail.com>
>> 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 <pramsey at cleverelephant.ca>
>> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
>> Subject: Re: [postgis-users] Help with ST_Within query issue...
>> Message-ID: <E7A07F21-30AB-4960-9B6D-172980969352 at cleverelephant.ca>
>> 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 <jeffp.actual at gmail.com> wrote:
>>>
>>> POLYGON((34.67010 -119.53618,
>>
>>
>> ------------------------------
>>
>> Message: 3
>> Date: Wed, 11 Jan 2023 11:40:52 -0500
>> From: "Regina Obe" <lr at pcorp.us>
>> To: "'PostGIS Users Discussion'" <postgis-users at lists.osgeo.org>
>> Subject: Re: [postgis-users] Help with ST_Within query issue...
>> Message-ID: <004801d925db$7866d150$693473f0$@pcorp.us>
>> 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:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jeffrey Peacock
>> Sent: Wednesday, January 11, 2023 11:32 AM
>> To: postgis-users at lists.osgeo.org
>> 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
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>>
>> ------------------------------
>>
>> End of postgis-users Digest, Vol 251, Issue 1
>> *********************************************
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list