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
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
