如何从PostgreSQL转换为GeoJSON格式？

Question

我有一个名为“
imposm3_restaurant”的简单表，其列为[id，name，geometry]，我想将这些数据转换为geoJSON，我正在使用此功能

CREATE VIEW imposm3_restaurants_geojson AS SELECT row_to_json(fc) AS geojson FROM 
(SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
As features FROM 
(SELECT 
'Feature' As type, 
ST_AsGeoJSON((lg.geometry),15,0)::json As geometry,
row_to_json((id, name)) As properties
FROM imposm3_restaurants As lg) As f ) As fc;

结果是这样的：

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"LineString","coordinates":[[2615020.47191046,5899232.25158985],[2615034.50527113,5899231.67978097],[2615033.86145338,5899215.4513157],[2615032.35921198,5899215.51938806],[2615031.96732292,5899205.64890158],[2615034.97180572,5899205.51275702],[2615034.36531075,5899190.07397728],[2615018.19522163,5899190.71385561],[2615018.77372453,5899205.40384137],[2615020.47191046,5899205.32215463],[2615020.91045298,5899216.48601561],[2615019.83742341,5899216.52685903],[2615020.47191046,5899232.25158985]]},"properties":{"f1":2719,"f2":"Atelierul de Pizza"}},{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[2615018.19522163,5899190.71385561],[2615018.77372453,5899205.40384137],[2615020.47191046,5899205.32215463],[2615020.91045298,5899216.48601561],[2615019.83742341,5899216.52685903],[2615020.47191046,5899232.25158985],[2615034.50527113,5899231.67978097],[2615033.86145338,5899215.4513157],[2615032.35921198,5899215.51938806],[2615031.96732292,5899205.64890158],[2615034.97180572,5899205.51275702],[2615034.36531075,5899190.07397728],[2615018.19522163,5899190.71385561]]]},"properties":{"f1":2720,"f2":"Atelierul de Pizza"}},{"type":"Feature","geometry":{"type":"LineString","coordinates":[[2624875.82864931,5903443.39761349],[2624897.49451598,5903452.78251964],[2624901.44139867,5903443.67003443],[2624879.78486269,5903434.29875908],[2624875.82864931,5903443.39761349]]},"properties":{"f1":2986,"f2":"Pizza Acrobatica"}},{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[2624875.82864931,5903443.39761349],[2624897.49451598,5903452.78251964],[2624901.44139867,5903443.67003443],[2624879.78486269,5903434.29875908],[2624875.82864931,5903443.39761349]]]},"properties":{"f1":2988,"f2":"Pizza Acrobatica"}},{"type":"Feature","geometry":{"type":"LineString","coordinates":[[2622460.22447654,5904586.41424973],[2622479.10046632,5904587.95362911],[2622480.25747212,5904573.81314552],[2622461.39081303,5904572.26014582],[2622460.22447654,5904586.41424973]]},"properties":{"f1":3248,"f2":"Casa Vikingilor"}},{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[2622460.22447654,5904586.41424973],[2622479.10046632,5904587.95362911],[2622480.25747212,5904573.81314552],[2622461.39081303,5904572.26014582],[2622460.22447654,5904586.41424973]]]},"properties":{"f1":3249,"f2":"Casa Vikingilor"}},{"type":"Feature","geometry":{"type":"LineString","coordinates":[[2625201.09657005,5897608.45120294],[2625224.46062264,5897614.30435379],[2625241.33051365,5897576.653689],[2625213.43174478,5897570.82778714],[2625201.09657005,5897608.45120294]]},"properties":{"f1":6152,"f2":"Silva"}},{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[2625201.09657005,5897608.45120294],[2625224.46062264,5897614.30435379],[2625241.33051365,5897576.653689],[2625213.43174478,5897570.82778714],[2625201.09657005,5897608.45120294]]]},"properties":{"f1":6153,"f2":"Silva"}},{"type":"Feature","geometry":{"type":"LineString","coordinates":[[2622825.25980629,5904372.27967993],[2622826.15555271,5904353.45341631],[2622834.51585268,5904353.1673446],[2622854.22227404,5904346.00193242],[2622860.03529512,5904362.26715407],[2622856.61093118,5904374.66361634],[2622825.25980629,5904372.27967993]]},"properties":{"f1":6322,"f2":"Restaurant Sinaia"}},{"type":"Feature","geometry":{"type":"Polygon","coordinates":[[[2622825.25980629,5904372.27967993],[2622856.61093118,5904374.66361634],[2622860.03529512,5904362.26715407],[2622854.22227404,5904346.00193242],[2622834.51585268,5904353.1673446],[2622826.15555271,5904353.45341631],[2622825.25980629,5904372.27967993]]]},"properties":{"f1":6323,"f2":"Restaurant Sinaia"}}]}

它没有当前的几何图形，您知道功能上有什么问题吗：我使用的是Postgres 9.3和PostGIS 2.2

Answer 1

您的输出是有效的geojson文件，但是使用投影EPSG：3857投影了几何。

您可以在大多数gis桌面应用程序（例如Quantum Gis）中加载数据而不会出现问题。

geojson.io可能仅支持长/纬坐标EPSG：4326，还尝试使用函数St_Transform将几何重新投影到长/纬坐标

更改此行：

ST_AsGeoJSON((lg.geometry),15,0)::json As geometry,

在此：

ST_AsGeoJSON(ST_Transform(lg.geometry, 4326),15,0)::json As geometry,