[postgis-users] Help with ST_Within query issue...
Jeffrey Peacock
jeffp.actual at gmail.com
Wed Jan 11 14:08:03 PST 2023
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
> *********************************************
More information about the postgis-users
mailing list