django: add GIS support

Question

django: add GIS support

timgraham opened this issue 4 years ago · 14 comments

I'm exploring adding GIS support with CockroachDB 20.2. Here are some missing features as of CockroachDB v20.2.0-alpha.1 (compared to PostGIS) that I ran into so far.

geometry columns can't be part of a unique constraint (or indexed generally, I believe)

SQL: ALTER TABLE "geoapp_uniquetogethermodel" ADD CONSTRAINT "geoapp_uniquetogethermodel_city_point_48b2672e_uniq" UNIQUE ("city", "point")

psycopg2.errors.FeatureNotSupported: unimplemented: column point is of type geometry and thus is not indexable
HINT:  You have attempted to use a feature that is not yet implemented.
See: https://github.com/cockroachdb/cockroach/issues/35730

3D columns not supported

django.db.utils.ProgrammingError: at or near "pointz": syntax error
DETAIL:  source SQL:
CREATE TABLE "geo3d_city3d" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "name" varchar(30) NOT NULL, "point" geometry(POINTZ,4326) NOT NULL, "pointg" geography(POINTZ,4326) NOT NULL)

✅ Negative one SRID not supported

django.db.utils.ProgrammingError: at or near "-": syntax error
DETAIL:  source SQL:
CREATE TABLE "geoapp_minusonesrid" ("id" integer NOT NULL PRIMARY KEY DEFAULT unique_rowid(), "geom" geometry(POINT,-1) NOT NULL)

✅ postgis_lib_version() not supported

Cannot determine PostGIS version for database "test_djangotests" using command "SELECT postgis_lib_version()". GeoDjango requires at least PostGIS version 2.2. Was the database created from a spatial database template?

Django uses this function to enable or disable functionality based on the PostGIS version. Perhaps if you're targeting a certain PostGIS version it might make sense to implement the function, but for now I customized the Django adapter so that it's not needed.

✅ Layer mapping query not supported:

SQL

SELECT "spatial_ref_sys"."srid", "spatial_ref_sys"."auth_name", "spatial_ref_sys"."auth_srid", "spatial_ref_sys"."srtext", "spatial_ref_sys"."proj4text"
FROM "spatial_ref_sys"
WHERE "spatial_ref_sys"."srid" = %s

======================================================================
ERROR: test_model_inheritance (gis_tests.layermap.tests.LayerMapTest)
Tests LayerMapping on inherited models.  See #12093.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: not yet implemented


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/django/contrib/gis/utils/layermapping.py", line 456, in coord_transform
    target_srs = SpatialRefSys.objects.using(self.using).get(srid=self.geo_field.srid).srs
  File "/home/tim/code/django/django/db/models/query.py", line 411, in get
    num = len(clone)
  File "/home/tim/code/django/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1152, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: not yet implemented


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/gis_tests/layermap/tests.py", line 275, in test_model_inheritance
    lm1 = LayerMapping(ICity1, city_shp, icity_mapping)
  File "/home/tim/code/django/django/contrib/gis/utils/layermapping.py", line 124, in __init__
    self.transform = self.coord_transform()
  File "/home/tim/code/django/django/contrib/gis/utils/layermapping.py", line 463, in coord_transform
    ) from exc
django.contrib.gis.utils.layermapping.LayerMapError: Could not translate between the data source and model geometry.

✅ st_geogfromwkb(): wkb: unknown type: 536870913

