Send postgis-users mailing list submissions to
[email protected]
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
[email protected]
You can reach the person managing the list at
[email protected]
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 <[email protected]>
To: [email protected]
Subject: [postgis-users] Help with ST_Within query issue...
Message-ID: <[email protected]>
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
------------------+---------+------------+---------------------------------------------------------------------
?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 <[email protected]>
To: PostGIS Users Discussion <[email protected]>
Subject: Re: [postgis-users] Help with ST_Within query issue...
Message-ID: <[email protected]>
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 <[email protected]> wrote:
POLYGON((34.67010 -119.53618,
------------------------------
Message: 3
Date: Wed, 11 Jan 2023 11:40:52 -0500
From: "Regina Obe" <[email protected]>
To: "'PostGIS Users Discussion'" <[email protected]>
Subject: Re: [postgis-users] Help with ST_Within query issue...
Message-ID: <[email protected]>
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:[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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.osgeo.org/pipermail/postgis-users/attachments/20230111/fb412dc9/attachment-0001.htm>
------------------------------
Subject: Digest Footer
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
------------------------------
End of postgis-users Digest, Vol 251, Issue 1
*********************************************
