#16455 PostGIS 2.0 support

278 views
Skip to first unread message

Flavio Curella

unread,
Aug 8, 2012, 11:12:09 AM8/8/12
to django-d...@googlegroups.com
Hi,

I've made some work on https://code.djangoproject.com/ticket/16455 and I think I'm close to have it ready for checking.

Can I have a quick review from the committers? I'm mostly interested in feedback about:

* the v8 patch installs multidimensional index for columns with more than 2 dimensions. From the PostGIS docs, seems like this kind of indexes are slow, and I can't fiind any advantages to use them. Patch v7 doesn't install the indexes. Any preference about which patch should we use?
* testing the patch: do already existing tests cover? if not, where's the most appropriate place where to put the new tests?
* documentation.

Thanks,
Flavio.

Anssi Kääriäinen

unread,
Aug 9, 2012, 4:07:46 PM8/9/12
to Django developers
On 8 elo, 18:12, Flavio Curella <flavio.cure...@gmail.com> wrote:
> Hi,
>
> I've made some work onhttps://code.djangoproject.com/ticket/16455and I
> think I'm close to have it ready for checking.
>
> Can I have a quick review from the committers? I'm mostly interested in
> feedback about:
>
> * the v8 patch installs multidimensional index for columns with more than 2
> dimensions. From the PostGIS docs, seems like this kind of indexes are
> slow, and I can't fiind any advantages to use them. Patch v7 doesn't
> install the indexes. Any preference about which patch should we use?
> * testing the patch: do already existing tests cover? if not, where's the
> most appropriate place where to put the new tests?
> * documentation.
>
> Thanks,
> Flavio.

The only question I have is about the index: is having no index worse
than having a slower than Postgis 1.5 index. (Assuming I understand
the issue correctly the Postgis 2.0 version of the index is 2-5x
slower to query than the 1.5 version).

Otherwise the patch looks mostly good on a quick glance. Notably my
GIS understanding is low, so I hope somebody knowing that part of the
code could take a look.

- Anssi

Jeremy Dunck

unread,
Aug 9, 2012, 4:48:47 PM8/9/12
to django-d...@googlegroups.com
I'm fairly familiar with the django gis code, but I haven't been
following postgis 2.0. Could you point me towards some discussion of
the index changes from 1.5 to 2.0? On the face of it, I'm doubtful
that they've removed a useful form of indexing with no
replacement/alternative.

It's hard for me to assess the patch w/o some more context re. the
upstream postgis changes.
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-developers/-/Bao8-qgH7fwJ.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to
> django-develop...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-developers?hl=en.

Flavio Curella

unread,
Aug 9, 2012, 4:58:28 PM8/9/12
to django-d...@googlegroups.com
The informations I've found on the 'net were quite old, so I did a quick benchmark.


TL;DR: index is better than no index or the 'gist_geometry_ops_nd' index

Anssi Kääriäinen

unread,
Aug 9, 2012, 5:03:45 PM8/9/12
to Django developers
On 9 elo, 23:48, Jeremy Dunck <jdu...@gmail.com> wrote:
> I'm fairly familiar with the django gis code, but I haven't been
> following postgis 2.0.  Could you point me towards some discussion of
> the index changes from 1.5 to 2.0?  On the face of it, I'm doubtful
> that they've removed a useful form of indexing with no
> replacement/alternative.
>
> It's hard for me to assess the patch w/o some more context re. the
> upstream postgis changes.

The only thing I have is this link in the ticket:
http://blog.opengeo.org/2012/03/13/postgis-2-0-new-features-3d4d-indexing/
and then this link leads to this discussion:
http://postgis.refractions.net/pipermail/postgis-devel/2010-December/011178.html

My understanding of the issue: It seems one index type isn't available
(at least not with the same name) in 2.0 compared to 1.5. This is the
whole incompatibility problem from Django code perspective. The 2d
index seems to be somewhat slower than the 1.5 version (at least as of
December 2010). For this reason the whole index isn't created.

So, there are actually two questions:
- is there a better index for the 2d case
- if not, shouldn't we still create the somewhat slower index, which
could still be orders of magnitude faster than no index at all.

- Anssi

Flavio Curella

unread,
Aug 9, 2012, 5:18:48 PM8/9/12
to django-d...@googlegroups.com
I was running the wrong query for the 3D case.
I've updated the benchmark gist and added some test under PostGIS 1.5.

Flavio Curella

unread,
Aug 9, 2012, 5:42:27 PM8/9/12
to django-d...@googlegroups.com
From the benchmark, my understanding is that, on PostGIS 2.0:

1) for the 2D case, the best index is 'gist (columname)'
2) for the 3D case, the best index is 'gist (columname gist_geometry_ops_nd)' only when using specific 3D operators (ie: '&&&')

I think we should create the index for 2D case, just as we did on PostGIS 1.5.

Re: the 3D case, 2) brings the question: should we add these new operators? Is it out of scope for the ticket?

Jeremy Dunck

