[postgis-users] st_geojson like st_mvt function

[François B]

ogr2ogr command line tool can read from PostgreSQL and write to GeoJSON
FeatureCollection. Is this an option for you? Or do you need to further
process the resulting GeoJSON inside PostgreSQL?

I recently wanted to achieve the same and used ogr2ogr to export a
PostgreSQL table into GeoJSON file. Example command line:

ogr2ogr -f GeoJSON output.json PG:"dbname=my_db" "my_table(geom)"

François


On Tue, Dec 12, 2017 at 10:31 PM, Jorge Gustavo Rocha <jgr at di.uminho.pt>
wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171212/786631f9/attachment.html>