* Python 3.6.3
* PostgreSQL 9.6.5
* PostGIS 2.4.0
* Django 1.11.6
* psycopg2 2.7.3.1
I have a some models that essentially look like this:
{{{
#!python
class Location(models.Model):
name = models.CharField(max_length=255)
# other fields
class LocationBorder(BoundaryBase):
geometry = models.GeometryField()
location = models.ForeignKey(Location, on_delete=models.CASCADE)
}}}
Locations can therefore have multiple borders, and locations can be of
different types (e.g., polygon, multipolygon, point).
What I want to do is pull all location borders that are either polygons or
multipolygons because I need to run an analysis on just those types of
locations. I know all my borders have `geom_type` attributes:
{{{
#!python
> for location_border in LocationBorder.objects.all():
> print(location_border.geometry.geom_type)
Polygon
MultiPolygon
Polygon
Point
...
}}}
So I thought that I could filter on that attribute, but it turns out I
can't:
{{{
#!python
> for location_border in
LocationBorder.objects.filter(Q(geometry__geom_type='Polygon') |
Q(geometry__geom_type='MultiPolygon'))
> print(location_border.geometry.geom_type)
...
django.core.exceptions.FieldError: Unsupported lookup 'geom_type' for
GeometryField or join on the field not permitted.
}}}
I did some searching, and I came across
[https://stackoverflow.com/q/26353830/1269634 this Stack Overflow thread],
which essentially says that it's not possible to do this except by using
`extra()`. That Stack Overflow thread was originally updated 2014, so I
was shocked to see that this still isn't possible. Why is it that I can't
filter on the `geom_type` attribute?
I should mention that using `extra()` ''does'' indeed work for me:
{{{
#!python
> for location_border in
LocationBorder.objects.extra(where=["GeometryType(geometry)='POLYGON' OR
GeometryType(geometry)='MULTIPOLYGON'"]):
> print(location_border.geometry.geom_type)
Polygon
MultiPolygon
Polygon
Polygon
...
}}}
While this works, it's verbose and not very Pythonic/Djangonic.
--
Ticket URL: <https://code.djangoproject.com/ticket/28696>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
* version: 1.11 => master
* stage: Unreviewed => Accepted
Comment:
The reason why you can't filter against `GeometryType` is simply that this
lookup have not been aded yet.
In the mean time you could use the expression API to achieve the same
thing without relying on `extra()`.
{{{#!python
from django.db.models import CharField, Func
class GeometryType(Func):
function = 'GeometryType'
output_field = CharField()
LocationBorder.objects.annotate(
geom_type=GeometryType('geometry'),
).filter(
geom_type__in={'POLYGON', 'MULTIPOLYGON'},
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:1>
Comment (by Geoffrey Fairchild):
Replying to [comment:1 Simon Charette]:
> The reason why you can't filter against `GeometryType` is simply that
this lookup have not been aded yet.
>
> In the mean time you could use the expression API to achieve the same
thing without relying on `extra()`.
>
> {{{#!python
> from django.db.models import CharField, Func
>
> class GeometryType(Func):
> function = 'GeometryType'
> output_field = CharField()
>
> LocationBorder.objects.annotate(
> geom_type=GeometryType('geometry'),
> ).filter(
> geom_type__in={'POLYGON', 'MULTIPOLYGON'},
> )
> }}}
Nice! This is cleaner than what I'm currently doing.
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:2>
Comment (by Simon Charette):
It looks like this could be added on all supported backends by using
`GeometryType` on PostGIS, Spatialite, and MySQLGIS and `SDO_GTYPE`
[https://docs.oracle.com/cd/A97630_01/appdev.920/a96630/sdo_objrelschema.htm#sthref181
on OracleGIS].
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:3>
* status: new => assigned
* owner: nobody => Sergey Fedoseev
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:4>
* owner: Sergey Fedoseev => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:5>
Comment (by Sergey Fedoseev):
`GeometryType` works differently on PostGIS and SpatiaLite for 3d
geometries, `SDO_GTYPE` returns integer, but not string.
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:6>