postgresql - 从 postgis 中的查询插入表单

Question

我尝试通过查询在我的表 geofield 中插入一个多边形。表 geofield 定义如下:

CREATE TABLE geofield(id SERIAL PRIMARY KEY, field GEOMETRY);

insert into geofield(field) values (myfield)
SELECT
    'POLYGON(('||cast((select lat from coordinates where id=1)as varchar)||' '||
      cast((select lon from coordinates where id=1)as varchar)||','||
      cast((select lat from coordinates where id=2)as varchar)||' '||
      cast((select lon from coordinates where id=2)as varchar)||','||
      cast((select lat from coordinates where id=3)as varchar)||' '||
      cast((select lon from coordinates where id=3)as varchar)||','||
      cast((select lat from coordinates where id=4)as varchar)||' '||
      cast((select lon from coordinates where id=4)as varchar)||','||
      cast((select lat from coordinates where id=1)as varchar)||' '||
      cast((select lon from coordinates where id=1)as varchar)||'))') AS myfield;

SELECT 单独返回我:

POLYGON((46.744628268759314 6.569952920654968,46.74441692818192 6.570487107359068,46.74426116111054 6.570355867853787,46.74447250168793 6.569821681149689,46.744628268759314 6.569952920654968))

但它不起作用，我也尝试使用 ST_GeomFromText 但结果相同。

Answer 1

我会避免使用文本连接并使用普通的 PostGIS functions相反:

SELECT
    /* Make a polygon from the linestring */
    ST_MakePolygon(
        /* Close the polygon */
        ST_AddPoint(
            t.linestring,
            ST_StartPoint(t.linestring)
        )
    )
FROM (
    SELECT
        /* Aggregate into a linestring */
        ST_MakeLine(ST_MakePoint(c.lon, c.lat) ORDER BY c.id) AS linestring
    FROM coordinates AS c
) t
;

关注您的 prior question ，如果你愿意，你可以直接从 json 中取回它:

SELECT
    /* Make a polygon from the linestring */
    ST_MakePolygon(
        /* Close the polygon */
        ST_AddPoint(
            t.linestring,
            ST_StartPoint(t.linestring)
        )
    )
FROM (
    SELECT
        /* Aggregate into a linestring */
        ST_MakeLine(ST_MakePoint(c.lon, c.lat) ORDER BY c.id) AS linestring
    FROM
    (
        SELECT
            e.id,
            (e.element->>'lat')::numeric AS lat,
            (e.element->>'lon')::numeric AS lon
        FROM
            field AS f,
            json_array_elements(f.data->'vertices') WITH ORDINALITY AS e(element, id)
    ) AS c
) t
;