在下面的文章中,我进行了一些查询,结果很好。然而,当我添加进行坐标转换的行和查询geom
的行时,我在运行web服务时收到以下错误:
Input geometry has unknown (0) SRID
我是Postgis的新手。如何解决此问题?
代码:
query = """ WITH data AS (
SELECT '{featuresArray}'::json AS featuresCollection
)
SELECT gid,geom,type::text,properties::text,
array_to_string(array_agg(x_4326||' '||y_4326 ORDER BY gid),',') AS g4326,
array_to_string(array_agg(x_25832||' '||y_25832 ORDER BY gid),',') AS g25832
FROM (
SELECT
ROW_NUMBER() OVER () AS gid,
ST_AsText(ST_GeomFromGeoJSON(feature->>'geometry')) AS geom,
feature->>'type' AS type,
feature->>'properties' AS properties,
ST_X((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) x_4326,
ST_Y((ST_DumpPoints((ST_GeomFromGeoJSON(feature->>'geometry')))).geom) y_4326,
ST_X((ST_DumpPoints((ST_Transform(ST_GeomFromGeoJSON(feature->>'geometry'),25832)))).geom) x_25832,
ST_X((ST_DumpPoints((ST_Transform(ST_GeomFromGeoJSON(feature->>'geometry'),25832)))).geom) y_25832
FROM (SELECT json_array_elements(featuresCollection->'features') AS feature FROM data) AS f) j
GROUP BY gid,type::text,properties::text,geom
ORDER BY gid;""".format(featuresArray=featuresArray)
一些PostGIS函数依赖于SRS,例如ST_Transform
。您必须指定要从哪个SRS进行转换,否则转换脚本没有计算新坐标的参考,例如从EPSG:25832
到EPSG:4326
:
SELECT ST_Transform('SRID=25832;POINT(1 1)',4326);
否则将引发异常
SELECT ST_Transform('POINT(1 1)',4326); -- <-- WKT literal without SRS
ERROR: ST_Transform: Input geometry has unknown (0) SRID
使用ST_SetSRID
,您可以将SRS设置为几何图形,以防它们没有几何图形——正如您的示例所示,例如
SELECT ST_Transform(
ST_SetSRID('POINT(1 1)'::geometry,25832),
4326);
CREATE TABLE
和INSERT
/UPDATE
语句也适用相同的原理。创建表时,我们声明SRS如下。。
CREATE TABLE t (geom geometry(point,4326));
因此PostGIS希望所有传入的几何图形都具有相同的SRS。。
INSERT INTO t VALUES ('SRID=4326;POINT(1 1)');
否则它也会引发异常
INSERT INTO t VALUES ('SRID=25832;POINT(1 1)');
ERROR: Geometry SRID (25832) does not match column SRID (4326)