时间戳

首页 > 解决方案 > 输入几何具有未知 (0) SRID

python - 输入几何具有未知 (0) SRID

问题描述

在下面的帖子中，我做了一些查询，它工作正常。但是，当我添加执行坐标转换的行和查询的行时，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)

标签： pythonpostgresqlpostgis

解决方案

一些 PostGIS 功能依赖于 SRS，例如ST_Transform. 您必须指定要转换的 SRS，否则转换脚本没有参考来计算新坐标，例如 from EPSG:25832to 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_SetSRIDSRS 设置为几何图形，以防它们没有任何几何图形 - 正如您的示例所暗示的那样，例如 .

SELECT ST_Transform(
         ST_SetSRID('POINT(1 1)'::geometry,25832),
         4326);

同样的原则也适用于CREATE TABLEand 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)

推荐阅读