CODEWITHSUNDEEP
postgresqlpostgisgeojson
user4602735
user4602735

Reputation:

How to combine multiple columns into one in postgresql using row_to_json

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

Answers (1)

Tom-db
Tom-db

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

Related Questions