Re: [postgis-users] Help with ST_Within query issue...

2023-01-11 Thread Roxanne Reid-Bennett 
We upgraded AWS RDS hosted 9.3 or something like that to 12.  If you are on AWS 
- I can give you pointers to get thru with.  They may or may not help in 
another environment- so private messaging. 

Roxanne
Just another DBA/dev…

Sent from my iPhone

> On Jan 11, 2023, at 2:08 PM, Jeffrey Peacock  wrote:
> 
> 
> Thank you for the help.
> 
> The v10 DB needs to be upgraded to v14/v15.  It has always presented a 
> problem doing that.  Will provide those errors next time I attempt.
> 
> Thanks again.
> 
> --J
> 
> 
>> On 1/11/23 13:00, postgis-users-requ...@lists.osgeo.org wrote:
>> Send postgis-users mailing list submissions to
>>postgis-users@lists.osgeo.org
>> 
>> 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
>>postgis-users-requ...@lists.osgeo.org
>> 
>> You can reach the person managing the list at
>>postgis-users-ow...@lists.osgeo.org
>> 
>> 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 
>> To: postgis-users@lists.osgeo.org
>> Subject: [postgis-users] Help with ST_Within query issue...
>> Message-ID: <74bdf03c-ec18-df5c-e02b-a64748547...@gmail.com>
>> 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 | |? |
>>

Re: [postgis-users] Help with ST_Within query issue...

2023-01-11 Thread Jeffrey Peacock 


Thank you for the help.

The v10 DB needs to be upgraded to v14/v15.  It has always presented a 
problem doing that.  Will provide those errors next time I attempt.


Thanks again.

--J


On 1/11/23 13:00, postgis-users-requ...@lists.osgeo.org wrote:

Send postgis-users mailing list submissions to
postgis-users@lists.osgeo.org

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
postgis-users-requ...@lists.osgeo.org

You can reach the person managing the list at
postgis-users-ow...@lists.osgeo.org

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 
To: postgis-users@lists.osgeo.org
Subject: [postgis-users] Help with ST_Within query issue...
Message-ID: <74bdf03c-ec18-df5c-e02b-a64748547...@gmail.com>
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
 
--+-++-

Re: [postgis-users] Help with ST_Within query issue...

2023-01-11 Thread Regina Obe 
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 |

Re: [postgis-users] Help with ST_Within query issue...

2023-01-11 Thread Paul Ramsey 
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

[postgis-users] Help with ST_Within query issue...

2023-01-11 Thread Jeffrey Peacock 

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   |