[postgis-users] st_geojson like st_mvt function

[Jorge Gustavo Rocha]

Hi Rémi,

If I understand you requirement, to get a proper GeoJSON
FeatureCollection you need a more complicate query.

I use these kind of queries, using a INNER JOIN, to send data to OpenLayers.

Maybe we could create something like st_asfeaturecollection to return
both the geometries and the properties.

Queries to return a FeatureCollection looks like:

var sql = `SELECT row_to_json(fc) as geojson
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
As features
FROM (
-- query
SELECT 'Feature' As type, ST_AsGeoJSON(lgeom.geom)::json As geometry,
row_to_json(lprop) As properties
FROM sentinel.products As lgeom
INNER JOIN (
SELECT id, uuid, identifier, instrument, product_type, cloud_cov_per,
cloud_shad_per, dark_feat_per, format, gen_qual, high_prob_cloud,
format_correction, sensor_start, sensor_stop, ice_per, cirrus_per,
unc_per, veg_per, water_per, to_download, downloaded, published
FROM sentinel.products
where sensor_start::timestamp::date >= '${sensor_start}' and
sensor_start::timestamp::date <= '${sensor_end}'
) As lprop
ON lgeom.id = lprop.id
-- end of query
) As f )  As fc`;

The output is a well formed FeatureCollection.

I've tried to isolate the proper query between the comments:
-- query
-- end of query

Regards,

J. Gustavo

On 12-12-2017 20:56, Rémi Desgrange wrote:
> Hi,
> 
> this is my first mail on this list, I didn't know if I should have
> posted on dev list or this one. Don't hesitate to correct.
> 
> With postgis 2.4 we now have the possibility to generate mvt format
> directly in SQL, what I really like is the ability to construct a mvt
> response with geometrie + data. The sql result is directly well formated
> and ready to use. When I need to use geojson in my app I need something
> like that :
> 
> { "type": "Feature", 
>   "geometry": 
>       { 
>         "type": "Point", 
>         "coordinates": [125.6, 10.1] 
>       }, 
>       "properties": { "name": "Dinagat Islands" }
>  }
> 
> In order to do that I do :
> SELECT name, st_geojson(geom) as geom FROM mytable
> 
> And then I my code I manipulate "name" to put it in the json under
> "properties" there hack around to directly return geojson, but they're
> hack not actual solutions.
> 
> What I imagine is something like that :
> 
> SELECT st_geojson(x) FROM (SELECT name, geom FROM mytable) x
> 
> the signature would be :
> 
> text St_AsGeoJSON(anyelement set row)
> 
> I do not know if this question has already been asked, I found nothing
> when I googled. Do you think that this is a legitimate feature request ?
> 
> Thanks.
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
> 

J. Gustavo
-- 
Jorge Gustavo Rocha
Departamento de Informática
Universidade do Minho
4710-057 Braga
Tel: +351 253604480
Fax: +351 253604471
Móvel: +351 910333888
skype: nabocudnosor