#16455 closed New feature (fixed)
Postgis 2.0 Compatibility
Reported by: | Christian Karrié | Owned by: | jbronn |
---|---|---|---|
Component: | GIS | Version: | dev |
Severity: | Normal | Keywords: | postgis 2.0 |
Cc: | ckarrie@…, cmutel@…, kmike84@…, anssi.kaariainen@…, pronik, dan.fairs@…, taylor.mitchell@…, zmiller@…, luiz.vital@…, Kamil Gałuszka, sean@…, Flavio Curella, aurelio@…, david@…, tom@… | Triage Stage: | Design decision needed |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Attachments (13)
Change History (86)
comment:1 by , 13 years ago
Severity: | Release blocker → Normal |
---|---|
Type: | Bug → New feature |
by , 13 years ago
Attachment: | django.log added |
---|
comment:2 by , 13 years ago
Keywords: | postgis 2.0 added |
---|
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!
comment:3 by , 13 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:4 by , 13 years ago
Has patch: | set |
---|---|
Needs tests: | set |
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.
comment:5 by , 13 years ago
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);
comment:6 by , 13 years ago
Just discovered, that there is already a method to detect the postgis version, so here is my updated patch.
by , 13 years ago
Attachment: | django-trunk-postgis2-version2.0.patch added |
---|
comment:7 by , 13 years ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
comment:8 by , 13 years ago
Needs documentation: | set |
---|---|
Patch needs improvement: | set |
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].
by , 13 years ago
Attachment: | django-trunk-rev16722-postgis2-version3.0.patch added |
---|
comment:9 by , 13 years ago
Cc: | added |
---|
comment:11 by , 13 years ago
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).
comment:12 by , 13 years ago
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.
comment:13 by , 13 years ago
Patch needs improvement: | unset |
---|---|
Version: | 1.3-beta → SVN |
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.
comment:14 by , 13 years ago
Cc: | added |
---|
comment:15 by , 13 years ago
So is it fixed in 1.4? I still get this error with PostgreSQL 9.1 + PostGIS 2.0.0 + Django 1.4
comment:16 by , 13 years ago
Cc: | added |
---|
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.
comment:17 by , 12 years ago
Cc: | added |
---|
comment:18 by , 12 years ago
Cc: | added |
---|
comment:19 by , 12 years ago
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?
comment:20 by , 12 years ago
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:
comment:21 by , 12 years ago
Cc: | added |
---|
comment:22 by , 12 years ago
Cc: | added |
---|
comment:23 by , 12 years ago
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.
comment:24 by , 12 years ago
Cc: | added |
---|
comment:25 by , 12 years ago
Cc: | added |
---|
comment:26 by , 12 years ago
Cc: | added |
---|
by , 12 years ago
Attachment: | 16455-v5.diff added |
---|
comment:28 by , 12 years ago
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
comment:29 by , 12 years ago
Cc: | added |
---|
follow-up: 34 comment:30 by , 12 years ago
I've also attached a draft for the doc changes. I'm not a native English speaker, so feel free to correct me.
comment:31 by , 12 years ago
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
comment:32 by , 12 years ago
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 :)
comment:33 by , 12 years ago
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.
follow-up: 35 comment:34 by , 12 years ago
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
by , 12 years ago
Attachment: | 16455-v6.diff added |
---|
comment:35 by , 12 years ago
comment:36 by , 12 years ago
can you help me with a tip how to make rebase (in comandline) for my branch of forked repo from github? thanks
comment:37 by , 12 years ago
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/
by , 12 years ago
Attachment: | 16455-v7.diff added |
---|
removed multidimensional index (gist_geometry_ops_nd)
comment:38 by , 12 years ago
nice, thanks! is it possible that this patch will be committed into master branch?
comment:39 by , 12 years ago
Needs documentation: | unset |
---|
comment:40 by , 12 years ago
are you sure that this patch re-ADDs something? looks like there is only delition
comment:42 by , 12 years ago
ref: thread on django-dev https://groups.google.com/d/topic/django-developers/m9NjmlvHjfw/discussion
by , 12 years ago
Attachment: | 16455-v8.diff added |
---|
re-added multidimensional index, but this time only for multidimensional geometries
comment:43 by , 12 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:45 by , 12 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
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.
follow-up: 47 comment:46 by , 12 years ago
Patch needs improvement: | unset |
---|
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.
comment:47 by , 12 years ago
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
.
comment:48 by , 12 years ago
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.
comment:49 by , 12 years ago
Cc: | added |
---|
comment:50 by , 12 years ago
Needs tests: | unset |
---|
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.
comment:51 by , 12 years ago
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.
comment:53 by , 12 years ago
Cc: | added |
---|
comment:54 by , 12 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:55 by , 12 years ago
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?
comment:56 by , 12 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
comment:57 by , 12 years ago
Resolution: | fixed |
---|---|
Status: | closed → reopened |
Triage Stage: | Ready for checkin → Design decision needed |
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:
- the classical way: creating the new database from a template database.
- 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
follow-up: 59 comment:58 by , 12 years ago
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"
comment:59 by , 12 years ago
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
comment:60 by , 12 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
I've created a new ticket specific to the reopened issue in #19152.
comment:61 by , 12 years ago
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
comment:63 by , 12 years ago
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.
comment:65 by , 12 years ago
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.
follow-up: 67 comment:66 by , 12 years ago
some question regarding the above fcurella comment:
- 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!
comment:67 by , 12 years ago
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.
comment:68 by , 11 years ago
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 get the exact version via SELECT postgis_full_version();
from a SQL shell.
comment:69 by , 11 years ago
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?
comment:70 by , 11 years ago
Cc: | added |
---|
comment:71 by , 11 years ago
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?
comment:72 by , 11 years ago
@fcurella ok thanks, I've done that here: https://code.djangoproject.com/ticket/21713#ticket
Error translation: ERROR: operator class "gist_geometry_ops" does not accept access method "gist"