wiki:dbSpatialSupport
Last modified 10 years ago Last modified on 04/23/2009 09:22:33 PM

Spatial Support

LSST Database

This page attempts to capture what geo-spatial functionality is needed by astronomical community.

Summary

Astronomers work with celestial coordinates (right ascension, declination), thus they need an efficient index in 2-D spherical space. There is no official standard. Probably the most commonly used index nowadays is HTM.

Commonly used geo-spatial data types include points (2-D), circles, polygons, ellipses and boxes.

Most common operations performed on the spatial regions: contain, outside, overlap, adjacent, difference and union.

Example operations

On a single catalog:

  • Find objects near other objects
  • Find objects inside a region, eg within a circle (cone searches) satisfying certain criteria
  • Find the closest object to a given point
  • Find all objects inside or outside a masked region
  • Find objects near the edges of a region
  • Check if given object belongs to a given region
  • Find objects in the overlapping section of two regions
  • Count the number of objects within a list of regions
  • Compute the area and circumference of a region

On multiple catalogs:

  • Is this point in any of the catalogs?
  • Find these 1,000–100,000 objects in these catalogs
  • Find objects in B which are within distance d of objects in A
  • Find objects which are in A, B, and C but not in D
  • Extract a random sample of existing objects within a region for all catalogs
  • Cross-match 2 catalogs within a given region
  • Cross-match n catalogs, n > 2, within a given region
  • Find surveys covering a given region
  • Compute the intersecting region between two or more catalogs

Spatial support in mysql

MySQL implements spatial extensions, see: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html. Based on discussions with mysql developers, this is rarely used and relatively poorly implemented / supported.

MyISAM engine supports spatial index. The storage overhead is quite significant. For the following tables:

CREATE TABLE radec (
  ra        REAL NOT NULL,
  decl      REAL NOT NULL,
  INDEX idxRaDec(ra, decl)
);

CREATE TABLE pt (
  pt       POINT NOT NULL,
  SPATIAL INDEX idxPt(pt)
);

The storage overhead per row is:

table data [bytes per row] index [bytes per row]
radec 17 15.02
pt 36 70.55

That was measured for 100K rows using mysql 5.0

Also, some relatively basic features like DISTANCE for two points are not implemented. Some improvements have been made, eg see http://forge.mysql.com/wiki/GIS_Functions, but these are not in the main mysql branch yet (as of Apr 2009)

References

  • 20 Spatial Queries for an Astronomer’s Bench(mark), M. Nieto-Santisteban, T. School, A. Szalay, A. Kemper, in Proceedings of Astronomical Data Analysis Software and Systems XVII, London, UK, 23rd - 26th September 2007.
  • Implementing the Region Syntax, A. Szalay, T. Budavari, P.D. Dowler, ADQL Workshop, 9/2007
  • phSphere

Attachments