Postgis 2.0 Compatibility

[Christian Karrié]

Please add compatibility for Postgis 2.0.

The current version has problems creating indexes and geospatial columns. For example, syncdb gives errors like:
operator class "gist_geometry_ops" does not exist for access method "gist".

Mainly, I'm using Postgis 2.0 because of the excellent split-Function.

Using Postgresql 8.4.

Thank you very much.

[Christian Karrié]

Error translation: ERROR: operator class "gist_geometry_ops" does not accept access method "gist"

[Christian Karrié]

geometry_columns is now a "View" and not a "Table".

[Christian Karrié]

I discovered that Django doesn't create the indexes as well as the geometry columns.

My workaround "by hand":

SELECT AddGeometryColumn('public','bzs_analyze_undevpotential','the_geom',21781,'MULTIPOLYGON',2);
CREATE INDEX bzs_analyze_undevpotential_the_geom
    ON bzs_analyze_undevpotential 
    USING GIST ( the_geom );

then

VACUUM ANALYZE bzs_analyze_undevpotential;

works perfect so far.

It seems, that the geometry colums can be added with the AddGeometryColumn-Function and with following SQL-Statement:

CREATE TABLE bzs_analyze_undevpotential
(
  [...]
  the_geom geometry(MultiPolygon,21781),
  [...]
);

I hope that helps!

[Christian Karrié]

Version 1 of my patch

[Christian Karrié]

Here is my patch. I have tested with postgis 2.0. Could somebody please verify this with 1.x?

Added a new method to django/contrib/gis/db/backends/postgis/introspection.py to detect the postgis version.

[Christian Karrié]

To clearify the differece between PostGIS 1.x and 2.0: only the "CREATE INDEX"-Part has changed

1.x:

  CREATE INDEX ogna_singlepoint_geom_id
  ON ogna_singlepoint
  USING gist
  (geom GIST_GEOMETRY_OPS); 

2.0:

  CREATE INDEX ogna_singlepoint_geom_id
  ON ogna_singlepoint
  USING gist
  (geom); 

[Christian Karrié]

Just discovered, that there is already a method to detect the postgis version, so here is my updated patch.

[Christian Karrié]

Strange. I just re-run syncdb on a fresh database and discovered following problem:

Installing index for ogna.Project model
Failed to install index for ogna.Project model: cursor already closed
Installing index for ogna.Order model
Failed to install index for ogna.Order model: cursor already closed
[..]
Installing index for ogna.Rasterdata model
Failed to install index for ogna.Rasterdata model: cursor already closed
Installing index for ogna.DeviceSensor model
Failed to install index for ogna.DeviceSensor model: cursor already closed

So here is my updated patch for [16722]. It's quite hard-coded. Maybe somebody have a better solution ;-)

Edit: Patch works with [16840].

[Jacob]

Milestone 1.4 deleted

[cmutel@…]

Note that AddGeometryColumn behaviour has ​changed as well, which the attached patch does not address. At the very least, an additional argument is needed (use_typmod, see PosGIS manual). There are probably other changes as well (though no other issues have come up for me).

[anonymous]

BTW - you don't need to pass in that additional argument unless you want to. If you don't pass it in, the geometries are created using typmod qualifiers which is the preferred in PostGIS 2.0. Also for the index, just drop the GIST_GEOMETRY_OPS and it will work just dandy for both PostGIS 2.0 and prior versions of PostGIS. You don't need to state it since its the default for older versions of PostGIS.

[Christian Karrié]

New path vor [17171]

[Christian Karrié]

New Patch works with Postgis 1.5 and Postgis 2.0 and Rev [17171].

Could someone write some tests to verify this? I have no experiences with writing and running tests.

[anonymous]

So is it fixed in 1.4? I still get this error with PostgreSQL 9.1 + PostGIS 2.0.0 + Django 1.4

[Anssi Kääriäinen]

I don't think it is fixed.

As for tests I don't know if there is anything to test. Maybe one could test if the index creation does not contain the GEOM_OPS definition? Otherwise, the test is "does it run on 2.0".

I will need to install PostGIS anyways for the db schema support feature, so I could take care of this. However, the fix will not go into 1.4.

[Anssi Kääriäinen]