unread,
Aug 9, 2012, 6:00:43 PM8/9/12
to django-d...@googlegroups.com
On Thu, Aug 9, 2012 at 2:42 PM, Flavio Curella <flavio....@gmail.com> wrote:
> From the benchmark, my understanding is that, on PostGIS 2.0:
>
> 1) for the 2D case, the best index is 'gist (columname)'
> 2) for the 3D case, the best index is 'gist (columname
> gist_geometry_ops_nd)' only when using specific 3D operators (ie: '&&&')
>
> I think we should create the index for 2D case, just as we did on PostGIS
> 1.5.
>
> Re: the 3D case, 2) brings the question: should we add these new operators?
> Is it out of scope for the ticket?

I think we clearly should, but landing basic support now and adding
new operators later is also useful.

Thanks for doing this work, by the way. :)

Flavio Curella

unread,
Aug 10, 2012, 11:12:53 AM8/10/12
to django-d...@googlegroups.com
Just to clarify, the plan is:

1) Merge in patch v8 (2d index for 2-dimensional fields, _nd index for 3D and up)
2) Open a new ticket for the new 3D operators.

Let me know if that sounds good, or if you can think of any way of improving the patch.

Thanks,
Flavio.

Jeremy Dunck

unread,
Aug 10, 2012, 1:12:58 PM8/10/12
to django-d...@googlegroups.com
That sounds good to me, though I'm not a committer on the tree as far
as I know. Anssi?
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/django-developers/-/yDzsjw77NzYJ.

Anssi Kääriäinen

unread,
Aug 13, 2012, 2:25:36 PM8/13/12
to Django developers
On 10 elo, 20:12, Jeremy Dunck <jdu...@gmail.com> wrote:
> That sounds good to me, though I'm not a committer on the tree as far
> as I know.   Anssi?

I can take care of the patch. As said earlier I don't understand GIS
well, but based on the discussions I have no doubt about the patch.
So, I will try to get this committed soon.

- Anssi

Anssi Kääriäinen

unread,
Aug 20, 2012, 5:58:32 AM8/20/12
to Django developers
I have updated the ticket with some comments. The docs of the previous
patch weren't ready for commit so I had to do an update to that part
of the patch. In addition I do get some errors from the gis tests (see
the ticket for details), but I am not sure if the errors are caused by
updated PostGIS & dependencies, or if the errors are unrelated to the
PostGIS 2.0 work.

In any case the patch needs another review before commit.

- Anssi

Anders Steinlein

unread,
Aug 20, 2012, 6:27:26 AM8/20/12
to django-d...@googlegroups.com
Any chance of having this patch backported to 1.4? Or is this a 1.5-only thing?

/anders

Anssi Kääriäinen

unread,
Aug 20, 2012, 9:26:54 AM8/20/12
to Django developers
On 20 elo, 13:27, Anders Steinlein <and...@e5r.no> wrote:
> Any chance of having this patch backported to 1.4? Or is this a 1.5-only
> thing?

New features do not get backpatched.

However, the changes to actual code are around 5 lines. The rest is
documentation changes. It should be fairly trivial to backpatch this
yourself if you absolutely need PostGIS 2.0 support before Django 1.5.

- Anssi

Flavio Curella

unread,
Aug 24, 2012, 11:21:08 AM8/24/12
to django-d...@googlegroups.com
Thanks everyone for the feedback on this patch.

I've added some code that deals with database creation. Specifically, to support the template database approach also on PostGIS 2.

I still have some failing test, but they fail on both PostGIS1.5 and 2.0. I'm running ``./runtests.py --settings=<mysettings> gis``.

Two of the failures are:

    AssertionError: u'{ "type": "Point", "coordinates": [ 100.000000, 0.000000 ] }' != '{ "type": "Point", "coordinates": [ 100.0, 0.0 ] }'

Could be a JSON serialization quirk? I don't have any JSON lib system-wide nor in my virtualenv, so I know it's using the one shipped within Django. Should we modify the test to unserialize (json.loads) and compare Python objects?

The other failure is:

    AssertionError: 'GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]' != 'GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]'

If you compare the two string, the only difference is that the spatial reference for the spheroid is "WGS_1984" instead of "WGS_84".
I'm not exactly sure if they are the same ref, or why and when it would have changed.

Thank you,
Flavio.

Flavio Curella

unread,
Sep 8, 2012, 3:02:55 PM9/8/12
to django-d...@googlegroups.com
I've fixed the tests and they pass under postgres9.1+postgis1.5


On Friday, August 24, 2012 10:21:08 AM UTC-5, Flavio Curella wrote:

Two of the failures are:

    AssertionError: u'{ "type": "Point", "coordinates": [ 100.000000, 0.000000 ] }' != '{ "type": "Point", "coordinates": [ 100.0, 0.0 ] }'

Could be a JSON serialization quirk? I don't have any JSON lib system-wide nor in my virtualenv, so I know it's using the one shipped within Django. Should we modify the test to unserialize (json.loads) and compare Python objects?

I've fixed these by deserializing and comparing the resulting python objects.
 
The other failure is:

    AssertionError: 'GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_1984",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]' != 'GEOGCS["GCS_WGS_1984",DATUM["WGS_1984",SPHEROID["WGS_84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]'

If you compare the two string, the only difference is that the spatial reference for the spheroid is "WGS_1984" instead of "WGS_84".
I'm not exactly sure if they are the same ref, or why and when it would have changed.

Here I've just changed the expected result. I would love to hear from people using postgres8 if I broke the test for them.

Thanks,
Flavio.
Reply all
Reply to author
Forward
0 new messages