Opened 7 years ago
Last modified 3 weeks ago
#28696 assigned New feature
Add the ability to natively filter geometries by geom_type
Reported by: | Geoffrey Fairchild | Owned by: | Ahmed Ibrahim |
---|---|---|---|
Component: | GIS | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
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 (11)
follow-up: 2 comment:1 by , 7 years ago
Triage Stage: | Unreviewed → Accepted |
---|---|
Version: | 1.11 → master |
comment:3 by , 7 years ago
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 by , 7 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:5 by , 7 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
follow-up: 7 comment:6 by , 7 years ago
GeometryType
works differently on PostGIS and SpatiaLite for 3d geometries, SDO_GTYPE
returns integer, but not string.
comment:7 by , 2 months ago
Replying to Sergey Fedoseev:
GeometryType
works differently on PostGIS and SpatiaLite for 3d geometries,SDO_GTYPE
returns integer, but not string.
I'll handle this ticket
Do we have an interface to handle returning different field types per database? or do I need to override as_sql ?
comment:8 by , 2 months ago
Owner: | set to |
---|---|
Status: | new → assigned |
follow-up: 10 comment:9 by , 2 months ago
Ahmed, you'll need to define a specialized transform for this to work similar to the Func
discussed in comment:1. To deal with Oracle you'll have to define an as_oracle
method that uses a CASE/WHEN
to map back the integer return values to the proper string types.
comment:10 by , 2 months ago
Replying to Simon Charette:
Ahmed, you'll need to define a specialized transform for this to work similar to the
Func
discussed in comment:1. To deal with Oracle you'll have to define anas_oracle
method that uses aCASE/WHEN
to map back the integer return values to the proper string types.
Thank you Simon for your valuable information, this helped me, I will be doing the PR soon
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()
.