Opened 14 months ago

Last modified 11 months ago

#28696 new New feature

Add the ability to natively filter geometries by geom_type

Reported by: Geoffrey Fairchild Owned by:
Component: GIS Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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:

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:

> 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:

> 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 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:

> 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.

Change History (6)

comment:1 Changed 14 months ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 1.11master

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().

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'},
)

comment:2 in reply to:  1 Changed 14 months ago by Geoffrey Fairchild

Nice! This is cleaner than what I'm currently doing.

Last edited 14 months ago by Geoffrey Fairchild (previous) (diff)

comment:3 Changed 14 months ago 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 on OracleGIS.

comment:4 Changed 13 months ago by Sergey Fedoseev

Owner: changed from nobody to Sergey Fedoseev
Status: newassigned

comment:5 Changed 11 months ago by Sergey Fedoseev

Owner: Sergey Fedoseev deleted
Status: assignednew

comment:6 Changed 11 months ago by Sergey Fedoseev

GeometryType works differently on PostGIS and SpatiaLite for 3d geometries, SDO_GTYPE returns integer, but not string.

Note: See TracTickets for help on using tickets.
Back to Top