CODEWITHSUNDEEP
postgresqlgeolocationgisgeospatialpostgis
fnisi
fnisi

Reputation: 1233

ST_Within does not return true

I have some data imported from a GeoPackage by using the ogr2ogr tool. I assume the import operation goes without any glitches since ST_IsValidReason() returns Valid Geometry for the imported multi-polygons (the geom column).

nzstat=# \d sa2_2020
                                           Table "public.sa2_2020"
     Column      |            Type             | Collation | Nullable |                Default                
-----------------+-----------------------------+-----------+----------+---------------------------------------
 gid             | integer                     |           | not null | nextval('sa2_2020_gid_seq'::regclass)
 sa22018_v1_00   | character varying           |           |          | 
 sa22018_v1_name | character varying           |           |          | 
 land_area_sq_km | double precision            |           |          | 
 area_sq_km      | double precision            |           |          | 
 shape_length    | double precision            |           |          | 
 geom            | geometry(MultiPolygon,2193) |           |          | 
Indexes:
    "sa2_2020_pkey" PRIMARY KEY, btree (gid)
    "sa2_2020_geom_geom_idx" gist (geom)

nzstat=# select ST_IsValidReason(geom) from sa2_2020 where gid=4;
 st_isvalidreason 
------------------
 Valid Geometry
(1 row)

nzstat=# select ST_IsValidReason(ST_SetSRID(ST_MakePoint(174.77632, -41.28671), 2193));
 st_isvalidreason 
------------------
 Valid Geometry
(1 row)

nzstat=# select sa22018_v1_name from sa2_2020 where ST_Within(ST_SetSRID(ST_MakePoint(174.82726, -41.16671), 2193), geom);
 sa22018_v1_name 
-----------------
(0 rows)

nzstat=#

The coordinates I use for ST_MakePoint() are from another database which gives the x and y values in NZGD2000 coordinate system (SRID = 2193)

               full_address_ascii                | gd2000_xcoord | gd2000_ycoord        
-------------------------------------------------+---------------+---------------
 1 Willis Street, Wellington Central, Wellington |     174.77632 |     -41.28671

The coordinate should be in one of the multi-polygons but my query returns no results. Is there anything I am missing here?

I use PostGIS 3.2 with PostgeSQL 13.5.

Thanks

Upvotes: 0

Views: 446

Answers (1)

Ian Turton
Ian Turton

Reputation: 10976

EPSG:2193 has coordinates in metres so it is unlikely that your values (174.82726, -41.16671) are in that projection, it is much more likely that they are in degrees (WGS84, EPGS:4326). So you need to transform your point to compare them to polygons in EPSG:2193, thus your SQL should be something like:

select sa22018_v1_name from sa2_2020 where ST_Within(ST_TRANSFORM(ST_MakePoint(174.82726, -41.16671), 2193), geom);

ST_SetSRID only changes the metadata of the point (setting the CRS), to actually change the values of a point's coordinates you need to reproject the point (transform it from one projection to another) and so must use ST_Transform.

Upvotes: 2

Related Questions