Reputation:
I have table with 3 columns:
id|name|geometry
-----------------
1 |xie|geometry
i want to get the geometry in geojson format.
select id,name,ST_AsGEOJSON(geom) from table;
`o/p : `'{"id":"1","name":"xie","type":"Polygon","coordinates"[[[103.349002218076,-4.79402531349065]]]}'`
then i am using row_to_json to combine the columns:
SELECT row_to_json(t) from (SELECT id,name,ST_AsGEOJSON(geom) from table where id=1)t
this command is changing the original output as:
o/p :`'{"id":"1","name":"xie","st_asgeojson":"{\"type\":\"Polygon\",\"coordinates\":[[[102.325742539781,-3.83353755877152]]]}"}'`
but i want to combine the columns without any change in the original output.How can i do that?
Upvotes: 1
Views: 1069
Reputation: 6878
You have two choices:
1) You build a json with the geometry as geojson but expressed as string (without quotes):
SELECT row_to_json(t) FROM (
SELECT id,name,replace(ST_AsGEOJSON(geom),'"','') AS geom
FROM table where id=1
) AS t;
Result:
{"id":1,"name":"xie","geom":"{type:Polygon,coordinates:[[[0,0],[0,1],[1,1],[1,0],[0,0]]]}"}
2) Build a json with a legal geojson geometry. Appending a json to another is unfortunately not really supported (yet) by Postgres. You have to build the Json manually:
SELECT (format(
'{"id":%s,"name":%s,"geom":%s}', to_json(id), to_json(name),st_asgeojson(geom))
)::json from table where id=1;
Result:
{"id":1,"name":"xie","geom":{"type":"Polygon","coordinates":[[[0,0],[0,1],[1,1],[1,0],[0,0]]]}}
Upvotes: 1