Search code examples
sqlpostgresqlpostgisgeojson

PostGIS ST_asGeoJSON on specific columns and not all the row columns

I have a table with hundred of columns. I would like to write a single SELECT to get the GeoJSON string returning ONLY specific columns values (geometry and some others fields, but not all the columns values).

If I run:

SELECT ST_AsGeoJSON(d.*)
FROM   mytable d 
WHERE  d.id = 76025);

It works, but if I want only some fields like this:

SELECT ST_AsGeoJSON(d.wkb_geometry, d.info, d.address)
FROM   mytable d 
WHERE  d.id = 76025);

Obviously the St_asGeoJSON doesn't work because it has row as parameter and not a list of fields. How can I get only those 3 columns in the resulting geoJSON WRITING A SINGLE STATEMENT?

Solution

  • The solution is very simple using subquery:

    SELECT ST_AsGeoJSON(p.*) 
FROM   (SELECT d.wkb_geometry, d.info, d.address
        FROM   mytable d 
        WHERE  d.id = 76025) AS p;