DataIndexingDetails

Remarks

The tests were run on a PC running Slackware Linux with a kernel 2.6.9. The CPU was a P4 2.80GHz, the RAM was 512 Mb (which was too small).

Database parameters were set to their default values. No optimization was attempted.

Data are considered to be spread on a rectangular plane. The spherical geometry is not taken into account.

Posgresql

Postgresql, since a long time, has implemented geospatial features.

See this reference Using Postgres for Spatial Data.

basic statement

select id, time, energy from events where ra_min < ra and ra < ra_max and dec_min < dec and dec < dec_max
The response time is around 1. second. On 100 queries the average number of events found is 14500.

statement with geo functions

select id, time, energy from events where point(ra,dec) @ box ra_max,dec_max, ra_min,dec_min
The object point is constructed with the content of the 2 columns ra, dec. The operator @ means is inside.
The response time is around 0.9 second.

select id, time, energy from events where position @ box ra_max,dec_max, ra_min,dec_min
A column position has been added to the table. It is of type point and stores ra, dec.
The response time is around 0.8 second.

select id, time, energy from events where error && box ra_max,dec_max, ra_min,dec_min
A column error has been added to the table. It is of type box and stores ra+dg, dec+dg, ra-dg, dec dg with dg = 1.°. The operator && means overlaps.
The response time is around 0.8 second.

A R-tree index has been built using the error bounding box.
The response time is 0.6 seconds.

using the postgis module

PostGIS adds support for geographic objects to PostgreSQL. It implements some of the OpenGIS specifications.
The events table has been modified to comply with the OpenGIS standard. The following statement has been sent to the database :
select id, time, energy from events where error &&
GeomFromText('POLYGON((ra_max dec_max, ra_max dec_min, ra_min dec_min, ra_min dec_max, ra_max dec_max)', -1)

The response time is 3.1 seconds.
A GIST index has been built.
The response time is 2. seconds.

Mysql

The most recent versions of Mysql provide geospatial functionnalities. These extensions follow the OpenGIS specifications.

basic statement

select id, time, energy rom events where ra_min < ra and ra < ra_max and dec_min < dec and dec < dec_max
The response time is around 0.65 second.

using the spatial extension

The following statement has been sent to the database :
select id, time, energy from events where MBRIntersects(error ,
GeomFromText('POLYGON((ra_max dec_max, ra_max dec_min, ra_min dec_min, ra_min dec_max, ra_max dec_max)', -1))

The response time is 5.7 seconds.
An index has been built.
The response time is 0.4 seconds.

Oracle

Oracle provides a specialized module to manipulate spatial data

basic statement

select id, time, energy rom events where ra_min < ra and ra < ra_max and dec_min < dec and dec < dec_max
The response time is around 0.3 second.

using the spatial module

This statement has been used :
select id, time, energy from events where sdo_filter(error, sdo_geometry(2003, null, null, sdo_elem_info_array(1,1003,3), sdo_ordinate_array(ra_min, dec_min, ra_max, dec_max))) = true

For this command to work an index must be created.
The response time is around 0.8 second.