topic Re: coordinate conversion in QlikView

coordinate conversion

Thu, 30 Jan 2014 12:41:32 GMT

Greetings everyone

I'm stuck in the following problem :

I have 2 columns with geo coordinates (latitude and longitude), expressed in decimal notation, and i need to convert them in sessagesimal notation (prime, minutes and seconds).

Firstly i have started to check whether the decimal number is positive or negative to get north or south (west/east) for each entry, creating the relative column.

Then i need to :

example : given the latitude 43,9608277 (in the column LAT) take 43 and store it in new column as degree; then take the decimal part of 0,9608277 multiply by 60 to get 57,6496669, storing 57 in the new column primes, and finally take the last decimal part of 0,6496669 multiply again by 60 to get 38,98 which is the seconds.

and repeat this for each entry (which are not distinct) in the two columns.

Anyone have any idea about how to do ?

thanks

Re: coordinate conversion

alexandros17 — Thu, 30 Jan 2014 13:05:03 GMT

I would prefer to do such operation during data loading in script but you can do even in an object:

If LAT is 43,9608277 then

Frac(Lat) gives you the deciamal part,

floor(Lat) gives you the left (integer) part

Hope it helps

Re: coordinate conversion

Thu, 30 Jan 2014 13:18:02 GMT

Hi Alessandro

I'm searching a way to do such conversion on loading, in script too.

All i get until now is :

Load

LAT,

subfield (LAT, ',' , 1) as DEGREES,

if ((LAT) > 0 , 'N' , 'S') as POS,

[..]

I'm Trying your suggestion thank you

Re: coordinate conversion

alexandros17 — Thu, 30 Jan 2014 13:36:22 GMT

Fai in questa maniera:

Load

...

LAT,

Floor(LAT) as Degrees

if ((LAT) > 0 , 'N' , 'S') as POS,

[..]

Quasi identico a quanto hai fatto ...

Re: coordinate conversion

Thu, 30 Jan 2014 13:49:07 GMT

Ciao Alessandro,

Grazie per l’ aiuto.

Sto provando a utilizzare floor anziché subfield ma vedo che mi approssima tutti i risultati all’ unità successiva.

Sai se è possibile moltiplicare tutti i valori della colonna LAT per 60 e riportare i risultati in una nuova colonna cosi da poter reiterare il tutto ?

Re: coordinate conversion

alexandros17 — Thu, 30 Jan 2014 14:00:43 GMT

Ciao Fabio,

purtroppo devi definire tutti i campi durante il caricamento (è di sicuro la cosa più veloce),

per quanto concerne il floor, la funzione dovrebbe darti la parte intera di un numero mentre ceil dà il numero intero successivo, guarda la documentazione. se hai problemi sono quiì

Ciao

Re: coordinate conversion

Thu, 06 Mar 2014 09:18:47 GMT

[Solved]

Coordinates:

Load *,

if (not Isnull(WT_LAT) , DEGREE & '° ' & LATPRIME & '` ' & LATSECOND & '`` ' & POSIT ) as NEWCOORDLAT,

if (not Isnull(WT_LONG) , DEGREEB & '° ' & LONGPRIME & '` ' & LONGSECOND & '`` ' & POSITB ) as NEWCOORDLONG;

Load *,

if ((DSECOND) < 0, fabs(ceil (DSECOND)) , fabs(floor(DSECOND))) as LATSECOND,

if ((DSECONDB) < 0, fabs(ceil (DSECONDB)) , fabs(floor(DSECONDB))) as LONGSECOND;

Load *,

TOSECOND * 60 as DSECOND,

TOSECONDB * 60 as DSECONDB;

Load *,

if ((DPRIME) < 0, fabs(ceil(DPRIME)) , fabs(floor(DPRIME))) as LATPRIME,

if (DPRIME < 0 , DPRIME - ceil (DPRIME) , DPRIME - floor (DPRIME)) as TOSECOND,

if ((DPRIMEB) < 0, fabs(ceil(DPRIMEB)) , fabs(floor(DPRIMEB))) as LONGPRIME,

if (DPRIMEB < 0 , DPRIMEB - ceil (DPRIMEB) , DPRIMEB - floor (DPRIMEB)) as TOSECONDB;

Load *,

TOPRIME * 60 as DPRIME,

TOPRIMEB * 60 as DPRIMEB;

Load *,

if (WT_LAT < 0 , WT_LAT - ceil (WT_LAT) , WT_LAT - floor (WT_LAT)) as TOPRIME,

if (WT_LONG < 0 , WT_LONG - ceil (WT_LONG) , WT_LONG - floor (WT_LONG)) as TOPRIMEB;

Load *,

if ((WT_LAT) < 0, fabs(ceil (WT_LAT)) , fabs(floor (WT_LAT))) as DEGREE,

if ((WT_LONG) < 0, fabs(ceil (WT_LONG)) , fabs(floor (WT_LONG))) as DEGREEB;

Load *,

if (WT_LAT < 0, 'S', 'N') as POSIT,

if (WT_LONG < 0, 'W' , 'E') as POSITB;

LOAD

WT_LAT,

WT_LONG

FROM