[postgis-users] Help with ST_Within query issue...
Regina Obe
lr at pcorp.us
Wed Jan 11 08:40:52 PST 2023
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.htm>
More information about the postgis-users
mailing list