Django filter geometry given a coordinate

Question

I want to get a row from a postgis table given a coordinate/point. With raw sql I do it with:

SELECT * FROM parcelas 
WHERE fk_area=152 
AND ST_contains(geometry,ST_SetSRID(ST_Point(342884.86705619487, 6539464.45201204),32721));

The query before returns one row.

When I try to do this on django it doesn't return me any row:

from django.contrib.gis.geos import GEOSGeometry
class TestView(APIView):
    def get(self, request, format=None):
        pnt = GEOSGeometry('POINT(342884.86705619487 6539464.45201204)', srid=32721)
        parcelas = Parcelas.objects.filter(fk_area=152,geometry__contains=pnt)

        #Also tried this
        #parcelas = Parcelas.objects.filter(fk_area=pk,geometry__contains='SRID=32721;POINT(342884.86705619487 6539464.45201204)')

        serializer = ParcelasSerializer(parcelas, many=True)
        return Response(serializer.data)

Even with django raw query it fails although in this case it returns me an internal server error (argument 3: class 'TypeError': wrong type):

class TestView(APIView):
        def get(self, request, format=None):
            parcelas = Parcelas.objects.raw('SELECT * FROM parcelas WHERE fk_area=152 AND ST_contains(geometry,ST_SetSRID(ST_Point(342884.86705619487, 6539464.45201204),32721))')
            for p in parcelas:
                #Internal server error
                print(p.id)

        return Response('Test')

My model parcelas look like this:

from django.contrib.gis.db import models

class Parcelas(models.Model):
    id = models.BigAutoField(primary_key=True)
    fk_area = models.ForeignKey(Areas, models.DO_NOTHING, db_column='fk_area')
    geometry = models.GeometryField()

    class Meta:
        managed = False
        db_table = 'parcelas'

I don't know what I'm doing wrongly if someone has any idea.

EDIT:

If I print the raw query that django made:

SELECT "parcelas"."id", "parcelas"."fk_area", "parcelas"."geometry"::bytea FROM "parcelas" WHERE ("parcelas"."fk_area" = 152 AND ST_Contains("parcelas"."geometry", ST_Transform(ST_GeomFromEWKB('\001\001\000\000 \321\177\000\000C\224\335w\223\355\024A\350\303\355\0342\362XA'::bytea), 4326)))

Seems like django is not converting it to the correct srid (32721) but I don't know why

EDIT 2:

If in my model I specify the SRID it works correctly:

class Parcelas(models.Model):
    geometry = models.GeometryField(srid=32721)

The problem is that the SRID can be variable depending on the query the rows have one SRID or another so I don't want to set it to always being one.

If you print the desired object (by filtering it by its `id`, if possible) what does the `geometry` value prints? I'm just guessing, but maybe Django is saving the coordinates values slightly different. — revliscano, Mar 03 '20 at 12:30
It prints "SRID=32721;POLYGON ((342416.32 6539436.85, 342415.73 6539437.1, ..." (it has many points). I think the problem is the SRID django is using the srid 4326 to filter but I dont know how to change it. — David1212k, Mar 03 '20 at 12:42
Okay, so it seems like your problem is doing the filtering like `geometry__contains`. Take a look at [dwithin](https://docs.djangoproject.com/en/3.0/ref/contrib/gis/geoquerysets/#std:fieldlookup-dwithin) to see if that helps — revliscano, Mar 03 '20 at 13:03
I have seen dwithin but it is to filter geometries which are near. In my case I want to filter geometries which are one inside another (point inside polygon). The problem is related with SRID see my edit 2 — David1212k, Mar 03 '20 at 13:16

score 1 · Answer 1 · answered Mar 03 '20 at 12:12

1

Test database is created separately and does not contain the same data as the main application database. Try using pdb and listing all entries inside the parcelas table. Unless TestView means just a mock view for the time being.

Using pdb:

   import pdb, pdb.set_trace()
   Parcelas.objects.all()

answered Mar 03 '20 at 12:12

frozenOne

558
2
8

Yes TestView is a temporal view I made for this example on my local computer that is pointing to the same database as the raw query I made directly in the database – David1212k Mar 03 '20 at 12:18

score 0 · Answer 2 · answered Jul 14 '21 at 11:55

In case the records geometry needs to be compared to a geojson like object one approach is to convert the object to GEOSGeometry and then find the record using .get(), .filter() etc.

For example, in case of an API JSON request payload that contains somewhere in the payload the following field:

"geometry": {
    "type": "Polygon",
    "coordinates": [
        [
                [21.870314, 39.390873],
                [21.871913, 39.39319],
                [21.874029, 39.392443],
                [21.873401, 39.391328],
                [21.873369, 39.391272],
                [21.873314, 39.391171],
                [21.872715, 39.390024],
                [21.870314, 39.390873]
        ]
    ]
}

One can use the following code:

import json

from django.contrib.gis.geos import GEOSGeometry

# Assuming the python dictionary containing the geometry field is geometry_dict
payload_geometry = GEOSGeometry(json.dumps(geometry_dict))

parcel = Parcel.objects.get(geometry=payload_geometry)

Django filter geometry given a coordinate

2 Answers2