Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, i have this kind of problem:
i need to match 2 coordinates Latitude and Longitude in between a set of coordinates (given from another table), to find out where Latitute and Longitue are placed
The second table gives me this kind of information: latitudeMIN, latitudeMAX, longitudeMIN, longitudeMAX , Town, State....
So i need to do this kind of work in the script:
IF LatitudeMIN<= Latitude and LatitudeMAX>=Latitude AND LongitudeMIN<=Longitude and LongitudeMAX>=Longitude
THEN i join the information STATE,TOWN etc...
it's like a double interval match, how can i work this out without joining the 2 massive tables in one? with big data this would get a memory crash.
Thank you in advance
Luca
Ook, i've done it, there was also another problem, the range of 2 coordinates wasn't distinct.
meaning that i had some sets like
latMin latMax
14,13000 14,78000
14,12555 14,78000
14,12000 14,78000
like there are subsets of the same range, then i needed to control latitude and longitude togheter. This is my last working script:
Location:
load *,
AutoNumber(latmin&'_'&latmax&'_'&lonmin&'_'&lonmax,'key_coordinates') as key_coordinates,
autonumber(latmin&'_'&latmax,'key_latitude') as key_latitude;
LOAD lonmin,
latmin,
lonmax,
latmax,
TOWN,
PROVINCE,
FROM
[CoordinateIT.qvd]
(qvd);
MatchLatitude:
IntervalMatch ([Latitude]) load latmin,latmax resident Mappa;
Left join (MainTable) load distinct [Latitude],latmin,latmax, autonumber(latmin&'_'&latmax,'key_latitude') as key_latitude Resident MatchLatitude;
drop tables MatchLatitude;
MatchLongitude:
IntervalMatch ([Client Longitude],key_latitude) load lonmin,lonmax,key_latitude resident Mappa;
Left join (MainTable) load distinct * Resident MatchLongitude;
drop tables MatchLongitude;
drop field key_latitude;
rename Table MainTable to MainTable_tmp;
MainTable:
load *, AutoNumber(latmin&'_'&latmax&'_'&lonmin&'_'&lonmax,'key_coordinates') as key_coordinates Resident MainTable_tmp;
left join load
key_coordinates,
TOWN,
PROVINCE
Resident Location;
Drop Tables Location,MainTable_tmp;
noone has a clue?
could you post a small example of the info? I'll give it a shot
Main Table:
Device | Dwl Kbps | Upl Kbps | Latency | Latitude | Longitude |
Device1 | 696 | 1 | 286 | 45.056 | 12.058 |
Device2 | 236 | 950 | 249 | 41.489 | 12.891 |
Device3 | 769 | 333 | 159 | 37.853 | 13.773 |
Device4 | 1,807 | 800 | 133 | 41.002 | 17.005 |
Location Table:
lonMIN | latMIN | lonMAX | latMAX | Town | Province |
7,508528 | 44,38173 | 7,521298 | 44,39072 | Cervasca | CN |
7,508528 | 44,21985 | 7,521298 | 44,22884 | Vernante | CN |
7,541318 | 45,96454 | 7,546837 | 45,97354 | Bionaz | AO |
7,534067 | 45,44293 | 7,546837 | 45,45193 | Sparone | TO |
7,534067 | 45,24508 | 7,546837 | 45,25408 | Mathi | TO |
7,534067 | 45,2271 | 7,546837 | 45,23609 | Fiano | TO |
7,534067 | 44,43569 | 7,546837 | 44,44468 | Cuneo | CN |
7,534067 | 44,38173 | 7,546837 | 44,39072 | Cuneo | CN |
these are examples of the 2 tables, i need to determine for all the Devices which is the town they are located in, to do so i need to check if its latitude is in the interval between latMIN and latMAX AND at the same time if its longitude is between lonMIN and lonMAX
Ok let me take a look at the info.
Beforehand, what alternative are you seeking to using a intervalmatch/joining/linking of tables solution?
For what i know, the only solution i could get on mind was to join the 2 tables and then load all the records checking the expression "IF Latitude>=latMIN and Latitude<=latMAX and longitude>=lonMIN and longitude<=lonMAX".
But joining a table of 20k records with another with 300k records, i guess it would burn my memory XD
That's why i was seraching if someone had the same kind of problem and how it could be solved differently.
Lucas,
instead of joining both tables and doing a subsequent load with a where clause, you could try using interval match, like
TAB1:
LOAD recno() as ID, * INLINE [
Device Latitude Longitude
Device1 45,056 12,058
Device2 41,489 12,891
Device3 37,853 13,773
Device4 41,002 17,005
] (delimiter is ' ');
TAB2:
LOAD * INLINE [
lonMIN latMIN lonMAX latMAX Town Province
11,508528 44,38173 13,521298 46,39072 Cervasca CN
9,508528 40,21985 13,521298 44,22884 Vernante CN
13,541318 33,96454 13,946837 37,97354 Bionaz AO
16,534067 40,44293 17,546837 45,45193 Sparone TO
7,534067 45,24508 7,546837 45,25408 Mathi TO
7,534067 45,2271 7,546837 45,23609 Fiano TO
7,534067 44,43569 7,546837 44,44468 Cuneo CN
7,534067 44,38173 7,546837 44,39072 Cuneo CN
] (delimiter is ' ');
// solution 1
Join IntervalMatch (Latitude) load latMIN, latMAX Resident TAB2;
Join IntervalMatch (Longitude) load lonMIN, lonMAX Resident TAB2;
If you don't like that, you could also doing something like this (should not use too much memory, but it's probably quite slow due to the loop / repeating resident load):
//solution 2
Let vDevNum = FieldValueCount('Device');
For i = 1 to $(vDevNum) step 1
//left join (TAB1)
RESULT:
load
Lookup('Device','ID',$(i),'TAB1') as Device, Town, Province
Resident TAB2 where
Lookup('Latitude','ID',$(i),'TAB1') <= latMAX and
Lookup('Latitude','ID',$(i),'TAB1') >= latMIN and
Lookup('Longitude','ID',$(i),'TAB1') <= lonMAX and
Lookup('Longitude','ID',$(i),'TAB1') >= lonMIN
;
next
drop table TAB2;
left join (RESULT) load * resident TAB1;
drop table TAB1;
Probably there is even some better approach..
Regards,
Stefan
edit: I edited the bounding boxes for the locations table, just to get some matches.. Don't use these for navigation 😉
Hi swuhel,
i was just making up something exatly like your solution1, but my computer completly freeze when it tries to do the 2nd join (core i3, 4GB Ram).
I was working around that solution right now...
I will surely try your second suggest if i miss the targer
Thank you very much
Luca
Ook, i've done it, there was also another problem, the range of 2 coordinates wasn't distinct.
meaning that i had some sets like
latMin latMax
14,13000 14,78000
14,12555 14,78000
14,12000 14,78000
like there are subsets of the same range, then i needed to control latitude and longitude togheter. This is my last working script:
Location:
load *,
AutoNumber(latmin&'_'&latmax&'_'&lonmin&'_'&lonmax,'key_coordinates') as key_coordinates,
autonumber(latmin&'_'&latmax,'key_latitude') as key_latitude;
LOAD lonmin,
latmin,
lonmax,
latmax,
TOWN,
PROVINCE,
FROM
[CoordinateIT.qvd]
(qvd);
MatchLatitude:
IntervalMatch ([Latitude]) load latmin,latmax resident Mappa;
Left join (MainTable) load distinct [Latitude],latmin,latmax, autonumber(latmin&'_'&latmax,'key_latitude') as key_latitude Resident MatchLatitude;
drop tables MatchLatitude;
MatchLongitude:
IntervalMatch ([Client Longitude],key_latitude) load lonmin,lonmax,key_latitude resident Mappa;
Left join (MainTable) load distinct * Resident MatchLongitude;
drop tables MatchLongitude;
drop field key_latitude;
rename Table MainTable to MainTable_tmp;
MainTable:
load *, AutoNumber(latmin&'_'&latmax&'_'&lonmin&'_'&lonmax,'key_coordinates') as key_coordinates Resident MainTable_tmp;
left join load
key_coordinates,
TOWN,
PROVINCE
Resident Location;
Drop Tables Location,MainTable_tmp;