I believe the patch does not work on 1.5. To me it seems some logic is needed to detect the version of PostGIS, and depending on that add/drop the GEOM_OPS definition.

The docs contain a lot of installation instructions, and they are pretty useful, so docs changes of how to install PostGIS 2.0 should be included (is it just create extension postgis;?).

For me to review this: Is it possible to install both PostGIS 2 and 1.5 on the same DB? Any instructions of how to do that?

[zmiller1]

It is not working with 1.4, I made the patch changes to creation.py in the PostGIS backend and was able to create the table in the GeoDjango tutorial on PostGIS 2.0. I have not tried it but according to this post you can run both versions on the same db:

​http://postgis.17.n6.nabble.com/How-do-you-install-multiple-versions-of-Postgis-on-a-single-database-cluster-td3554707.html

[anonymous]

The patches above are a good start, but note that with PostGIS 2, the preferred method for enabling PostGIS for a database is to create postgis and postgis_topology extensions within the database rather than creating a separate PostGIS template database. However, the existing Django 1.4 code assumes the presence of a template database (named by the POSTGIS_TEMPLATE setting). To complicate this further, PostGIS 2 still supports the old PostGIS template database approach as well. So any patch to add full PostGIS 2 support to Django should support both the template database configuration and the PostGIS extension configuration.

[anonymous]

v4 patch works for me!

[Flavio Curella]

I've uploaded a simpler patch to support at least the template database approach.

I've opened a pull request in github at :
​https://github.com/django/django/pull/249

[Flavio Curella]

doc draft.

[Flavio Curella]

I've also attached a draft for the doc changes. I'm not a native English speaker, so feel free to correct me.

[anonymous]

be careful with >= 2.0, could you check with latest 2.0svn version? Because there are people who tells that Django is working without patching on fresh PostGIS 2.0svn builds

[anonymous]

Also do you really need this (postgis_version = self.connection.ops.spatial_version) every time running even if f is not an instance of GeometryField or f.geography is True?

could this be a better way?

if f.geography or self.connection.ops.spatial_version >= (2, 0):
    index_opts = ''
else:
    ....

?

The main idea here is not to do extra actions until it's really needed :)

[Flavio Curella]

be careful with >= 2.0, could you check with latest 2.0svn version? Because there are people who tells that Django is working without patching on fresh PostGIS 2.0svn builds

I just tested PostGIS 2.0.0, 2.0.2SVN and 2.1.0SVN and they all seem to require the patch.

[Bryce Groff]

Replying to fcurella:

I've also attached a draft for the doc changes. I'm not a native English speaker, so feel free to correct me.

On line 522, PistGIS => PostGIS

[Flavio Curella]

Replying to brycegroff:

On line 522, PistGIS => PostGIS

Thanks, I've updated patch v6.

[anonymous]

can you help me with a tip how to make rebase (in comandline) for my branch of forked repo from github? thanks

[anonymous]

Also it looks like _nd index is not so great for most users :/

​http://blog.opengeo.org/2012/03/13/postgis-2-0-new-features-3d4d-indexing/

[Flavio Curella]

removed multidimensional index (gist_geometry_ops_nd)

[anonymous]

nice, thanks! is it possible that this patch will be committed into master branch?

[anonymous]

are you sure that this patch re-ADDs something? looks like there is only delition

[Flavio Curella]

D'oh! I've DIFF'd the wrong way. Replaced v8 with the correct patch.

[Flavio Curella]

ref: thread on django-dev ​https://groups.google.com/d/topic/django-developers/m9NjmlvHjfw/discussion

[Flavio Curella]

re-added multidimensional index, but this time only for multidimensional geometries

[anonymous]

used the code above to create a postgis backend for django-hstore

​https://github.com/aino/django-hstore/pull/5/files

[Anssi Kääriäinen]

The patch wasn't commit ready, I have polished it somewhat, but it still needs some more polish:

• the note for versions prior to 9.1 isn't correct, you need to do create extension even in 9.1, and the actual differentiator isn't PostgreSQL version, it is PostGIS version
• the CREATE EXTENSION docs will not build properly
• it seems one needs to install GDAL before installing PostGIS 2.0.

I also needed to install libxml2-dev, but I am not sure if we are trying to track all dependencies of postgis.

