Re: [postgis-users] Help with ST_Within query issue...
?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 --
>&
Re: [postgis-users] Help with ST_Within query issue...
?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
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Help with ST_Within query issue...
Message-ID:
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 wrote:
POLYGON((34.67010 -119.53618,
--
Message: 3
Date: Wed, 11 Jan 2023 11:40:52 -0500
From: "Regina Obe"
To: "'PostGIS Users Discussion'"
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-boun...@lists.osgeo.org] On Behalf Of
Jeffrey Peacock
Sent: Wednesday, January 11, 2023 11:32 AM
To:
Re: [postgis-users] Help with ST_Within query issue...
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-boun...@lists.osgeo.org] On Behalf Of
Jeffrey Peacock
Sent: Wednesday, January 11, 2023 11:32 AM
To: postgis-users@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
Re: [postgis-users] Help with ST_Within query issue...
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 wrote:
>
> POLYGON((34.67010 -119.53618,
___
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
