postgresql - 在 Postgis 中如何从空间表和相关(非空间)表之间的一对多关系构造 Geojson

Question

我有一个医院(点)的空间表，它与一个(非空间)价表(循环、紧急等)具有一对多关系

医院 (id, name, geom);化合价(id, name, valence)

“name”是公共(public)字段。

如何在 PostgreSQL/Postgis 中构建一个有效的 Geojson，其中每个医院(点)可以有一个或多个价数？

我已经尝试过此查询的一些变体，但总是给出“用作表达式的子查询返回的不止一行”的相同错误。

SELECT row_to_json(fc) FROM 
( 
    SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features from

    (
    SELECT 'Feature' As type, ST_AsGeoJSON(lg.geom)::json As geometry, row_to_json

        ((select l from (select
        v.*
                FROM valence v
                inner join hospital lg  on lg."name" =  v."name" ) As l   
        )) As properties
        from hospital as lg)

     As f 
)  As fc;

非常感谢!

Answer 1

通过此查询，“化合价”列表作为属性 properties.valences 中的 json 数组返回

SELECT
  json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
      json_build_object(
        'type', 'Feature',
        'geometry', ST_AsGeoJSON(h.geom)::json,
        'properties', json_build_object(
          'name', h.name,
          'valences', (
            -- Generate json array of "valences":
            SELECT array_to_json(array_agg(v.valence)) 
            FROM valence v 
            WHERE v.name = h.name 
            GROUP BY v.name
          )
        )
      ) 
    )
  ) json
FROM
  hospital h

返回的 GeoJSON 对象根据 http://geojsonlint.com/ 有效