Once I got Postgis 2.0 installed, the tests pass just like on 1.5. There are three test failures using postgis, but it seems the failures are old ones. I am now trying to track the existing failures.

I did already some updates to the patch: ​https://github.com/akaariai/django/tree/postgis2, but now I am out of time.

[Anssi Kääriäinen]

I have now an updated patch in ​https://github.com/akaariai/django/tree/postgis2. This needs another review.

I do get errors from the test suite:

======================================================================
FAIL: test01_retrieve (django.contrib.gis.tests.test_spatialrefsys.SpatialRefSysTest)
Testing retrieval of SpatialRefSys model objects.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/akaariai/projects/django/tests/django/contrib/gis/tests/test_spatialrefsys.py", line 58, in test01_retrieve
    self.assertEqual(srtext, srs.wkt)
AssertionError: 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]' != u'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'

======================================================================
FAIL: test02_osr (django.contrib.gis.tests.test_spatialrefsys.SpatialRefSysTest)
Testing getting OSR objects from SpatialRefSys model objects.
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/akaariai/projects/django/tests/django/contrib/gis/tests/test_spatialrefsys.py", line 88, in test02_osr
    self.assertEqual(srtext, srs.wkt)
AssertionError: 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]' != 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]'

----------------------------------------------------------------------
Ran 27 tests in 0.017s

FAILED (failures=2)

The failures seem to be about float conversion. On my laptop I got different errors, again related to float rounding. The question is if this is related to the updated PostGIS & dependencies, or if it is a pre-existing failure. At least on the laptop these errors seem to be present even using PostGIS 1.5.

[Flavio Curella]

Looking at the data closer, seems like there's a precision/rounding issue.

