美文网首页
PostGIS空间表查询GeoJSON

PostGIS空间表查询GeoJSON

作者: almj | 来源:发表于2020-11-24 12:31 被阅读0次

    PostGIS 提供了非常多的函数可以直接实现空间操作及转换,下面用SQL语句查询空间表的数据并转成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(geom)::json as geometry  --geom表中的空间字段
                , (
                    SELECT
                        row_to_json(t)
                    FROM (
                        SELECT
                           id, user,content
                        ) AS t
                    ) AS properties
            FROM test_table 
        ) AS f
    ) AS fc
    

    查询结果如下:

    {
        "type": "FeatureCollection",
        "features": [
            {
                "type": "feature",
                "geometry": {
                    "type": "Point",
                    "coordinates": [
                        87.6416250523,
                        43.6650995192
                    ]
                },
                "properties": {
                    "id": "6500000182",
                    "user": "王晓波",
                    "content": "温性荒漠草原-小蓬"
                }
            },
            {
                "type": "feature",
                "geometry": {
                    "type": "Point",
                    "coordinates": [
                        87.766879,
                        43.790263
                    ]
                },
                "properties": {
                    "id": "6500000197",
                    "user": "王晓波",
                    "content": "温性草原-禾草,锦鸡儿"
                }
            }
        ]
    }
    

    相关文章

      网友评论

          本文标题:PostGIS空间表查询GeoJSON

          本文链接:https://www.haomeiwen.com/subject/bbsiiktx.html