PostGIS空间表查询GeoJSON

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": "温性草原-禾草，锦鸡儿"
            }
        }
    ]
}