Executing SQL in Django (raw() manager)

The raw() manager method allows for SQL statements to be executed while returning a Django queryset in response.
This allows for basic direct SQL querying although it pushes you towards using the ORM.

    
    manhattan_subway_stations = NycSubwayStations.objects.raw(
        "SELECT * \
        FROM geo_nycsubwaystations \
        WHERE borough='Manhattan';"
    )
       

Executing SQL in Django (cursor)

With this approach a direct connection to the database is opened. A cursor contains a tuple of the rows returned from the SQL query. There are two options for returning the row(s) - cursor.fetchone() and cursor.fetchall().

The response is a raw tuple of row data with no reference to the field names. In the example below the field names are sought from the cursor and then these are zipped into a dictionary to ease later manipulation. Note the Django documentation above also gives the option to use a namedtuple instead of a dictionary.

NOTE: the geometry fields in this instance are returned as hex and therefore need to be converted into GEOSGeometry objects.

    
    with connection.cursor() as cursor:

        cursor.execute(
            "SELECT * \
            FROM geo_nycsubwaystations \
            WHERE borough='Manhattan';"
        )
        # get the column names 
        columns = [col[0] for col in cursor.description]

        # retrieve data and zip with the column names in a dictionary
        manhattan_subway_stations = [
            dict(zip(columns, row)) for row in cursor.fetchall()
            ]
       

Basic PostGIS - ST_Area()

In this simple test example the ST_Area() function is used to return the area of the census blocks in Manhattan. This returns a single tuple (row) containing the result. Note the division to get the value in acres.

    
    with connection.cursor() as cursor:

        cursor.execute(
            """ 
            SELECT Sum(ST_Area(geom)) / 4047 \
            FROM geo_nyccensusblocks \
            WHERE boroname = 'Manhattan';
            """
        )
        row = cursor.fetchone()
       

Tabulated Data - ST_Length()

In this example ST_Length is used to find the length of the roads in New York grouped by type of road. The response here is a tabulated list of rows with no geospatial data.

    
    SELECT type, Sum(ST_Length(geom)) AS length \
    FROM geo_nycstreets \
    GROUP BY type \
    ORDER BY length DESC;
       

Spatial Join ST_Contains()

This example, drawn from the postgis tutorial, uses a spatial join on the neighbourhoods and subways geometries to to return the specific details (borough and neighbourhood) for the selected station.

    
    SELECT
        subways.name AS subway_name,
        neighbourhoods.name AS neighborhood_name,
        neighbourhoods.boroname AS borough
    FROM geo_nycneighbourhoods AS neighbourhoods
    JOIN geo_nycsubwaystations AS subways
    ON ST_Contains(neighbourhoods.geom, subways.geom)
    WHERE subways.name = 'Broad St';
       

Spatial Query ST_Intersect

In this example the the subway stations table is joined with the neighbourhoods table. A neighbourhood is selected ('Downtown') and then the ST_Intersects function is used to return the subway stations within this neighbourhood.

    
    SELECT
        subways.name AS subway_name,
        subways.geom AS subway_geom
    FROM geo_nycsubwaystations AS subways
    JOIN geo_nycneighbourhoods AS neighbourhoods
    ON ST_Intersects(neighbourhoods.geom, subways.geom)
    WHERE neighbourhoods.name = 'Downtown';