Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

singbittoo
Contributor

change min sec coordinates to lat lons

i am getting latitude and longitude as

  

GPS_LATITUDEGPS_LONGITUDE
40:11:0.2

-84:52:1.2

when using GeoMakePoint function in qliksense its not reading this properly, is there any way to convert these into degree latitude longitude coordinates?

1 Solution

Accepted Solutions

Re: change min sec coordinates to lat lons

Hi, to pass minutes seconds to langitude you need to calculate to decimal part to add to the degrees: pass minutes to seconds and calculte the proportion of a full degree (3600 seconds):

=SubField('40:11:0.2',':',1) // Degrees

+((SubField('40:11:0.2',':',2)*60 // Convert minutes to seconds

  +SubField('40:11:0.2',':',3)) // Add seconds

  /3600) // divide seconds by a dregree to get the proportional decimal part

This is using this variables for number format, maybe you need Num#() if you have different settings:

SET ThousandSep=',';

SET DecimalSep='.';

3 Replies

Re: change min sec coordinates to lat lons

Hi, to pass minutes seconds to langitude you need to calculate to decimal part to add to the degrees: pass minutes to seconds and calculte the proportion of a full degree (3600 seconds):

=SubField('40:11:0.2',':',1) // Degrees

+((SubField('40:11:0.2',':',2)*60 // Convert minutes to seconds

  +SubField('40:11:0.2',':',3)) // Add seconds

  /3600) // divide seconds by a dregree to get the proportional decimal part

This is using this variables for number format, maybe you need Num#() if you have different settings:

SET ThousandSep=',';

SET DecimalSep='.';

singbittoo
Contributor

Re: change min sec coordinates to lat lons

i did it like this

RangeSum(num(SubField(text(GPS_LATITUDE),':',1))

,num(SubField(text(GPS_LATITUDE),':',2))/60

,num(SubField(text(GPS_LATITUDE),':',3))/3600) as NEW_LAT,

RangeSum(num(SubField(text(GPS_LONGITUDE),':',1))

,num(SubField(text(GPS_LONGITUDE),':',2))/60

,num(SubField(text(GPS_LONGITUDE),':',3))/3600) as NEW_LON, as if there are nulls the + operator doesn't work i think,

but is there any other way of doing this like when we read date we use DATE# then convert it using DATE in desired format , do we have similar thing for coordinates or can we use some existing functions to do so?

Re: change min sec coordinates to lat lons

Right, better with rangesum.

I'm not sure if this can be done with date() funtions, in that functions the base unit is the day, in this operation base unit is the hour, I think that using dates will also need maths operations to convert to the desired result.

Also, using time functions will cause the seconds to round, losing the decimals and moving the point... or maybe with timestamp... in any case, those are also day based funtions.... maybe with "Interval(DateField)*24"?

Community Browser