How to convert raw SQL query to gino ORM query?

Question

I have this table in a postgreSQL database with postGIS extension installed and enabled.

                                         Table "public.crime_data"


   Column    |            Type             | Collation | Nullable |                Default                 
-------------|-----------------------------|-----------|----------|----------------------------------------
 id          | integer                     |           | not null | nextval('crime_data_id_seq'::regclass)
 state       | character varying           |           |          | 
 district    | character varying           |           |          | 
 location    | character varying           |           |          | 
 sub_type_id | integer                     |           |          | 
 date_time   | timestamp without time zone |           |          | 
 latitude    | double precision            |           |          | 
 longitude   | double precision            |           |          | 
 geom_point  | geography(Point,4326)       |           |          | 


Indexes:
    "crime_data_pkey" PRIMARY KEY, btree (id)
    "idx_crime_data_geom_point" gist (geom_point)
Foreign-key constraints:
    "crime_data_sub_type_id_fkey" FOREIGN KEY (sub_type_id) REFERENCES sub_type(id)

I am using Sanic web framework and along with it Gino ORM since it's asynchronous.

I am able to write and run raw SQL queries in the command line and also using Gino. I just want to know if it's possible to convert a certain query to ORM syntax.

This is the raw query that is working. This code snippet is inside an async view function and this is returning the expected result.

data_points = await db.status(db.text('''
    SELECT 
        location, 
        sub_type_id, 
        latitude, 
        longitude, 
        date_time
    FROM 
        crime_data
    WHERE 
        ST_Distance(
        geom_point,
        ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)
    ) <= 5 * 1609.34;
'''), {
    'lat': lat,
    'lng': lng,
})

This is my attempt to convert it to an ORM query, which isn't working.

data_points = await CrimeData.query.where(
    geo_func.ST_Distance(
        'geom_point',
        geo_func.ST_SetSRID(
            geo_func.ST_MakePoint(lng, lat),
            4326
        )
    ) <= (5 * 1609.34)
).gino.all()

While trying to run this query and return the response as text, I'm getting this error.

⚠️ 500 — Internal Server Error
parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry

Traceback of __main__ (most recent call last):
InternalServerError: parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry
File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/sanic/app.py, line 973, in handle_request

response = await response

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/backend/services/crime_plot.py, line 30, in test

data_points = await CrimeData.query.where(

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/api.py, line 127, in all

return await self._query.bind.all(self._query, *multiparams, **params)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/engine.py, line 740, in all

return await conn.all(clause, *multiparams, **params)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/engine.py, line 316, in all

return await result.execute()

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/dialects/base.py, line 214, in execute

rows = await cursor.async_execute(

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/gino/dialects/asyncpg.py, line 184, in async_execute

result, stmt = await getattr(conn, "_do_execute")(query, executor, timeout)

File /home/disciple/Documents/Code/MyProject-All/MyProject-Sanic/venv/lib/python3.8/site-packages/asyncpg/connection.py, line 1433, in _do_execute

result = await executor(stmt, None)

File asyncpg/protocol/protocol.pyx, line 196, in bind_execute


InternalServerError: parse error - invalid geometry HINT: "ge" <-- parse error at position 2 within geometry while handling path /crime-plot/test1

I understand the ORM query is a SELECT * and that is fine as long as I actually get results. I don't understand what I'm doing wrong. I'm getting the work done but I just want to make sure that it's possible with the ORM too.

This is the code for the view function incase it's relevant.

@app.route('/test')
async def test(request):
    """
    /test?lng=88.21927070000001&lat=23.9130464
    """
    lat = request.args.get('lat')
    lng = request.args.get('lng')
    if lat and lng:
        lat = float(lat)
        lng = float(lng)

        data_points = ...  # either of the above mentioned queries
        return text(data_points)
    else:
        return text('ERROR: lat or lng value missing')

Answer 1

Since you're using the ORM, you need to use the model class's attribute instead of strings for column names. Change the ORM query to this and it should work.

data_points = await CrimeData.query.where(
    geo_func.ST_Distance(
        CrimeData.geom_point,
        geo_func.ST_SetSRID(
            geo_func.ST_MakePoint(lng, lat),
            4326
        )
    ) <= (5 * 1609.34)
).gino.all()