postgis中构造geojson

首先注意：

比如现在有两张表，一张population_rural表和一张sichuan表，population_rural表提供属性数据，sichuan表提供地理空间数据

population_rural表内容如下：

现在从population_rural表中查，并将结果表存在名为B的暂存表中

sichuan表内容如下：

现在从sichuan表中查，并将结果表存在名为A的暂存表中

想把geom字段命名为geometry，如下：

接着想把geometry字段中的数据改为json格式，用函数：st_asgeojson（）可以实现：

现在想把A表和B表放在一块，有三种方式：

1：使用on连接

2：使用using连接

可以看到，使用USING(city)，它会自动使用on A.city=B.city，而且结果只会保留一个city。

3：使用natural连接。推荐使用这个，因为这样就可以带入变量了。

NATURAL是USING 的简写形式。

注意：要合并的字段名要相同，否则自动合并后会有各自的两个字段，如下：

然后把查出的数据存到名为C 的暂存表中，如下：

接着想要添加值为Feature的type字段，并且构造properties字段

然后构造geojson外层

最后构造出geojson

最终构造geojson成功的代码：

with A as( select name as city ,st_asgeojson(geom)::json as geometry,center from sichuan),
B as (select city,sum(num) from population_rural GROUP BY city),
C as(select * from A natural inner join B ),
feature as( select 'Feature' as type, geometry, (select json_strip_nulls(row_to_json(fields)) from (select city,center,sum) as fields) as properties from C),
features as(select 'FeatureCollection' as type, array_to_json(array_agg(feature.*)) as features from feature )
select row_to_json(features.*) from features

例2：

现在有一张表，内容如下

with a as( select name,center,st_asgeojson(geom)::json as geometry from sichuan)
select * from a

把它转为geojson：

feature as( select 'Feature' as type, geometry, (select json_strip_nulls(row_to_json(fields)) from (select name,center) as fields) as properties from a),
features as(select 'FeatureCollection' as type, array_to_json(array_agg(feature.*)) as features from feature )
select row_to_json(features.*) from features

综合：

with a as( select name,center,st_asgeojson(geom)::json as geometry from sichuan),
feature as( select 'Feature' as type, geometry, (select
json_strip_nulls(row_to_json(fields)) from (select name,center) as fields) as
properties from a),
features as(select 'FeatureCollection' as type, array_to_json(array_agg(feature.*)) as
features from feature )
select row_to_json(features.*) as geojsondata from features