Convert Drive Test Log to Google Earth Thematic Map

Hi readers, I would like to share another case of converting drive test (DT) logfiles to be viewed in Google Earth, but this time with thematic color. Some of you guys should know how to create thematic map using Mapinfo or DT post processing tools such as Actix or Nemo Analyzer. Using this kind of thematic maps showing the distribution of signal strength or quality is a common way to understand the network behavior and their problem. It is going to be more exciting to combine thematic maps overlayed in Google Earth.

In this article, I am going to show you how to export logfile data from TEMS Investigation to thematic KML.

1. Export Logfiles to TXT

First, we need to export collected logfiles into .txt file. Choose Text file in the Format dropdown, then on the Setup->Export to Text File window select the Information Elements that you need. Longitude and Latitude is mandatory. In the example, we choose RxLevSubdBm and RxQual. After that simply choose the desired logfiles and let TEMS do the rest.

2. Using MS Access

After the export process finished, we will find the exported TXT file on the targeted directory. We can use Excel to open it. But if the file size is large, then Access is the right option. Let’s open the exported TXT using Access. First, you need to create blank database by clicking on File->New…->Blank Database. The database has been created, now we need to import the exported TXT file into the database. Right click on the Table then choose Import. For Access 2007 users use External Data then choose Text File.

Locate the exported TXT then the Import Text Wizard will appear. ChooseDelimited then click Next. On the Choose the delimiter that separates your fields: choose Tab. Put a check on First Row Contains Field Names and choose Text Qualifier as {None}. Click Next.

On the next window just choose In a New Table. Click Next.

Click Next again. On the next window choose No primary key. Click Next.

Put a name in the Import to Table: field then Finish. Access will import it to a new table.

Now, we need to do simple query. Choose Create query in Design View or click the Design toolbar. Put the table into the query and put the MS, Longitude, Latitude, and RxLev Sub dBm to the fields. On the Criteria fillMS1 or desired MS for choosing the MS/UE we desire. Put also Is not null on the All RxLev Sub dBm’s criteria to select non blank data.

Press CTRL + S to save the query and give a unique name such as RxLev. Run the query and you should see something like this:

Now right click on the query, RxLev in this example and choose Export… Give a name and choose Text File in the Save as Type. Click Export.
Choose Delimited. Next.
Choose Comma  as the delimiter, put a check in the Include Field Names on First Row, and Text Qualifier=None.
In the Export to File rename the .txt extension into .csvFinish.

Exporting CSV into Thematic KML

We need csv2kml, a fantastic tools created by CoverageTools. You can download it for free in their site or click here.

After installing this software just run it and choose File->Open CSV to locate our CSV file.

On the Source Data choose the IE, for example All-RxLev Sub (dBm).
On the Latitude select Latitude.
On the Longitude select Longitude.

We can set our standard Range Values. For example:

Just put out-of-range fields with value such as -999. What about the color? The easiest way to set the color is click on Save Values after you set the range. Then, simply go to C:\Program Files\coveragetools\csv2kml and open the saved .VAL using Notepad. It is going to be like this:

RSSI [dBm]
 6 
-68,0,347235
-72,-68,00FF00
-76,-72,D0F52F
-80,-76,F5000A
-89,-80,1F8FE
-105,-89,100FE
-999,-105,FFFFFF
-999,-999,FFFFFF
-999,-999,FFFFFF
-999,-999,FFFFFF
Basestation
 10 
 0 ,7B1284
 1 ,6FB490
 2 ,66F799
 3 ,25EADA
 4 ,ADEA52
 5 ,F1700E
 6 ,B10B4E
 7 ,61069E
 8 ,1C81E3
 9 ,5EDFA

Simply change the hexadecimal value according to the desired color. You can refer to this for color hexadecimal value.

Ready for the result? Just click Export button, KML file will be saved in the same directory of the CSV file. Give it a double click and you can see it on Google Earth!