1、用SQL查询出geojson格式的空间数据
-- 表:TABLEONE 数据库中的空间字段:geometry
SELECT row_to_json (fc) FROM
(SELECT 'FeatureCollection' AS TYPE, array_to_json ( ARRAY_AGG (f)) AS Feature
FROM
(SELECT 'Feature' AS TYPE, ST_AsGeoJSON ( '数据库中的空间字段geometry' ) :: json AS geometry,
(SELECT row_to_json ( T )
FROM
(SELECT id,
name,
type,
remark,
status,
...,
time) AS T ) AS properties
FROM TABLEONE
WHERE 'geometry' IS NOT NULL
ORDER BY ID ASC ) AS f ) AS fc
2、用SQL查询普通表(不含有空间坐标等数据),结果为json格式
-- 表:TABLEONE
SELECT array_to_json(array_agg(row_to_json(t)))
FROM
(SELECT *
FROM TABLEONE) t;
SELECT array_to_json(array_agg(row_to_json(t)))
FROM
(SELECT id,name,type,remark,status,...,time
FROM TABLEONE) t;