======================================================================
ERROR: setUpClass (gis_tests.distapp.tests.DistanceFunctionsTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: st_geogfromwkb(): wkb: unknown type: 536870913


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/django/test/testcases.py", line 1143, in setUpClass
    call_command('loaddata', *cls.fixtures, **{'verbosity': 0, 'database': db_name})
  File "/home/tim/code/django/django/core/management/__init__.py", line 168, in call_command
    return command.execute(*args, **defaults)
  File "/home/tim/code/django/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/home/tim/code/django/django/core/management/commands/loaddata.py", line 72, in handle
    self.loaddata(fixture_labels)
  File "/home/tim/code/django/django/core/management/commands/loaddata.py", line 114, in loaddata
    self.load_label(fixture_label)
  File "/home/tim/code/django/django/core/management/commands/loaddata.py", line 181, in load_label
    obj.save(using=self.using)
  File "/home/tim/code/django/django/core/serializers/base.py", line 223, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/home/tim/code/django/django/db/models/base.py", line 784, in save_base
    force_update, using, update_fields,
  File "/home/tim/code/django/django/db/models/base.py", line 887, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/home/tim/code/django/django/db/models/base.py", line 926, in _do_insert
    using=using, raw=raw,
  File "/home/tim/code/django/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/tim/code/django/django/db/models/query.py", line 1204, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1392, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.InternalError: Problem installing fixture '/home/tim/code/django/tests/gis_tests/geogapp/fixtures/initial.json': Could not load geogapp.City(pk=1): st_geogfromwkb(): wkb: unknown type: 536870913

✅ invalid cast: geometry -> bytes

SQL:

SELECT "geoapp_city"."id", "geoapp_city"."name", "geoapp_city"."point"::bytea, ST_AsKML(%s, "geoapp_city"."point", %s) AS "kml"
FROM "geoapp_city"

======================================================================
ERROR: test_geositemap_kml (gis_tests.geoapp.test_sitemaps.GeoSitemapTest)
Tests KML/KMZ geographic sitemaps.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.CannotCoerce: invalid cast: geometry -> bytes


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/tim/code/django/tests/gis_tests/geoapp/test_sitemaps.py", line 44, in test_geositemap_kml
    kml_doc = minidom.parseString(self.client.get(kml_url).content)
  File "/home/tim/code/django/django/test/client.py", line 518, in get
    response = super().get(path, data=data, secure=secure, **extra)
  File "/home/tim/code/django/django/test/client.py", line 346, in get
    **extra,
  File "/home/tim/code/django/django/test/client.py", line 421, in generic
    return self.request(**r)
  File "/home/tim/code/django/django/test/client.py", line 496, in request
    raise exc_value
  File "/home/tim/code/django/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/home/tim/code/django/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/home/tim/code/django/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/home/tim/code/django/django/contrib/gis/sitemaps/views.py", line 54, in kml
    return render('gis/kml/placemarks.kml', {'places': placemarks})
  File "/home/tim/code/django/django/contrib/gis/shortcuts.py", line 27, in render_to_kml
    loader.render_to_string(*args, **kwargs),
  File "/home/tim/code/django/django/template/loader.py", line 62, in render_to_string
    return template.render(context, request)
  File "/home/tim/code/django/django/template/backends/django.py", line 61, in render
    return self.template.render(context)
  File "/home/tim/code/django/django/template/base.py", line 171, in render
    return self._render(context)
  File "/home/tim/code/django/django/test/utils.py", line 95, in instrumented_test_render
    return self.nodelist.render(context)
  File "/home/tim/code/django/django/template/base.py", line 936, in render
    bit = node.render_annotated(context)
  File "/home/tim/code/django/django/template/base.py", line 903, in render_annotated
    return self.render(context)
  File "/home/tim/code/django/django/template/loader_tags.py", line 150, in render
    return compiled_parent._render(context)
  File "/home/tim/code/django/django/test/utils.py", line 95, in instrumented_test_render
    return self.nodelist.render(context)
  File "/home/tim/code/django/django/template/base.py", line 936, in render
    bit = node.render_annotated(context)
  File "/home/tim/code/django/django/template/base.py", line 903, in render_annotated
    return self.render(context)
  File "/home/tim/code/django/django/template/loader_tags.py", line 62, in render
    result = block.nodelist.render(context)
  File "/home/tim/code/django/django/template/base.py", line 936, in render
    bit = node.render_annotated(context)
  File "/home/tim/code/django/django/template/base.py", line 903, in render_annotated
    return self.render(context)
  File "/home/tim/code/django/django/template/defaulttags.py", line 166, in render
    len_values = len(values)
  File "/home/tim/code/django/django/db/models/query.py", line 258, in __len__
    self._fetch_all()
  File "/home/tim/code/django/django/db/models/query.py", line 1261, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/tim/code/django/django/db/models/query.py", line 57, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "/home/tim/code/django/django/db/models/sql/compiler.py", line 1152, in execute_sql
    cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/tim/code/django/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/home/tim/code/django/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/tim/code/django/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: invalid cast: geometry -> bytes

timgraham commented 4 years ago

@otan FYI

Answer 1 · 2020-06-23T01:43:04.000Z

thanks for jumping the gun!

investigating this atm. our blocker is that we don't know whether people need the PK ordering to be the same as PostGIS.
won't be supported for 20.2
fix coming soon
fix coming soon
works on master
~~can you pass the query which causes this?~~ fix coming soon
fix coming soon

Answer 2 · 2020-06-27T23:54:46.000Z

Thanks for the fixes! Some new errors with the CockroachDB nightly:

st_geomfromewkb(): unknown SRID for Geometry: 32140 / 4269

======================================================================
ERROR: setUpClass (gis_tests.distapp.tests.DistanceFunctionsTests)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: st_geomfromewkb(): unknown SRID for Geometry: 32140

and

======================================================================
ERROR: test_null_geom_with_unique (gis_tests.layermap.tests.LayerMapTest)
LayerMapping may be created with a unique and a null geometry.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql, params)
psycopg2.InternalError: st_geomfromewkb(): unknown SRID for Geometry: 4269

