Let’s continue to see some tips on using Mapinfo for Telecom and IT. Today we see an example where we use SQL.
Through a very simple step by step, let’s see how do basic operations involving points and polygons.
And let’s get to …
To begin, as we’ll do operations with points and polygons, we need some sample data.
All files are available for download (sample files for this tutorial) so you can try yourself.
Let’s start with some points of our network (sites).
Then we edit the cosmetic layer. For this, we can click right on the map, and in the layers click for editing.
Tip: a shortcut to select a layer to edit is to click on the ‘Editing’ Label and choose the layer you want.
With this, the toolbar ‘Drawing’ has enabled the buttons - we can draw.
Using the button ‘Polygon’, we draw a polygon around some of our sites.
Tip: When you draw a polygon, if we hold down the SHIFT key, the segments are always horizontal, vertical or at an angle of 45 degrees! This can help a lot when we need to draw using these guidelines.
But our polygon has a solid color fill, and our points are not visible. For convenience, we make the polygon fill color as transparent.
To do this, use the button ‘Select’ toolbar, and we double-click the polygon.
Then comes the dialog box that allows us to edit it. Note: Currently, we are not concerned with changing the forms of the polygon - the edges. Let’s just change your fill, so click the button next to the ‘Style’ label.
Several options can be changed here, as the thickness of the lines of the polygon. But let’s just stick to make the fill color to none, and simply select ‘N’ in ‘Pattern’ ‘Fill’.
Okay, we have our scenario. Of course it is a simple scenario, but serves to understand how SQL can be used in Mapinfo.
Note: Although simple, this scenario can be found in practice. For example: 'What are the sites of my network are in the area of ‘MyRegion’?.
Finding Points within a Polygon
To perform operations with our polygon we’ve just created, we must first save it with any name.
As we create the polygon in cosmetic layer, to save this layer - which in our case is the polygon, open the menu: ‘Map’ -> ‘Save Cosmetic Objects …’.
We save our polygon with an appropriate name, eg ‘MyRegion.TAB’.
If re-access the Control Layer, we see that we have an original layer, with our sites, and a new layer ‘MyRegion’, we just created with the polygon that was drawn on the cosmetic layer.
Now we can make our operations.
First, let’s find what are the sites of my network that are within "MyPolygon’?
Access the Menu: ‘SQL’->’ SQL Select …’.
In the new window, in ‘Tables’ choose ‘Sites’ table, and then the table ‘MyRegion’.
Note that SQL is already ‘built’ on condition ‘Sites.Obj MyRegion.Obj Within’. In other words, the SQL search all fields (* = Select Columns) for MyRegion and Sites tables, where the points - Websites - are within (Within) the polygon - MyRegion.
To organize, save the result of this operation in a new table we call ‘Points_Within_MyRegion’.
Note: You can click the ‘Verify’, and verify that the SQL is valid. You can also save that SQL clicking on ‘Save Template’. SQL can be edited in any simple text editor.
Clicking the OK button, the SQL is executed, and there we have the expected result: the points inside the polygon are selected.
Note: Because the ‘Find Results’ was checked, the result - the new table - was also open.
Finding Points that are NOT inside the Polygon
Okay. We did see how simple operations are done in Mapinfo, when involving points and regions.
But now, if we wnat to find the points that are NOT inside the polygon?
We can also use SQL, making some changes.
In this case, we want to select all points that are NOT inside the object of our polygon. The output will only have data from table ‘Sites’.
The objects of our polygon are obtained by SQL ‘(SELECT obj FROM MyRegion)’.
So we have a SQL of all points whose status indicates that the objects of the same are not in any of the objects of my polygon: ‘Obj NOT WITHIN ANY (SELECT obj FROM MyRegion)’.
Our SQL looks like this:
And running, we again have the expected result.
Note: Notice that visually we see only three points out of our polygon. However, the table has more points sites - sites that were not appearing at the current zoom.
Applying a longer zoom, we see all the sites, as expected.
With that, we saw a simple way to perform operations with objects in Mapinfo, using SQL.
Depending on the needs of your daily work, these operations can greatly simplify this kind of work.
This was another brief tutorial on Mapinfo for Telecom where we could learn how to basic operations using Mapinfo.
I hope you enjoyed it, and until our next meeting!
Download the file used in this tutorial.