我使用ogr2ogr
工具从GeoPackage导入了一些数据。我假设导入操作没有任何问题,因为ST_IsValidReason()
为导入的多多边形(geom
列(返回Valid Geometry
。
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=#
我用于ST_MakePoint()
的坐标来自另一个数据库,该数据库给出了NZGD2000坐标系(SRID=2193(中的x
和y
值
full_address_ascii | gd2000_xcoord | gd2000_ycoord
-------------------------------------------------+---------------+---------------
1 Willis Street, Wellington Central, Wellington | 174.77632 | -41.28671
坐标应该在其中一个多边形中,但我的查询没有返回任何结果。这里有我遗漏的东西吗?
我使用PostGIS 3.2和PostgeSQL 13.5。
感谢
EPSG:2193的坐标以米为单位,因此您的值(174.82726,-41.16671(不太可能在该投影中,它们更有可能以度为单位(WGS84,EPGS:4326(。因此,您需要转换您的点,将其与EPSG:2193中的多边形进行比较,因此您的SQL应该类似于:
select sa22018_v1_name from sa2_2020 where ST_Within(ST_TRANSFORM(ST_MakePoint(174.82726, -41.16671), 2193), geom);
ST_SetSRID
只更改点的元数据(设置CRS(,要真正更改点的坐标值,您需要重新投影点(将其从一个投影转换到另一个投影(,因此必须使用ST_Transform
。