CODEWITHSUNDEEP
google-bigquery
foxwendy
foxwendy

Reputation: 2929

Bigquery - How to cast float to string w/o round up/down?

Basically I have a batch of GPS coordinates(lat and long), I wanted to have a string formatted as "lat;long". What I did was:

CONCAT(CAST(Latitude AS STRING),";", CAST(Longitude AS STRING))

But ran into a problem where the coordinates were rounded up by CAST (STRING() did the same thing). for example, by select CAST(45.3618927 as STRING) I'll get 45.3619. How can I prevent this rounding?

Note: I know SAFE_CAST in the standard SQL gives me what I want, but for other reasons I'd have to stay with Legacy dialect.

Upvotes: 0

Views: 2209

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

#legacySQL
SELECT CONCAT(
  RTRIM(CONCAT(LEFT(lat_temp, lat_pos - 1), '.', SUBSTR(lat_temp, lat_pos)), '0'), 
  ';',
  RTRIM(CONCAT(LEFT(lon_temp, lon_pos - 1), '.', SUBSTR(lon_temp, lon_pos)), '0') 
  ) AS lat_lon
FROM (
  SELECT 
    INSTR(STRING(lat * 1000000), '.') - 6 AS lat_pos, 
    REPLACE(STRING(lat * 1000000),'.', '') AS lat_temp,
    INSTR(STRING(lon * 1000000), '.') - 6 AS lon_pos, 
    REPLACE(STRING(lon * 1000000),'.', '') AS lon_temp
  FROM 
    (SELECT 34.052235 AS lat, -118.243683 AS lon) AS yourTable
)

Sure looks ugly, but as you said - for some reason you want to stick wirg Legacy SQL
Otherwise see below for Standard SQL version 

#standardSQL
WITH yourTable AS (
  SELECT 34.052235 AS lat, -118.243683 AS lon
)
SELECT CONCAT(CAST(lat AS STRING),";", CAST(lon AS STRING)) AS lat_lon
FROM yourTable

Another (less ugly) version for Legacy SQL is with using of JS UDF 

#legacySQL 
SELECT lat, lon, lat_lon
FROM (JS(
  -- input table
  (
    SELECT lat, lon FROM
    (SELECT 34.052235 AS lat, -118.243683 AS lon) AS yourTable
  ),
  -- input vars
  lat, lon, 
  -- output schema
  "[
    {name: 'lat_lon', type: 'STRING'},
    {name: 'lat', type: 'FLOAT'},
    {name: 'lon', type: 'FLOAT'}
    ]",
  -- the function
  "function(row, emit) {
    emit({
      lat_lon: row.lat.toString().concat(';').concat(row.lon.toString()),
      lat: row.lat,
      lon: row.lon
    })
  }"
))

And, finally, if by chance - you will decide to migrate to Standard SQL, use of FORMAT makes life much more easier (thanks to Elliott for commenting on this) 

#standardSQL 
SELECT FORMAT('%t;%t', lat, lon) AS lat_lon 
FROM
  (SELECT 34.052235 AS lat, -118.243683 AS lon) AS yourTable

Upvotes: 2

Related Questions