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

[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                   |          
    |          |
      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.htm>