Spatial Indexing Add-ons to Postgres

pgAstro and pgSphere

The aim of the pgAstro project is to provide astronomical functionality in Postgres. Further details can be found at http://www.pgastro.org. The first priority was to support spatial indexing using using spherical-polar coordinate systems - a sub-project called pgSphere. Both of these projects have been undertaken by a group of programmers mostly at the Sternberg Astronomical Instutute in Moscow.

I downloaded a beta release of pgSphere from their web-site and installed it on hydra. The package is fast to download and easy install; the documentation (in English fortunately) is well-written and described nearly everything I needed to know. Among the spatial data types supported is "scircle" which is a circle needing centre (RA,DEC) and radius.

I modified the programs which convert sections of 2mass and USNO-B from their distribution format to tab-separated value, and ran some tests comparing the built-in R-tree indexing on boxes with the scircle data type support in pgSphere. The results were identical except for tiny differences probably caused by differences in rounding floating-point numbers, and pgSphere produced good results, solving the problem of error-regions which overlap the poles or the line of zero along the RA axis where RA wraps around, and making the queries much simpler. Unfortunately the performance was somewhat poorer: in cone searches pgSphere was nearly as fast, but the operation is so fast that timing is difficult. A spatial join was found to take about six times longer using pgSphere than the built-in R-trees. I have reported this to the support group, and investigations are under way.

pgSphere is built on top of the GiST - Generalised Search Trees http://gist.cs.berkeley.edu:8000/gist from the EECS Department at Berkeley. My guess is that the overheads of a general-purpose indexing structure may be slowing pgSphere down somewhat.

PostGIS

PostGIS is another interesting project: it aims to add support for geographical objects to Postgres. The web-site is http://postgis.refractions.net/.

This project is also built on top of GiST indexing, the reasons for using this rather than the built-in R-trees of Postgres are said to be:

1. Postgres native R-trees can only handle objects less than 8 kilobytes in size, and some complex geographical shapes are polygons with so many points that this limit might be exceeded.
2. Postgres R-trees are not null-safe.

I think neither of these problems is likely to afflict use in astronomy: most of our objects are circles or boxes (or can be represented by simple bounding boxes), and none of our error-regions has any reason to be null.

I have not experimented at all with PostGIS.

-- ClivePage - 23 Mar 2004