Index: /trunk/docs/VOimplementation.tex
===================================================================
--- /trunk/docs/VOimplementation.tex	(revision 88)
+++ /trunk/docs/VOimplementation.tex	(revision 88)
@@ -0,0 +1,390 @@
+\documentclass{article}
+\usepackage{natbib}
+\usepackage{fancyheadings}
+\usepackage{array}
+\usepackage{xspace}
+
+\textwidth=165 mm
+\textheight=248 mm
+\topmargin=-15 mm
+\oddsidemargin=0 mm
+\parindent=6 mm
+
+\pagestyle{fancy}
+\rhead{}
+\lhead{}
+\lfoot{\today}
+\cfoot{}
+\rfoot{\thepage}
+\setlength{\headrulewidth}{0in}
+
+\newcommand{\eg}{e.g.\xspace}
+\newcommand{\ie}{i.e.\xspace}
+
+\begin{document}
+\begin{flushright}
+{\huge Implementation of VO queries}\\
+\vspace{5mm}{\Large Matthew Whiting}
+\end{flushright}
+
+\tableofcontents
+
+\section{Summary}
+
+This document summarises how the VO-compliant query form is to be set
+up, what queries we expect, and what modifications need to be made
+from where the archive is at present (June 2007). This will be
+continually updated as this part of the project progresses.
+
+\newpage
+\section{Expected queries}
+
+Here is a list of expected queries that the archive should be able to
+deal with, along with comments on each:
+
+\begin{itemize}
+
+\item All observations of a given pulsar.
+\\ \textit{This is essentially how the archive works at the moment.}
+
+\item All observations of a given sky position, within a search radius
+  -- this is essentially a conesearch.  
+\\ \textit{This also exists on the archive at the moment, but doesn't
+    seem to work. An example: a search for J0835-4510 returns a large
+    number of files, but a search at its RA \& Dec gives nothing (\ie
+    ``No pulsars found within (08:35:20.700, -45:10:35.700) with
+    radius 2'').}
+
+\item Accept standard conesearch queries from a VO service.
+\\ \textit{Not possible at present.}
+
+\item All observations within given time range, perhaps combined with
+  above. 
+\\ \textit{A time-range is catered for at present -- and it seems to
+  work fine.}
+
+\item Be able to refine queries based on pulsar parameters.
+\\ \textit{\ie search for all pulsars with period in a given range, or
+  with DM greater than some value... This is not catered for at present.}
+
+\end{itemize}
+
+This is mostly a wish-list. The current project (as at June 2007) will
+involve enabling conesearches to be made on survey data, as well as
+the queries that are currently possible with the on-line
+archive. Conesearches on timing data, particularly with concurrent
+searches on pulsar parameters is probably beyond the scope of what we
+can do.
+
+\newpage
+\section{The mySQL database}
+
+We note here some important points for the mySQL database. It is based
+on \texttt{herschel}, with two accounts: \textbf{pulsar} and
+\textbf{psrdba} -- the latter is the administrative account and should
+be used for adding/modifying tables and so on. The first allows
+searching of tables and other similar queries only.
+
+The full set of php, sql and related files that Albert has compiled
+reside on the machine \texttt{herschel}, in 
+\texttt{herschel:/var/www/vhosts/pulsararchive.atnf.csiro.au/htdocs}
+and sub-directories. This includes the perl script
+\texttt{perl/find\_archives.pl}, which does the trawling of the disks
+to pick up new data files.
+
+\subsection{Problems}
+
+\begin{itemize}
+\item When you are on herschel, you cannot see the \texttt{/psr} disk,
+  which means all the psr programs are unavailable. Is there any
+  reason for this? It is particularly a problem with the
+  position-related query on the web archive, as this uses \texttt{vap}
+  to search for pulsars around a given position.
+\item The version of mySQL could probably do with an update. It is
+  only 4.0, and it would be great to have 5.0 or similar so that we
+  could create functions easily -- I'm thinking functions like an
+  angular separation calculator, as this would make the cone-search
+  queries much easier to write.
+\end{itemize}
+
+\newpage
+\section{Existing data: The ``Observations'' table}
+
+The list of the columns in the existing \textsc{observations}
+mySQL table is given in the table in Appendix~\ref{app-obstable}. 
+
+\subsection{Positional information}
+
+Note that the sky positions for the files in the \textsc{observations}
+table are broken up into hours/degrees, minutes and seconds
+columns. This presents a problem, as the declination information does
+not properly record positions like -00:01:23. This is because you
+cannot distinguish between $-0$ and $+0$ in the decj\_degree entry.
+
+The positions should either be stored as decimal degrees (best
+option), or, if the hours/degrees, minutes and seconds must be stored
+separately, also have a decj\_sign column that indicates the sign of
+the declination (equal to +1 or $-1$). The positions could also be
+stored as a string (\ie ``18:23:45.3''), although a numerical value
+will also be needed for angular separation calculations.
+
+An example of how we can create new columns to store the decimal
+degree values is the following bit of SQL code:
+\begin{quote}
+\begin{verbatim}
+SELECT pulsar_name,filename,data_type,decj_degree,decj_minute,decj_second,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        CASE WHEN pulsar_name LIKE "%-%" 
+             THEN -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             ELSE abs(decj_degree)+decj_minute/60.+decj_second/3600. 
+             END as decj
+FROM observations 
+WHERE abs(decj_degree)<1 
+      AND (decj_minute>0 or decj_second>0) 
+ORDER BY pulsar_name;
+\end{verbatim}
+\end{quote}
+
+Currently the positions are stored incorrectly even in the ATNF Pulsar
+Database\footnote{See
+http://www.atnf.csiro.au/research/pulsar/psrcat/}. A search ``within
+circular boundary'' with radius of 1 degree from 16:07:00, -00:30:00
+does not return J1607$-$0032, which appears to be 1.05 degrees
+away. This is because the DECJ is returned as +00:32:40.83 (\ie the
+sign of the declination is not preserved). This seems to be a wider
+problem, originating with the \texttt{psrcat} program.
+
+A new table can be created very simply that has two new columns with
+the decimal values of RA and DEC in it:
+\begin{quote}
+  \begin{verbatim}
+-- version of mysql is <4.1 so cannot use CREATE ... LIKE ...
+CREATE TEMPORARY TABLE newobs
+   SELECT *,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        CASE WHEN pulsar_name LIKE "%-%" 
+                  THEN -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             WHEN (decj_degree<0.)
+                  THEN -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             ELSE abs(decj_degree)+decj_minute/60.+decj_second/3600. 
+        END AS decj
+   FROM observations;
+  \end{verbatim}
+\end{quote}
+Note the two clauses that set the declination to be negative. The
+first is designed to deal with sources that have
+$-1^\circ<\delta<0^\circ$, and looks at the name to see if it has a
+minus sign. The second clause deals with sources that don't have an
+``IAU-format'' name (such as ``CAL''). This will only catch them if
+they have $\delta<-1^\circ$, but fortunately all the sources with
+$-1^\circ<\delta<0^\circ$ are suitably named to be caught by the first
+clause. 
+
+The only remaining problem sources as far as positions go are those
+with no positional information in the table. There are 246 rows that
+satisfy this criterion -- how are we going to deal with these?
+
+\newpage
+\section{The new data: 70cm Survey}
+
+\subsection{Location of data}
+
+The data is going to be stored in the \$S70V directory, once it is
+converted to PSRFITS format from the PKS format files in
+\$S70TAPES. There is some data there now, most of which is not up to
+date with all the metadata (it was converted earlier in the year, with
+older versions of the \textsc{pvoconvert} code and the PSRFITS
+definition file), and some that I have converted in the past week.
+
+\subsection{Incorporating the data into the database}
+
+It is relatively straightforward to load the data into the database,
+either directly using SQL commands or via the PHP interface. Here's an
+example of how it may be done (taken from
+\texttt{\$PVO/sql/create\_table\_s70.sql}):
+\begin{quote}
+\begin{verbatim}
+-- create the table with the following fields
+CREATE TEMPORARY TABLE s70 (
+	file_id SMALLINT UNSIGNED AUTO_INCREMENT,
+	filename VARCHAR(30),
+	fileloc VARCHAR(100),
+	hdrver VARCHAR(10),
+	survey VARCHAR(10),
+	src_name VARCHAR(20),
+	raj VARCHAR(15),
+	decj VARCHAR(15),
+	rajd DOUBLE,
+	decjd DOUBLE,
+	bmaj FLOAT,
+	bmin FLOAT,
+	bpa FLOAT,
+	date_obs VARCHAR(30),
+	mjd DOUBLE,
+	obsfreq DOUBLE,
+	chanbw DOUBLE,
+	nchan INT,
+	scanlen DOUBLE,
+	tbin DOUBLE,
+	PRIMARY KEY (file_id)
+);
+
+-- now load the data from the file in my PulsarVO directory. 
+-- It is a csv file, and the strings in it are enclosed by ". 
+-- We don't want the quote marks in the table, so the 
+-- "OPTIONALLY ENCLOSED BY" statement will remove them.
+LOAD DATA LOCAL
+INFILE "/DATA/SITAR_1/whi550/VO/PulsarVO/s70v_summary.csv"
+INTO TABLE s70
+FIELDS TERMINATED BY ","
+OPTIONALLY ENCLOSED BY "\""
+IGNORE 1 LINES  -- ignore the first line in the csv file (contains the headers)
+(filename,fileloc,hdrver,survey,src_name,raj,decj,rajd,decjd,
+bmaj,bmin,bpa,date_obs,mjd,obsfreq,chanbw,nchan,scanlen,tbin);
+\end{verbatim}
+\end{quote}
+
+The exact columns are still to be fully determined, but the SQL makeup
+will be as given in Appendix~\ref{app-s70table}.
+
+
+\newpage
+\section{VO conesearch queries}
+
+\subsection{How it can be done in SQL code}
+
+Here is an example of how you can query the database, once the 70cm
+data is in there, by searching for angular separation:
+\begin{quote}
+\begin{verbatim}
+-- Performing a conesearch around the position 
+--  RA=0.=00:00:00 and DEC=-43.=-43:00:00 for the S70 data.
+-- This should pick up three files from the new batch added on June 26.
+-- The old query of RA=0 and DEC=-49 should pick up four files.
+SET @qra=0., @qdec=-49.;
+SELECT filename, raj, decj, 0.5*(bmaj+bmin) AS beamsize, obsfreq, scanlen,
+       (degrees( acos( cos(radians(rajd-@qra))*
+                       cos(radians(decjd))*cos(radians(@qdec))
+                      +sin(radians(decjd))*sin(radians(@qdec)) ) )) AS angsep
+FROM s70
+WHERE (degrees( acos( cos(radians(rajd-@qra))*
+                      cos(radians(decjd))*cos(radians(@qdec))
+                     +sin(radians(decjd))*sin(radians(@qdec)) ) ) 
+        - 0.25*(bmaj+bmin) ) <  1.
+ORDER BY angsep;
+\end{verbatim}
+\end{quote}
+
+\subsection{Producing a VOTable}
+
+The above SQL structure can be readily incorporated into a .php file
+and used to query the database via a dynamic webpage. This .php file
+can also produce an XML file rather than the usual HTML format. The
+way to do this is to have at the start of the php file the expression
+\begin{quote}
+\verb|header("Content-type: text/xml");|
+\end{quote}
+to make sure the resulting file is read as XML. 
+
+From there, it is straightforward to put in the necessary VOTable
+fields and fill in the data that results from the query. This is done
+in the file \texttt{conequery-vo.php}.
+
+\subsection{Provision of data -- what's in the VOTable?}
+
+The VOTable that we provide is simply a table of metadata. By default
+all metadata that is in the SQL table is provided, with the exception
+of the file's disk location. We do provide the filename, but that is
+all -- there is no mechanism to directly obtain the data from the
+cone-search query.
+
+The reasoning here is that a user might first do a VO cone-search, and
+then, if a result of interest came up, they would then go to the web
+archive and repeat the search. The web archive's results page would
+then provide means for downloading the data, in much the same way as
+it is set up at present (thanks to Albert).
+
+
+%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
+\newpage
+\appendix
+\section{``Observations'' table setup}
+\label{app-obstable}
+
+\begin{table}[h]
+\begin{tabular}{l|l|l|l|l|l}
+ Field             & Type                & Null & Key & Default      & Extra \\
+\hline
+ pulsar\_name      & varchar(16)         &      &     &              &\\
+ filename          & varchar(32)         &      & PRI &              &\\
+ raj\_hour         & int(2)              & YES  &     & 0            &\\
+ raj\_minute       & int(2)              & YES  &     & 0            &\\
+ raj\_second       & decimal(12,10)      & YES  &     & 0.0000000000 &\\
+ decj\_degree      & int(2)              & YES  &     & 0            &\\
+ decj\_minute      & int(2)              & YES  &     & 0            &\\
+ decj\_second      & decimal(12,10)      & YES  &     & 0.0000000000 &\\
+ dm                & decimal(16,10)      & YES  &     & NULL         &\\
+ period            & decimal(32,24)      & YES  &     & NULL         &\\
+ bw                & decimal(16,6)       & YES  &     & NULL         &\\
+ cnfg              & varchar(128)        & YES  &     & NULL         &\\
+ data\_loc         & varchar(128)        &      & PRI &              &\\
+ data\_type        & varchar(16)         & YES  &     & NULL         &\\
+ freq              & decimal(32,16)      & YES  &     & NULL         &\\
+ inst              & varchar(32)         & YES  &     & NULL         &\\
+ MJD               & decimal(32,26)      & YES  &     & NULL         &\\
+ npol              & int(6)              & YES  &     & NULL         &\\
+ nchan             & int(6)              & YES  &     & NULL         &\\
+ nbin              & int(6)              & YES  &     & NULL         &\\
+ nsub              & int(6)              & YES  &     & NULL         &\\
+ rcvr              & varchar(16)         & YES  &     & NULL         &\\
+ site              & varchar(8)          & YES  &     & NULL         &\\
+ file\_size\_bytes & int(32)             & YES  &     & NULL         &\\
+ length            & decimal(32,20)      & YES  &     & NULL         &\\
+ obsrvr            & varchar(32)         & YES  &     & NULL         &\\
+ date              & varchar(16)         & YES  &     & NULL         &\\
+ ut                & varchar(16)         & YES  &     & NULL         &\\
+ gl                & float               & YES  &     & NULL         &\\
+ gb                & float               & YES  &     & NULL         &\\
+ tsamp             & float               & YES  &     & NULL         &\\
+ nbeam             & tinyint(3) unsigned & YES  &     & NULL         &\\
+\end{tabular}
+\caption{Fields for the \textsc{observations} table in the mySQL
+  database, as it stands at present.}
+\label{tab-obs}
+\end{table}
+
+\newpage
+\section{Proposed ``S70'' table setup}
+\label{app-s70table}
+
+\begin{table}[h]
+\begin{tabular}{l|l|l|l|l|l}
+ Field     & Type                 & Null & Key & Default & Extra\\
+\hline
+ file\_id  & smallint(5) unsigned &      & PRI & NULL    & auto\_increment\\
+ filename  & varchar(30)          & YES  &     & NULL    &\\
+ fileloc   & varchar(100)         & YES  &     & NULL    &\\
+ hdrver    & varchar(10)          & YES  &     & NULL    &\\
+ survey    & varchar(10)          & YES  &     & NULL    &\\
+ src\_name & varchar(20)          & YES  &     & NULL    &\\
+ raj       & varchar(15)          & YES  &     & NULL    &\\
+ decj      & varchar(15)          & YES  &     & NULL    &\\
+ rajd      & double               & YES  &     & NULL    &\\
+ decjd     & double               & YES  &     & NULL    &\\
+ bmaj      & float                & YES  &     & NULL    &\\
+ bmin      & float                & YES  &     & NULL    &\\
+ bpa       & float                & YES  &     & NULL    &\\
+ date\_obs & varchar(30)          & YES  &     & NULL    &\\
+ mjd       & double               & YES  &     & NULL    &\\
+ obsfreq   & double               & YES  &     & NULL    &\\
+ chanbw    & double               & YES  &     & NULL    &\\
+ nchan     & int                  & YES  &     & NULL    &\\
+ scanlen   & double               & YES  &     & NULL    &\\
+ tbin      & double               & YES  &     & NULL    &\\
+\end{tabular}
+\caption{Fields for the proposed 70cm data table in the mySQL
+  database.}
+\label{tab-s70}
+\end{table}
+
+\end{document}
Index: /trunk/docs/VOrequirements.tex
===================================================================
--- /trunk/docs/VOrequirements.tex	(revision 87)
+++ /trunk/docs/VOrequirements.tex	(revision 88)
@@ -4,5 +4,4 @@
 \usepackage{array}
 \usepackage{xspace}
-\usepackage{ulem}
 
 \textwidth=165 mm
@@ -270,15 +269,16 @@
  raj       & varchar(15)          & YES  &     & NULL    &\\
  decj      & varchar(15)          & YES  &     & NULL    &\\
- rajd      & float                & YES  &     & NULL    &\\
- decjd     & float                & YES  &     & NULL    &\\
+ rajd      & double               & YES  &     & NULL    &\\
+ decjd     & double               & YES  &     & NULL    &\\
  bmaj      & float                & YES  &     & NULL    &\\
  bmin      & float                & YES  &     & NULL    &\\
  bpa       & float                & YES  &     & NULL    &\\
  date\_obs & varchar(30)          & YES  &     & NULL    &\\
- obsfreq   & float                & YES  &     & NULL    &\\
- chanbw    & float                & YES  &     & NULL    &\\
- nchan     & float                & YES  &     & NULL    &\\
- scanlen   & float                & YES  &     & NULL    &\\
- tbin      & float                & YES  &     & NULL    &\\
+ mjd       & double               & YES  &     & NULL    &\\
+ obsfreq   & double               & YES  &     & NULL    &\\
+ chanbw    & double               & YES  &     & NULL    &\\
+ nchan     & int                  & YES  &     & NULL    &\\
+ scanlen   & double               & YES  &     & NULL    &\\
+ tbin      & double               & YES  &     & NULL    &\\
 \end{tabular}
 \caption{Fields for the proposed \textbf{70} table in the mySQL
@@ -313,4 +313,19 @@
 sign of the declination is not preserved). This seems to be a wider
 problem, originating with the \texttt{psrcat} program.
+
+An example of how we can create new columns is the following bit of
+SQL code:
+\begin{verbatim}
+SELECT pulsar_name,filename,data_type,decj_degree,decj_minute,decj_second,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        CASE WHEN pulsar_name LIKE "%-%" 
+             THEN -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             ELSE abs(decj_degree)+decj_minute/60.+decj_second/3600. 
+             END as decj
+FROM observations 
+WHERE abs(decj_degree)<1 
+	and (decj_minute>0 or decj_second>0) 
+ORDER BY pulsar_name;
+\end{verbatim}
 
 
Index: /trunk/new-htdocs/conequery-vo.php
===================================================================
--- /trunk/new-htdocs/conequery-vo.php	(revision 87)
+++ /trunk/new-htdocs/conequery-vo.php	(revision 88)
@@ -127,5 +127,5 @@
 
 ////////////////////////////
-// Constraint on boundary
+// Cone search
 
 $angsep_str = "";
Index: /trunk/new-htdocs/newObsTable.php
===================================================================
--- /trunk/new-htdocs/newObsTable.php	(revision 88)
+++ /trunk/new-htdocs/newObsTable.php	(revision 88)
@@ -0,0 +1,22 @@
+<?php
+
+function newObsTable() {
+
+  // MAKES A NEW TABLE THAT HAS EVERYTHING THE OBSERVATIONS TABLE HAS,
+  // BUT ALSO HAS COLUMNS WITH VALUES OF RA AND DEC IN DECIMAL
+  // DEGREES. THE SIGN OF THE DECLINATION HAS BEEN CORRECTED FOR ALL
+  // SOURCES AS WELL
+
+  $newtable = "
+CREATE TEMPORARY TABLE newobs
+   SELECT *,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        case when pulsar_name like "%-%" then -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             else abs(decj_degree)+decj_minute/60.+decj_second/3600. end as decj
+ FROM observations";
+
+  $result = mysql_query($newtable) or die('Make new table failed: ' . mysql_error());
+
+}
+
+?>
Index: /trunk/new-htdocs/xmlFunctions.php
===================================================================
--- /trunk/new-htdocs/xmlFunctions.php	(revision 87)
+++ /trunk/new-htdocs/xmlFunctions.php	(revision 88)
@@ -70,5 +70,5 @@
 
 function print_data_line_VO($line) {
-  /// PRINT VOTABLE HEADERS (<FIELD>) FOR EACH OF THE COLUMNS TO BE INCLUDED
+  // PRINT A SINGLE ROW OF DATA TO THE VOTABLE
   echo "<TR>\n ";
   printf("<TD>%s</TD>",$line['filename']);
Index: /trunk/sql/angsep_query-s70.sql
===================================================================
--- /trunk/sql/angsep_query-s70.sql	(revision 87)
+++ /trunk/sql/angsep_query-s70.sql	(revision 88)
@@ -4,10 +4,12 @@
 source /DATA/SITAR_1/whi550/VO/PulsarVO/sql/create_table_s70.sql;
 --
--- Performing a conesearch around position RA=0.=00:00:00 and DEC=-49.=-49:00:00 for the S70 data
--- This should pick up four files.
-SELECT filename, raj, decj, 0.5*(bmaj+bmin) as beamsize, obsfreq,
-(degrees( acos( cos(radians(rajd-0.))*cos(radians(decjd))*cos(radians(-49.))+sin(radians(decjd))*sin(radians(-49.)) ) )) as angsep
+-- Performing a conesearch around position RA=0.=00:00:00 and DEC=-43.=-43:00:00 for the S70 data
+-- This should pick up three files from the new batch added on June 26.
+-- The old query of RA=0 and DEC=-49 should pick up four files.
+SET @qra=0., @qdec=-49.;
+SELECT filename, raj, decj, 0.5*(bmaj+bmin) as beamsize, obsfreq, scanlen,
+(degrees( acos( cos(radians(rajd-@qra))*cos(radians(decjd))*cos(radians(@qdec))+sin(radians(decjd))*sin(radians(@qdec)) ) )) as angsep
 FROM s70
-WHERE (degrees( acos( cos(radians(rajd-0.))*cos(radians(decjd))*cos(radians(-49.))+sin(radians(decjd))*sin(radians(-49.)) ) ) - 0.25*(bmaj+bmin) ) <  1.
+WHERE (degrees( acos( cos(radians(rajd-@qra))*cos(radians(decjd))*cos(radians(@qdec))+sin(radians(decjd))*sin(radians(@qdec)) ) ) - 0.25*(bmaj+bmin) ) <  1.
 ORDER by angsep;
 -- period;
Index: /trunk/sql/angsep_query.sql
===================================================================
--- /trunk/sql/angsep_query.sql	(revision 87)
+++ /trunk/sql/angsep_query.sql	(revision 88)
@@ -2,5 +2,5 @@
 -- Performing a conesearch around position RA=272.46=18:09:50.4 and DEC=-19.73=-19:43:48
 -- This should pick up at least J1809-1943 and J1810-2005 (within 0.5deg of that position).
-SELECT pulsar_name, period, dm, freq,
+SELECT pulsar_name,filename,data_type, period, dm, freq,
 --	raj_hour, raj_minute, raj_second,
 --	(raj_hour+raj_minute/60.+raj_second/3600.)*15 as RA,
@@ -14,5 +14,7 @@
 		     )  
 		) ) as angsep