unknown signature: st_askml(int, geometry, int)

======================================================================
ERROR: test_geositemap_kml (gis_tests.geoapp.test_sitemaps.GeoSitemapTest)
Tests KML/KMZ geographic sitemaps.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/tim/code/django/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: unknown signature: st_askml(int, geometry, int)

Answer 3 · 2020-06-28T00:28:05.000Z

✅ not sure we're supporting those SRIDs just yet :) we're hardcoding a fixed list atm. are those popular?
yeah we haven't gotten around to ST_AsKML yet (cockroachdb/cockroach#48881 / cockroachdb/cockroach#48384)

Answer 4 · 2020-06-30T17:37:48.000Z

I'm not a GIS user so I'm not sure if those SRIDs are popular. They're used in a handful of tests.

Answer 5 · 2020-07-09T16:58:21.000Z

8 is done.

Answer 6 · 2020-07-09T19:16:01.000Z

Thanks. More issues that now appear:

errors like "st_area(): geos: error during GEOS init: geos: cannot load GEOS from dir "/usr/local/lib/cockroach": failed to execute dlopen"
SRID 2278 not supported. (st_geomfromewkb(): unknown SRID for Geometry: 2278)

After unblocking SRID 2278 (I commented out the relevant fixtures), I see a fair number unknown signature [e.g. st_union(geometry)], unknown function [e.g. st_extent()], and unknown SQL operators (e.g. WHERE "geoapp_city"."point" &< ST_Union("geoapp_city"."point", "geoapp_city"."point")). I imagine it may not be useful for me to enumerate all those errors (at least until you make more progress) but let me know. You can view the test log at https://gist.github.com/timgraham/d4e95ca117a8804140e188607c0a183f.

Answer 7 · 2020-07-09T19:20:55.000Z

ah yeah, you'll need to copy libgeos.so and libgeos_c.so from the cockroachdb build (in the lib folder) to /usr/local/lib/cockroach (the docker container will do this soon, and it will be included in the tar gz)
ok ill add that

yeah unknown signatures will be a bulk of the work i guess, if there's any particularly useful lmk, otherwise i guess that's in the realm of expected.

Answer 8 · 2020-07-15T00:41:58.000Z

not sure about

Traceback (most recent call last):
  File "/home/tim/code/django/tests/gis_tests/geoapp/tests.py", line 430, in test_null_geometries_excluded_in_lookups
    self.assertNotIn(null, State.objects.filter(**{'poly__%s' % lookup: geom}))
  File "/opt/python3.7.0/lib/python3.7/unittest/case.py", line 1110, in assertNotIn
    if member in container:
TypeError: argument of type 'QuerySet' is not iterable

Answer 9 · 2020-09-29T23:57:31.000Z

Progress is going well: master...timgraham:gis

There are issues in cockroachdb/cockroach for most of the remaining work. Is there an issue for operators like &>, &<, <<|, @, <<, >>, etc?

Answer 10 · 2020-11-24T23:26:42.000Z

Pinging @otan on the comment above. Thanks.

Answer 11 · 2020-11-24T23:31:50.000Z

There are issues in cockroachdb/cockroach for most of the remaining work. Is there an issue for operators like &>, &<, <<|, @, <<, >>, etc?

ah, just for @ -- cockroachdb/cockroach#56124

feel free to file an issue for the other ones!

Answer 12 · 2020-11-25T01:53:32.000Z

I think cockroachdb/cockroach#57092, cockroachdb/cockroach#57095, cockroachdb/cockroach#57096, cockroachdb/cockroach#57098, and cockroachdb/cockroach#57099 covers it.

Answer 13 · 2020-11-25T01:59:45.000Z

cheers dude