spgist 2d and 3d bindings for geometry

[robe]

Before I forget — didn't see it on the list, and Paul you said you have it planned for 2.1.

spgist is a new gist type supported in 9.2. We will start with just 2d support for 2.1 and perhaps later nd.

Creating an spgist index would look like:

CREATE INDEX idx_edges_geom_spgist ON edges USING spgist (geom);

Details on spgist: ​http://www.pgcon.org/2011/schedule/events/309.en.html

[robe]

Looks like Mohit will be working on this as part of GSoc and strk will be mentoring? or did I misunderstand?

​https://www.google-melange.com/gsoc/project/details/google/gsoc2014/mohitkharb/5665319561461760

Excerpt from google summer of code

 The idea is to design and implement hierarchical clustering to the point data in Postgres database and integrate it in CartoDB such that beyond a lower zoom level the spatially nearer points merge into one another to form a single point. The leaf nodes will contain the original point data and the intermediate nodes will have the count of leaf nodes below that level. It will implement SP-GiST indexing on the underlying postGIS database. 

[robe]

forgot link to actual proposal: ​http://www.google-melange.com/gsoc/proposal/public/google/gsoc2014/mohitkharb/5698390809640960

[strk]

You are not mistaken. I asked Mohit to report to postgis-devel (in addition to cartodb forum)

[strk]

Weekly reports for the GSoC project:

​https://github.com/CartoDB/cartodb/wiki/GSOC2014-:-%22Fuzzy-visualization-on-low-zoom-levels-in-CartoDB-using-Sp-GiST-indexing%22

[mohitkharb]

Hi all, The first commit is at ​https://github.com/mohitkharb/postgis/commit/d0d87512 ​https://github.com/mohitkharb/postgis/commit/d0d87512.patch The code would be cleaned by tomorrow and I will update another patch.

[robe]

pramsey has a work in progress works against patched 9.5

Here’s the postgresql branch

​https://github.com/pramsey/postgres/tree/spgistcompress

Here’s the postgis branch (you have to manually load the gserialized_spgist_2d.sql file to get the bindings)

More details here: ​http://lists.osgeo.org/pipermail/postgis-devel/2014-December/024651.html

​https://github.com/pramsey/postgis/tree/spgist-kdtree

[robe]

Paul,

Sadly you may need to let this one go triage since I still feel it ain't going to make the 9.5 cut.

[pramsey]

Moving it to future; without compress in spgist it's not happening.

[robe]

Milestone renamed

[komzpa]

There is SP-GiST compress patch on commitfest: ​https://commitfest.postgresql.org/15/1298/

Let's review it and try to comment, so we get the chance for SP-GiST in PostGIS?

[robe]

shooting for PostGIS 2.5

[komzpa]

Postgres now going to have SP-GiST compress in 11:

​https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=854823fa334cb826eed50da751801d0693b10173

Road is clear

[pramsey]

OK, so an implementation of kd-tree that only accepts points is easy enough. Is there another patchset that provides an example of the 4-d approach for boxes that was mentioned in the -hackers email chain?

[komzpa]

@pramsey: look at the attached patch I took from the thread.

[komzpa]

The box index thread: ​https://www.postgresql.org/message-id/flat/CAF4Au4zxd2XOV0A__FU7xoHxSiwJzm1z2xhs-FFaT1DzB9ub3Q%40mail.gmail.com#CAF4Au4zxd2XOV0A__FU7xoHxSiwJzm1z2xhs-FFaT1DzB9ub3Q@mail.gmail.com

Patch: ​https://www.postgresql.org/message-id/attachment/40377/q4d.patch

[robe]

I'm guessing pull requests

​https://github.com/postgis/postgis/pull/258

and

​https://github.com/postgis/postgis/pull/251

fall into this bucket though maybe (251 spgist for box3d) should have a separate ticket

[robe]

In 16626:

spgist 2d and 3d support for PG 11+
references #1847
Credits: Arthur Lesuisse, Esteban Zimanyi from Université Libre de Bruxelles (ULB) and Darafei Praliaskouski
also added logic to exclude PG < 11 from including this functionality
Closes ​https://github.com/postgis/postgis/pull/258

[robe]

This still needs documentation.

[komzpa]

In 16641:

SP-GiST implementation polishing

Added copyrights to headers, moved BOX3D function declarations from SP-GiST to box3d headers to suppress warnings.

References #1847
Closes ​https://github.com/postgis/postgis/pull/269

[komzpa]

In 16642:

SP-GiST documentation.

Patch provided by Esteban Zimanyi.

Closes #1847