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')
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()