-FROM observations
+, raj, decj
+FROM newobs
+--FROM observations
 WHERE ( degrees(acos( cos( radians( (raj_hour+raj_minute/60.+raj_second/3600.)*15 - 272.46 ) )
 			* cos(radians(abs(decj_degree)+decj_minute/60.+decj_second/3600.)*sign(decj_degree))
Index: /trunk/sql/create_new_obs_table.sql
===================================================================
--- /trunk/sql/create_new_obs_table.sql	(revision 88)
+++ /trunk/sql/create_new_obs_table.sql	(revision 88)
@@ -0,0 +1,14 @@
+-- MAKE A TEMPORARY COPY OF THE OBSERVATIONS TABLE THAT HAS THE RA AND
+-- DEC PROPERLY DONE
+
+-- if the table exists, drop it so we can reload it
+DROP TEMPORARY TABLE IF EXISTS newobs;
+
+-- version of mysql is <4.1 so cannot use CREATE ... LIKE ...
+CREATE TEMPORARY TABLE newobs
+   SELECT *,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        case when pulsar_name like "%-%" then -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             when (decj_degree<0.)   then -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             else abs(decj_degree)+decj_minute/60.+decj_second/3600. end as decj
+ FROM observations;
Index: /trunk/sql/create_table_s70.sql
===================================================================
--- /trunk/sql/create_table_s70.sql	(revision 87)
+++ /trunk/sql/create_table_s70.sql	(revision 88)
@@ -22,5 +22,5 @@
 	chanbw DOUBLE,
 	nchan INT,
-	scanlen FLOAT,
+	scanlen DOUBLE,
 	tbin DOUBLE,
 	PRIMARY KEY (file_id)
Index: /trunk/sql/define_angsep_function.sql
===================================================================
--- /trunk/sql/define_angsep_function.sql	(revision 88)
+++ /trunk/sql/define_angsep_function.sql	(revision 88)
@@ -0,0 +1,14 @@
+-- THIS IS CODE THAT SHOULD CREATE A STORED FUNCTION
+-- THIS FUNCTION TAKES FOUR INPUT PARAMETERS, BEING RA & DEC OF TWO POSITIONS
+--  AND RETURNS A SINGLE PARAMETER, BEING THE ANGULAR SEPARATION
+-- ALL ANGLES IN DEGREES.
+
+--DELIMITER //;
+
+CREATE FUNCTION angsepfunc (ra1 DOUBLE, dec1 DOUBLE, ra2 DOUBLE, dec2 DOUBLE) 
+	RETURNS DOUBLE
+	RETURN acos( cos(radians(ra1-ra2))*cos(radians(dec1))*cos(radians(dec2))+sin(radians(dec1))*sin(radians(dec2)) );
+--;
+--//
+
+--DELIMITER ;
Index: /trunk/sql/find_bad_declinations.sql
===================================================================
--- /trunk/sql/find_bad_declinations.sql	(revision 87)
+++ /trunk/sql/find_bad_declinations.sql	(revision 88)
@@ -7,2 +7,14 @@
 	and (decj_minute>0 or decj_second>0) 
 	and pulsar_name like "%-%";
+
+-- THIS TESTS A WAY TO PRINT THE CORRECT DECLINATION
+select pulsar_name,filename,data_type,decj_degree,decj_minute,decj_second,
+        (raj_hour+raj_minute/60.+raj_second/3600.)*15 as raj,
+        case when pulsar_name like "%-%" then -1.*(abs(decj_degree)+decj_minute/60.+decj_second/3600.)
+             else abs(decj_degree)+decj_minute/60.+decj_second/3600. end as decj
+from observations 
+where  abs(decj_degree)<1 
+	and (decj_minute>0 or decj_second>0) 
+order by pulsar_name
+;
+
