Can't transform geometry to geojson

Question

My problem is this. I'm creating a model for some data.

class Cables(Base):
    __tablename__ = 'cables'

    id = Column(Integer, nullable=False)
    route = Column(Geometry(geometry_type='LINESTRING', srid=4326), nullable=False)

Now, I want to transform such a route to GeoJSON.

Things I've tried

@app.route("/api/cable/<int:id>", methods=['GET'])
def get_cable(id):
    cable = session.query(Cables).filter(Cables.id == id).first()
    return str(geoalchemy2.functions.ST_AsGeoJSON(cable.route))

returns ST_AsGeoJSON(ST_GeomFromEWKB(:ST_GeomFromEWKB_1))

If I change the return:

return geoalchemy2.functions.ST_AsGeoJSON(cable.route)

returns TypeError: 'ST_AsGeoJSON' object is not callable

return str(cable.route)

returns 0102000020e610000002000000b34fd4d9bca351c032e14d5134c240c0d24f8055e0a351c0dedea9f4dcbf40c0 This would signal that I do have a geometry object.

return cable.route

returns TypeError: 'WKBElement' object is not callable

If I print the route type,

print(type(cable.route))

returns

<class 'geoalchemy2.elements.WKBElement'>

I thought it should have returned an object of such class, and not the class itself. I'm baffled at this point and I don't know what I should do now.

Any suggestions?

score 6 · Answer 1 · answered Jan 18 '17 at 15:37

It seems to be that the correct way to call ST_AsGeoJSON is inside a query. For instance,

ruta = session.query(Cables.route.ST_AsGeoJSON()).filter(Cables.id == id).first()

What I ended up doing was installing a new library (shapely) in order to read the hex bytestring and then transform it to a dictionary, as dictionaries can be transformed to json in an easy way.

def ewkb_route_to_dict(self):
    """
    returns the cable's route as a dictionary.
    It uses shapely.wkb.loads. The first argument is the route as a bytestring,
    and the second one (True) is to tell loads that
    the input wkb will be represented as a hex string
    """
    return mapping(shapely.wkb.loads(str(self.route), True))

Then in my to string method:

def __str__(self):
    d = dict()
    ...
    d["route"] = self.ewkb_route_to_dict()
    ...
    return dumps(d)

That will correctly transform the geometry to GeoJSON.

Is it possible to return a name or ID with the geometry? I'm struggling with a similar issue; https://gis.stackexchange.com/questions/297713/geojson-feature-collection — user3342735, Oct 04 '18 at 21:43

Jair Perrut · Answer 2 · 2018-07-27T17:54:48.737

2

I know that this post is old but for those who still have this difficulty it follows my solution:

import ast
from flask.json import jsonify
from sqlalchemy import func

@app.route("/api/cable/<int:id>", methods=['GET'])
def get_cable(id):
    geom = session.query(func.ST_AsGeoJSON(Cables.route)).filter(Cables.id == id).scalar()
    # The code below makes a geojson with CRS.
    # See http://www.postgis.org/docs/ST_AsGeoJSON.html for more details.
    #geom = session.query(func.ST_AsGeoJSON(Cables.route,15,2)).filter(Cables.id == id).scalar()
    return jsonify(ast.literal_eval(geom))

edited Jul 27 '18 at 17:54

answered Jul 27 '18 at 17:17

Jair Perrut

1,360
13
24

Is it possible to return a name or ID with the geometry? I'm struggling with a similar issue; https://gis.stackexchange.com/questions/297713/geojson-feature-collection – user3342735 Oct 04 '18 at 21:45
the `scalar()` will return the first column of the first row, if you want other columns you can use `all()` instead, but in this case will return a list of rows. `session.query(Cables.id, Cables.other_data, func.ST_AsGeoJSON(Cables.route)).filter(Cables.id == id).all()` – Jair Perrut Oct 04 '18 at 22:13
Results are displaying in the console, with 500 internal server error at URL. Any suggestions? – user3342735 Oct 05 '18 at 14:45
Can you paste the exception description? – Jair Perrut Oct 22 '18 at 04:22

Nick K9 · Answer 3 · 2019-04-23T11:42:34.137

In the docs, PostGIS helpfully informs you that:

ST_AsGeoJSON only builds geometry. You need to build the rest of Feature from your Postgres table yourself

Indeed, trying to send the output of ST_AsGeoJSON to a Leaflet layer will fail because it won't be a valid Feature. While the docs provide 25 lines of PostGIS code, using it with SQLAlchemy is unappealing. The easiest way I've found to do this is using a small python library, shapely-geojson.

$ pip install shapely-geojson

And then in your code:

from shapely_geojson import dumps, Feature
from geoalchemy2.shape import to_shape

cable = session.query(Cables).filter(Cables.id == id).first()
feature = Feature(to_shape(cable[0]))
geoJSON = dumps(feature)

Note that the GeoJSON standard changed in 2016 to enforce the right hand rule. Depending on how your line or polygon is structured in your data, and how persnickety the receiving software is, you may need to use geojson_rewind to change the order of the points.

Can't transform geometry to geojson

3 Answers3

Linked