0 votes
99 views

When I query a catalog like NSC DR2, the R.A. and dec. columns list up to 14 decimals. I want to cut off some of the decimals (to 7 decimals max) to reduce the size of the results table and thought the ROUND function would be the best way. Here's a little test code:

SELECT ROUND(ra, 7) as ra, dec
FROM nsc_dr2.object
WHERE 't' = Q3C_RADIAL_QUERY(ra, dec,0.0,0.0,0.01) 

It throws an error message though.

>Error: PSQLException: ERROR: function round(double precision, integer) does not exist.

What's the best/working way to cut off decimals from a column like ra or dec?

by martinkb (720 points)
edited by 0 | 99 views

1 Answer

0 votes
Best answer

Hi, thanks for reaching out. You could use CAST:

SELECT cast(ra as numeric(10,7)), cast(dec as numeric(10,7)) from nsc_dr2.object limit 5

Note that the casting will cost extra time; how much, I don't know, you should experiment with some LIMIT values first.

Best,
Robert

by robertdemo (8.1k points)
selected by 0
Thanks Robert. I'll give it a try. :)

I'm still interested though why ROUND() doesn't work. I thought Astro Data Lab is using PostgreSQL which knows this function?
I'm either doing something wrong or the code is wrong. I get an error message:

Error: IllegalArgumentException: net.sf.jsqlparser.JSQLParserException JSQLParserException: ParseException: Encountered "( ra as" at line 1, column 21. Was expecting one of: "AS" ... "INTO" ... "FROM" ... <S_IDENTIFIER> ... <S_QUOTED_IDENTIFIER> ... "," ... "." ... "(" "NULL" ... "(" "CASE" ... "(" "?" ... "(" "+" ... "(" "-" ... "(" "{fn" ... "(" <S_IDENTIFIER> "." ... "(" <S_IDENTIFIER> "(" ... "(" <S_DOUBLE> ... "(" <S_INTEGER> ... "(" <S_IDENTIFIER> "*" ... "(" <S_IDENTIFIER> "/" ... "(" <S_IDENTIFIER> "+" ... "(" <S_IDENTIFIER> "-" ... "(" <S_IDENTIFIER> "," ... "(" <S_IDENTIFIER> ")" ... "*" ... "/" ... "+" ... "-" ...
Gave it another test. "cast()" is working with the Download client. I've tested with a (python) query resulting in ~42,000 rows and it didn't slow the process down very much. So thanks again Robert. :)

The error above came from the online Query interface (https://datalab.noirlab.edu/query.php). When I tried the sample code from above it asked me to remove the limit so I just tested with:

> SELECT cast(ra as numeric(10,7)), cast(dec as numeric(10,7)) from nsc_dr2.object
Hi Martin,

thanks for diagnosing a bit more. We did the same, and this is the gist:

- The web query form uses ADQL, and "ROUND" is a reserved keyword in ADQL, but our implementation doesn't have it.

- When you use the datalab command line client, or the queryClient python package, you can submit a query as Postgres SQL (which is the default), and that does support ROUND(). But, curiously, the ROUND() function expects the first argument to be 'numeric', so one has to cast it as such. Meaning, this works:

from dl import queryClient as qc

print(qc.query(sql='SELECT ra, CAST(ra AS numeric(10,7)) AS ra_numeric, ROUND(CAST(ra AS numeric),4) AS ra_round FROM nsc_dr2.object LIMIT 3'))

ra,ra_numeric,ra_round
-8.972056466339541,-8.9720565,-8.9721
-8.960232627460641,-8.9602326,-8.9602
-8.956693787104939,-8.9566938,-8.9567

I.e., you might as well stick with just the "CAST AS NUMERIC(10,7)" or so.

Cheers,
Robert
Hi Robert,

thanks for this thorough investigation. :)

> The web query form … implementation doesn't have it.

The error message makes sense then and it's good to know the web query uses ADQL instead of SQL.

> When you use the datalab command line client, or the queryClient python package…

I've mixed those two up as being the same thing but upon further investigation I understand now there's a crucial difference between the command line client and using the queryClient python package.
To be 100% precise, I was indeed using the queryClient python package and not the command line client when testing and finding out that cast() was working as intended.

> … you might as well stick with just the "CAST AS NUMERIC(10,7)" or so.

I think you're right. Makes sense to just cast() and save the extra work of ROUND().

421 questions

437 answers

442 comments

640 users

Welcome to Data Lab Help Desk, where you can ask questions and receive answers from other members of the community.

Categories