postgis中构造geojson

野性酷女 2021-07-25 16:22 797阅读 0赞

**目录**

一、单条记录构造geojson

二、多条记录构造geojson

--------------------

首先注意：

1、以下所有命令最好自己手动在Navicat中敲，不要直接粘贴进去。哪怕最简单的select \* from sichuan这行命令，如果直接粘贴进命令行，就会查询失败，而自己手动输入进去就会查询成功。

这个报错其实是空格造成的！！当直接复制SQL命令到Navicat命令行后，虽然空格看上去没什么问题，但实际上并没有空格！这个可以在vscode中看出来。

![20210401153256360.png][]![20210401153411995.png][]

还有的SQL语句复制过来看上去都有小圆点，但还是报错，这是因为这句SQL语句中存在没有小圆点的空格，像下面这种，一定要把它删掉！！！**要把所有没有小圆点的空格都给删掉！！**

![20210401154023608.png][]

2、select \* from 表名  的上面一行千万不要有任何符号，尤其是逗号或者分号

![20200822000736688.png][]

![20210401212959680.png][]

先看一下geojson结构：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70][]

# 一、单条记录构造geojson #

查看表内容：

select * from sichuan

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]

查询name为“成都市”的记录：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2][]

select name,st_asgeojson(geom)::json as geometry from sichuan where name='成都市'

**注意：‘成都市’一定要用单引号，不要用双引号！！！**

构造feature：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3][]

构造features：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4][]

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

构造geojson：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5][]

最后一行，如果想修改字段名，可以这样：select row\_to\_json(features.\*) as geojsondata from features      其中，geojsondata为自己修改的字段名。前端获取到的就是geojsondata，而不是默认的row\_to\_json字段了

# 二、多条记录构造geojson #

查看表内容：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]

select * from sichuan

查询字段name,adcode,geom：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6][]

select name,adcode,st_asgeojson(geom)::json as geometry from sichuan

构造feature：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 7][]

![20200822001209998.png][]

构造features：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 8][]

with a as(select name,adcode,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,adcode) as fields) as properties from a),
    
    features as(select 'FeatureCollection' as type,array_to_json(array_agg(feature.*)) as features from feature)
    
    select * from features

构造geojson：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 9][]

[20210401153256360.png]: https://img-blog.csdnimg.cn/20210401153256360.png
[20210401153411995.png]: /images/20210725/1cb1d9556ccf4e888afa3695da9383e4.png
[20210401154023608.png]: /images/20210725/e551ccc2132044a7a1349c96410dd19b.png
[20200822000736688.png]: /images/20210725/20da92bd06184b838a470aecfce63dec.png
[20210401212959680.png]: /images/20210725/241c1ee318ee4b3a9e3308de5290e65e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70]: /images/20210725/0143477edd9f43228c89e06298c645f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1]: /images/20210725/565e749e61e84a11aece11d3a3ee0706.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 2]: /images/20210725/67448fc052284ae1a201b8e009a824d2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 3]: /images/20210725/00c0fdcd7c3343d68f2d31545f5b880d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 4]: /images/20210725/b6c400e924e849948b0ef0853124f071.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 5]: /images/20210725/c51f41f84bac4f0e91bbdc8257950bf0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 6]: /images/20210725/eed35b8589584671a9fdbd3da5347f6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 7]: /images/20210725/c212718a06f34012b496a3dc8c799133.png
[20200822001209998.png]: /images/20210725/4a174044a1df4fc08c8c40f8906debba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 8]: /images/20210725/b2630158c3614b6c96fe61d24eefc72f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 9]: /images/20210725/0e99d70c0e28417b89c6995b9e8dcd80.png