[GIS] How to use Django to execute PostGIS query to get polygons within four points

geodjangopostgis

Here's a sample PostGIS query I use to get geometries within four points:

SELECT *
FROM myTable
WHERE ST_MakeEnvelope(-97.82381347656252, 30.250444940663296, -97.65901855468752, 30.29595835209862, 4326) && ST_Transform(myTable.geom,4326);

With this query, I can get all rows within those four points. How do I execute this query or similar queries in Django or GeoDjango?

EDIT: I must also note that my geom column is a MultiPolygon type.

Best Answer

There are many ways to go about it, but a straight-forward one is to use Django's connection object:

from django.db import connection

This creates a direct connection to your database. With this, you can either execute direct SQL queries:

cursor = connection.cursor()
bbox = (97.82, 30.25, -97.65, 30.29)
cursor.execute("SELECT * FROM myTable WHERE ST_MakeEnvelope(%s, %s, %s, %s, 4326) && ST_Transform(myTable.geom, 4326)", bbox)
result = cursor.fetchall()

Or you can pass your query to a function using Postgres' plpgsql (calling it, say, fn_grab_polys_bbox), and execute it:

cursor = connection.cursor()
bbox = (97.82, 30.25, -97.65, 30.29)
cursor.callproc('fn_grab_polys_bbox', bbox)
result = cursor.fetchall()

Django's connection object is actually referencing Python's own DB-API, which works with a variety of databases. Hence, if you need to call a function, or a table, from a different database than the one you set for your project, you can even do a specific connection to that and repeat the above steps, the returned objects will be the same.

Related Question