On 2008-5-14 10:17, Jeff Lusted wrote:
> Hi Colleagues, once more!
>
> On Wed, 2008-05-14 at 18:07 +0100, Jeff Lusted wrote:
> > -- Find all observed objects within 0.001 degrees of those
> > -- observed objects which have a quality greater than 3
> > -- and a redshift greater than 1.6
> > select s.obsra, s.obsdec, s.z, s.specid, s.targetname, s.quality from
> > spectra as s
> > where s.quality > 3 and s.z > 1.6 and
> > CONTAINS( POINT( 'J2000', s.ra, s.dec ), CIRCLE( 'J2000', s.ra, s.dec,
> > 0.001 ) ) = 1 ;
>
> OK, my apologies, this is wrong. I think it needs some form of
> correlated query to work. I'll see what I can come up with, unless
> someone else can step in? But you can see what I'm getting at. So the
> question about STC-s still stands.
Recognizing that this is a spatial join of s1 (all observed objects) with s2 (the observed objects with the specified properties), the intent can be expressed as:
select
s1.csys, s1.obsra, s1.obsdec, s1.z, s1.specid, s1.targetname, s1.quality from
spectra as s1 JOIN spectra as s2 ON INTERSECTS(
POINT(s1.csys, s1.obsra, s1.obsdec)
CIRCLE(s2.csys,s2.obsra, s2.obsdec, 0.001) ) = 1
where
s2.quality > 3 and s2.redshift > 1.6
Note: I prefer using INTERSECTS because the order of the arguments doesn't matter. I also prefer to put the join conditions in the from clause so I can more easily understand the intent of the query, but it could have gone in the where clause as well.
Bigger note: I have put in this example the use of the csys (coordinate system) column from the table. It is not required but highly important that real use is either like above (for tables with positions expressed as multiple columns) or that a single column (s1.position) be used in place of the POINT construct entirely. All of Arnold's examples/complaints are predicated on the fact that he somehow thinks that in ADQL the user has to know the coordinate system and that STC magically fixes this. In fact, the user should get the coordinate system along with the coordinate values in either case and if they do not they risk making a mistake.
Note: In practice, many existing DBs will have tables with ra and dec columns and no coordinate system column, simply because the DB designer "knew" it was ICRS (for example). The simple solution is for that provider to create a view that adds a column with the coordinate system (as a constant) and expose the view to ADQL querying. The second simplest solution is for the TAP service to say the table has a column called 'coordsys' when it doesn't, parse the ADQL for use of that column, and replace it with the constant.
-- Patrick Dowler Tel/Tél: (250) 363-6914 | fax/télécopieur: (250) 363-0045 Canadian Astronomy Data Centre | Centre canadien de donnees astronomiques National Research Council Canada | Conseil national de recherches Canada Government of Canada | Gouvernement du Canada 5071 West Saanich Road | 5071, chemin West Saanich Victoria, BC | Victoria (C.-B.)Received on 2008-05-14Z20:38:34