Re: [postgis-users] Help with ST_Within query issue...
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-requ...@lists.osgeo.org wrote:
Send postgis-users mailing list submissions to
postgis-users@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-requ...@lists.osgeo.org
You can reach the person managing the list at
postgis-users-ow...@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
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Help with ST_Within query issue...
Message-ID: <74bdf03c-ec18-df5c-e02b-a64748547...@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
--+-++-
[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 |
