...
Different databases, different ways of specifying the query and various indexing methods have been tried.
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.
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 2x2 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.
Postgresql, since a long time, has implemented geospatial features.
See this reference Using Postgres for Spatial Data.
select * 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.
select * 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 * 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.
...
Section | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
...
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 * 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)
...
|
Details are available on this page.
See also: