Page History

...

Different databases, different ways of specifying the query and various indexing methods have been tried.

September - October

The spatial extensions are no longer used since performances are disappointing especially when indexes need to be rebuilt.

Selection of events located within a cone is implemented more accurately.
For each event cartesian coordinates (x,y,z) were computed from the celestial coordinates (ra,dec) and have been stored in the event table.
A bounding box of the intersection of the sphere by the cone is computed. If a pole is located in the cone a whole cap is kept.
Events located inside this box are selected. For each event in this subset the dot product between their direction and the axis of the cone is computed.
A query looks like :

select event_number, run_number, time, energy, quality, ra, decl from events
where (1198 <= ra and ra <= 2041 and 1980 <= decl and decl <= 2340) and
(x * 0.35355338 + y * -0.35355338 + z * 0.8660254 > 0.9961946980917455)

A typical result with MySQL is shown here.

January - June

With a pretty small number of events and using the geographical modules available in the database procucts a first evaluation has been made.

Sample Data are stored in a relational database. Events data are kept in a table.
A perl script is run to retrieve events located in a region defined by ra_max, dec_max, ra_min,dec_min. (typically 32x32 degrees).
The time spent by the query as well as the number of events found are kept.
The query is launched 100 times, statistics are output.

See also:

• the Astrogrid DataDocs page.
• Pat Nolan's report

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 50.

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 1.2 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.7 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.9 second.

A R-tree index has been built using the error bounding box.
The response time is 0.01 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.7 seconds.
A GIST index has been built.
The response time is 0.03 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.35 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))

Details are available on this page.

See also:

• the Astrogrid DataDocs page.
• Pat Nolan's report

...