Django postgresql postgis incorrect spatial lookup

Using Django
1

I have the following model:

class Item(Model):
    lat_lng = PointField(geography=True, null=True)

Item.objects.create(lat_lng=Point(-95.864468, 36.075450))
bbox1 = (-168.3984375, 25.16517336866393, -52.03125, 75.32002523220804)
bbox2 = (-145.1953125, 25.16517336866393, -52.03125, 63.07486569058663)

bbox1 contains bbox2, but Item.objects.filter(lat_lng__coveredby=Polygon.from_bbox(bbox1)) doesn’t return the item, while Item.objects.filter(lat_lng__coveredby=Polygon.from_bbox(bbox2)) returns it.

Am I missing something?

2

So I did some playing around and did recreate what you’ve found.

Additionally,

pb1 = Polygon.from_bbox(bbox1)
pb2 = Polygon.from_bbox(bbox2)

pb1.contains(pb2)
True

pb2.contains(pb1)
False

lat_lng = Point(-95.864468, 36.075450)

pb1.contains(lat_lng)
True

pb2.contains(lat_lng)
True

So yes, something unusual appears to be happening.

3

Further research shows that this looks like it is more a PostgreSQL / postgis issue than Django. I ran the queries directly in psql and got the same results.

4

It looks like that geography=True calculates inclusion differently from geometry. Geometry vs Geography | SQL from the Trenches.

5

Ok, this all makes sense now. With geography = True, it’s calculating a great-circle route instead of a cartesian path.

I plotted the “southern” line boundary of bbox1 and bbox2 on Google Earth along with your designated point in Tulsa, Ok, and yes, those two lines are on each side of the designated point.

(I’m not used to working with bounding boxes being large enough for the difference to be significant. I don’t think I ever work with a square larger than 1 km on a side.)