The usage of MapInfo Pro is huge within the telco industry. MapInfo Pro is used for a wide range of use cases such as monitoring the performance of wireless networks, expansion of networks, and planning fiber rollout. Just to name a few.
@Floyd Pedarse from AT&T asked me for help automating the loading site data and creating sectors.
They have an Excel file that holds all the data for the sites in a given area. This first requirement was to be able to easily load this into MapInfo Pro.
The file holds information about the cell towers and their antennas. From this information, they wanted cell sectors created. Their antennas are grouped and they also wanted this to be visualized when creating the sectors. We decided to do this by offsetting the sectors away from the towers based on the antenna group.
The final visualization would look like this:
Happy #MapInfoMonday!
I created a MapBasic script using the SQL Window in MapInfo Pro to automate the process.
The script prompts the user to select the input Excel file and select where to save the output table for the sites. These are currently the only flexible elements in the script. The rest is hardcoded, such as the radius of the sectors, the name of the Excel sheet, and the name of the columns used.
I have included the script below in a zip file. The file in the zip file is of type MB but holds simple text and can be opened in your preferred text editor. From here you can copy the script and paste it into the SQL Window in MapInfo Pro.
Let me walk you through the script.
At the top, you find several Dim statements. These statements create variables for table names, file names, and more.
Dim sPath As String
Dim sTabSite As String
Dim sTabFileSite As String
Dim sTabSector As String
Dim sTabFile As String
Dim sExcelFileSite As String
Dim fRadius As Float
In the next part, we setup a variable for the radius, fRadius, and prompt the user to specify the input and output files.
Now it's time to open the Excel file into MapInfo Pro, create a copy of it as a NativeX table, and finally open the copy. The variable sTabSite holds the name of the Site table.
Register Table sExcelFileSite TYPE "OGR" TABLE "Sheet1"
DRIVER "XLS" Charset "UTF-8" Cache On
Into sTabFile
Open Table sTabFile
sTabSite = PathToTableName$(sTabFile)
Commit Table sTabSite As sTabFileSite TYPE NATIVEX
Close Table sTabSite
Kill sTabFile
Open Table sTabFileSite
sTabSite = PathToTableName$(sTabFileSite)
The table holds columns with Longitude and Latitude values. We use these to create points marking the position of the cell towers.
Create Map For sTabSite CoordSys Earth Projection 1, 104
Set CoordSys Table sTabSite
Update sTabSite Set OBJ = CreatePoint(Longitude, Latitude)
Commit Table sTabSite
Now we create a copy of the site table to hold the sectors. The variable sTabSector holds the name of the Sector table.
sTabFile = Left$(sTabFileSite, Len(sTabFileSite) - 4) + " Sectors.TAB"
sTabSector = PathToTableName$(sTabFile)
Commit Table sTabSite As sTabFile
Open Table sTabFile
The Sector table has a column for the antenna groups. The column holds numbers from 1 to 7. We use this column to offset the points based on the antenna group and the direction of the antenna. I use the function DTNorth2MathAngle from DrawTools, see below, to convert from a bearing to a direction used by MapInfo Pro. The function Offset lets you move the input object in a given direction a specified distance. The expression used will move the objects half the radius times the antenna group minus half the radius. For the antenna group 1, this will mean: (1 * 100) - 100 = 0. For the antenna group 2, this will mean: (2 * 100) - 100 = 100, and so on.
Update sTabSector
Set Obj = Offset(OBJ, DTNorth2MathAngle(Azimuth), (AntennaUnitGroupId * (fRadius / 2)) - (fRadius / 2), "m")
To create the circular sectors, I will again rely on a custom function from DrawTools: DTCreateCircularSector. I set the styles to be applied to the circular sectors using the Set Style statements.
Set Style Brush MakeBrush (2, 16728064, 0)
Set Style BorderPen MakePen (1, 2, 0)
Update sTabSector
Set Obj = DTCreateCircularSector(OBJ, DTNorth2MathAngle(Azimuth), fRadius, Horizontal_Beam_Width, 36, 2)
Commit Table sTabSector
The next part can be omitted. It just changes the styles of the circular sectors based on the antenna group. You could use a thematic map to do this styling. The function WHAlterBrush is from the WindowHelper tool.
Update sTabSector
Set OBJ = WHAlterBrush(Obj, MakeBrush (2, 13671424, 0))
Where AntennaUnitGroupId = 2
Update sTabSector
Set OBJ = WHAlterBrush(Obj, MakeBrush (2, 57344, 0))
Where AntennaUnitGroupId = 3
Commit Table sTabSector
Finally, I create a new map window with the two tables and set a custom label expression for the Site table.
Map From sTabSite, sTabSector
Set Map Layer sTabSite
Label With iuantAntennaModelNumber+ " " + Azimuth + Chr$(176)
+ " W:" + Horizontal_Beam_Width + Chr$(176)
+ " Group: " + AntennaUnitGroupId
+ " Tilt: " + electricalAntennaTilt + Chr$(176)
In the SQL Window, I give the script a meaningful name and mark it as a favorite script using the star in the Scripts list. This allows me to find the script in the Favorite section of the SQL dropdown and run it from here.
In this way, it's easy to execute the script as I don't have to load it into the SQL window first.
DrawTools and WindowHelper Custom Functions
The script above is using a few custom functions from the two tools: WindowHelper and DrawTools. This means that you need to have these tools installed and running for the script to work.
You can find both tools in the MapInfo Marketplace and install it with a single click of a button.
The functions from DrawTools help me create circular sectors and also convert between bearings and directions. DrawTools also comes with a user interface for creating circular sectors as discussed in a previous #MapInfoMonday article. In this example, we do however take the usage to the next level as we pass in expressions and not just a single column for some of the parameters.
The function from WindowHelper helps me change the style of existing objects. As I mentioned above, this part is only optional so WindowHelper isn't required if you leave out this bit from the script.
As I was talking through this process with AT&T in a call, a new suggestion surfaced. I'll cover this in an upcoming article. Keep watching this space!
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------