For both failures, the expected value contains UNIT["degree",0.0174532925199433, but we get a more accurate UNIT["degree",0.01745329251994328.

[Flavio Curella]

I was having some problem creating the test database on PostGIS2. sql_table_creation_suffix was trying to use the template database.

As noted before, PostGIS2 still support the template database approach. Patch v9 looks for the template database and uses it if it exists. If it doesn't we raise an ImproperlyConfigured exception on PostGIS < 2.

[Flavio Curella]

checks if a template database exists

[Flavio Curella]

I've fixed the tests and run them using postgres9.1 + postgis 1.5. I would love if some could run the tests under postgres 8 and report if django.contrib.gis.gdal.tests.test_ds.DataSourceTest.test05_geometries passes.

[Flavio Curella]

fixed tests

[Claude Paroz]

The test failures have been fixed through ticket #18795, they were not directly related to Postgis 2. This part can now be removed from the patch.

[Flavio Curella]

Thanks @claudep, I missed that. Just ignore patch v10.

[Claude Paroz]

While preparing the commit, I'm still getting a failing test (geoapp.GeoLookupTest.test_left_right_lookups). When I short-circuit the index creation process, the test succeeds, so I guess this has something to do with indexes. I have PostGIS 2.0.1 on Ubuntu 12.04. Can someone reproduce the problem?

[Claude Paroz <claude@…>]

In 92b5341b19e9b35083ee671afc2afb2f252c662d:

Fixed #16455 -- Added support for PostGIS 2.0

Thanks ckarrie for the report and the initial patches, Flavio Curella
for updating the patch, and Anssi Kääriäinen for testing. See ticket
for other valuable contributors.

[Flavio Curella]

I'm reopening this ticket instead of creating because most of the discussion is in here.

The ticket was closed in 92b5341b19e9b35083ee671afc2afb2f252c662d, but it's been partially reverted in 91ef2a5253a50a602523bdda943c32c4cfe719fe[1].

The crux of the dilemma is how users are supposed to create a spatial database on which version of postgis and postgresql.

There are two way, both supported as postgis2.0:

1. the classical way: creating the new database from a template database.
2. The new way: creating the new database (without having a spatial template), and then creating extensions on it (note that this will only work on postgresql 9.1+)

I think we should support both ways.

The original implementation of this patch used to query the database looking for the template database and using it if found. Otherwise it will assume the user is trying to go the 'create extension' way.
This means one additional query on creation[2], which obviously is not optimal, but I can't see any way around it.

91ef2a5253a50a602523bdda943c32c4cfe719fe reverts this in that it assumes there's always a template database available. Indeed, creating a database will fail with it.

First step would be to determine if the cost of the additional query is worth it. If it isn't, we should update the docs to instruct the user to create the template database regardless of what version of postgresql or postgis they're using.

---
[1] Only the code has been reverted. The documentation hasn't been updated to reflect it.
[2] The original patch had a suboptimal implementation of this which would query per-table. I have opened a pull request so that it will be one query per run. ​https://github.com/django/django/pull/439

[houmie]

Something isn't quite right here.
I have installed PostGIS 2.0 and no matter if I create the DB with a template DB or extend an existing one I get the following error:

Failed to install index for myapp.WorldBorder model: operator class "gist_geometry_ops" does not exist for access method "gist"

[houmie]

Replying to houmie:

Something isn't quite right here.
I have installed PostGIS 2.0 and no matter if I create the DB with a template DB or extend an existing one I get the following error:

Failed to install index for myapp.WorldBorder model: operator class "gist_geometry_ops" does not exist for access method "gist"

please ignore this. I was trying Django 1.4 with PostGIS 2.0. Which isn't compatible. Thanks

[Claude Paroz]

I've created a new ticket specific to the reopened issue in #19152.

[anonymous]

I have installed PostGIS 2.0.1 and if I run syncdb I get the following error:
Failed to install index for cities.City model: operator class "gist_geometry_ops" does not exist for access method "gist"
I am using Django 1.4.2 and I also tried with PostGIS 2.0.0. thanks

[Claude Paroz]

Django 1.4 is not compatible with Postgis 2.

[anonymous]

That much is obvious, otherwise the bugs wouldn't be there ;)

Is there any specific reason why there shouldn't be Postgis 2 support in DJango?
Postgis 1.x development will cease eventually so it would be good to start thinking about Postgis 2 support.

[izzaddin.ruhulessin@…]

Has anybody tried creating the tables manually?

[Alexis Petrounias]

Replying to izzaddin.ruhulessin@…:

Has anybody tried creating the tables manually?

I tried it and then hit the same problem when any custom SQL was being generated after syncdb, if I remember correctly.

[tomlesters@…]

some question regarding the above fcurella comment:

1. The original patch had a suboptimal implementation. It has one additional query on creation. This happens on database/table creation time or some other time?

In my opinion, If it's only on database/table creation time the performance impact is not a big concern.

2.
If we want to get rid of the additional query, Shouldn't we instruct the user to create database the new way without templates since that's the way postgis moving towards?

regarding petrounias comments:

any custom SQL was being generated after syncdb, any technique details on why that failed?

wish to see this patch in the next django 1.4.x release!

[Alexis Petrounias]

Replying to tomlesters@…:

regarding petrounias comments:

any custom SQL was being generated after syncdb, any technique details on why that failed?

Sorry, I don't have the output any more, but the error was definitely about the custom gist_geometry_ops not existing, which is in line with the errors reported by others above.

[Robert Coup]

If you get a message like operator class "gist_geometry_ops" does not exist for access method "gist" and you're running a recent Django release & PostGIS 2.0+, check that settings.POSTGIS_VERSION is either not set, or set to the PostGIS version installed in your database eg. POSTGIS_VERSION=(2,0,1). You can check this via SELECT postgis_full_version(); from a SQL shell.

[Thomas Schreiber]

I am hitting an error on Django 1.7 master, PostgreSQL 9.3.2, postgis 2.1.1. 'django.db.utils.ProgrammingError: extension "postgis" already exists' when running tests.

​https://github.com/django/django/blob/master/django/contrib/gis/db/backends/postgis/creation.py#L92

Changing the call line 92 to "CREATE EXTENSION IF NOT EXISTS postgis" fixes it, but I imagine it could be something with my environment as I haven't seen it before. Is this something worth opening a new ticket for?

[Flavio Curella]

I don't see any reason why we shouldn't add the 'IF NOT EXISTS' clause.

@rizumu Can you create a new ticket for it and provide a pull request?

[Thomas Schreiber]

@fcurella ok thanks, I've done that here: https://code.djangoproject.com/ticket/21713#ticket

[Mariusz Felisiak <felisiak.mariusz@…>]

In c6d88a1:

Refs #16455 -- Added test for using opclasses on indexes for multidimensional geometry fields on PostGIS.