PostGIS get all ordered features in one feature collection

by Johnczek   Last Updated March 10, 2018 15:22 PM

I have a table of routes, it has some id, geom and come other collumns (desc, owner, ...) I would like to get feature collection GEOJson of nearest 10 routes to some point I have (its center of map I see). Plus I dont want to add routes to collection I have already added (I have array of Indexes of routes I added before).

So it would be like

{
    "type": "FeatureCollection",
    "features": [
        {
            "type": "Feature",
            "id": 3, // Its ID of route ID row
            "geometry": {
                "type": "LineString",
                "coordinates": [
                    [-122.48369693756104, 37.83381888486939],
                    [-122.48348236083984, 37.83317489144141],
                    [-122.48339653015138, 37.83270036637107],
                    [-122.48356819152832, 37.832056363179625],
                    [-122.48404026031496, 37.83114119107971],
                    [-122.48404026031496, 37.83049717427869],
                    [-122.48348236083984, 37.829920943955045],
                    [-122.48356819152832, 37.82954808664175],
                    [-122.48507022857666, 37.82944639795659],
                    [-122.48610019683838, 37.82880236636284],
                    [-122.48695850372314, 37.82931081282506],
                    [-122.48700141906738, 37.83080223556934],
                    [-122.48751640319824, 37.83168351665737],
                    [-122.48803138732912, 37.832158048267786],
                    [-122.48888969421387, 37.83297152392784],
                    [-122.48987674713133, 37.83263257682617],
                    [-122.49043464660643, 37.832937629287755],
                    [-122.49125003814696, 37.832429207817725],
                    [-122.49163627624512, 37.832564787218985],
                    [-122.49223709106445, 37.83337825839438],
                    [-122.49378204345702, 37.83368330777276]
                ]
            },
            "properties": {
                "time": [
                    "2012-10-24T23:22:51.000Z"
                ]
            }
        },
        { some other feature },
         { some other feature }
    ]
}

Is this possible to do it via SQL? Or I have to get closest route, store it as a feture and add it to some array in PHP, then load another one feature... and at the end make JSON Feature collection? I know its possible to do it via PHP but I wonder If there is any possibility to do it via SQL query.



Related Questions


Help serializing a postGIS query to geojson

Updated June 14, 2015 22:09 PM


How to convert from PostgreSQL to GeoJSON format

Updated February 22, 2017 03:22 AM

json to postgis stored in csv

Updated April 20, 2017 16:22 PM

ST_ForcePolygonCCW does not exist

Updated November 16, 2017 15:22 PM


Cache file /home/queryxchang/public_html/apps/frontend/config/../cache/-q-23-274314-postgis-get-all-ordered-features-in-one-feature-collection- could not be written