[Django] #28696: Add the ability to natively filter geometries by geom_type
Django
----------------------------------------------+------------------------
Reporter: Geoffrey Fairchild | Owner: nobody
Type: New feature | Status: new
Component: GIS | Version: 1.11
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
----------------------------------------------+------------------------
First, version information just in case it matters:
* 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.
Django
Reporter: Geoffrey Fairchild | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* 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>
Django
Reporter: Geoffrey Fairchild | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
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>
Django
Reporter: Geoffrey Fairchild | Owner: nobody
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
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>
Django
Reporter: Geoffrey Fairchild | Owner: Sergey
| Fedoseev
Type: New feature | Status: assigned
Component: GIS | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Sergey Fedoseev):
* status: new => assigned
* owner: nobody => Sergey Fedoseev
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:4>
Django
Reporter: Geoffrey Fairchild | Owner: (none)
Type: New feature | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Sergey Fedoseev):
* owner: Sergey Fedoseev => (none)
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/28696#comment:5>
Django
Reporter: Geoffrey Fairchild | Owner: (none)
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
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>