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