Happy #MapInfoMonday!
As a followup to last week's post about five basic SQL Select statements, today we will take a look at five spatial queries to get you started with Spatial SQL in MapInfo Pro.
SQL can help you in many ways to analyze your data, find specific records, and even create derived data. So if I were to give you one piece of advice to improve the way you work with data it would be to learn SQL
In last week's article, we went thru five basic SQL statements to get you started. In this article, we will add the spatial context to the basic queries when looking at five basic spatial SQL queries.
1. Extracting coordinates from points
Occasionally you have to share your data with someone who cannot use a MapInfo tab file. In order to include the coordinates of your points, you will have to extract these and include them in the file you are sharing. One way to do this is to create columns for the coordinates and update these with the coordinates of the centroid/the point. You can use the Coordinate Extractor tool to do this.
However, you do not have to create these additional columns. You can extract the values using a SQL Select statement instead.
MapInfo Pro has functions that you can use to extract coordinates from any object. In this example, we are using points and therefore we will focus on the basic functions for extracting the coordinates of the centroid of an
CentroidX(object)
CentroidY(object)
You can find the functions in the Functions List in the SQL Window or if you prefer, you can type the functions directly into the fields where you want to use them. You can also filter the Functions List to narrow down the list to the functions you are looking for.
In the query, I give my expressions a column alias. In this way, this new column name will also be exported as the header to the file when I use the Export function in MapInfo Pro to create a CSV or TXT file from the query result.
I am also specifying exactly which columns I want to extract from the table. I could also give these columns an alias if I needed to rename the columns in the exported file.
And I use a table alias which allows me to refer to the table using that alias instead of the full table name.
The query looks like this:
Select a.StreetName, a.StreetBuildingIdentifier, a.PostCodeIdentifier,
CentroidX(a.obj) As "X", CentroidY(a.obj) As "Y"
From Addresses As "a"
Into Selection
The resulting query looks like this and I can easily export this result to a TXT or CSV file using the Export option on the TABLE tab or through the Export item on the context menu from the Table List.
The benefit of using the SQL Window compared to say the classic SQL Select dialog is the fact that you can combine your select statement with other MapBasic statements. If you don't specify anything, the coordinates will be extracted using the coordinate system of the current session. As you can see above, I was using Latitude/Longitude WGS84. I can just add a single line above my Select statement and force MapInfo Pro to use the same coordinate system as my table:
Set Coordsys Table Addresses
Running the statements in the SQL Window now will extract the coordinates using the coordinate system of the table instead. I can of course also hard code the coordinate system to any preferred coordinate system.
2. Calculating the length of polylines
When working with spatial data, you often need to calculate the size of your spatial objects. Typically, you might think you need to create a column and update this with the size of your spatial object. However, that is a misunderstanding and just gives you another column that you need to make sure is always updated.
SQL Select allows you to inspect the spatial objects and do calculations on these when you need the values.
In this example, we need to know the length of our roads.
First, you need to consider if you want to use the Cartesian or the Spherical calculation method. We recommend always using Cartesian for projected coordinate systems and Spherical for Lat/Long coordinate systems.
Secondly, you have to decide on a distance unit. You can use any of the distance units supported by MapInfo Pro. When you select a function for calculating distances, it will default to your distance unit preference. You can change to meters ("m"), miles ("mi"), kilometers ("km"), or a number of other distance units
Finally, you might also consider adding a rounding to your calculation to avoid going into too much detail.
For calculation of object lengths, MapInfo Pro has these two dedicated functions:
CartesianObjectLen(object, distance_unit)
SphericalObjectLen(object, distance_unit)
Use the Cartesian version for projected data, and the spherical version for data based on Lat/long data.
For rounding values, you can use this function:
The round_to parameter tells the function what value to round to. Often you will use values like 1 to round to an integer value or 0.01 to round to two decimals. However, you can also specify 5 to round to the nearest 5.
The final query where we calculate the length of all the roads looks like this. Note that I have given the calculation result an alias: LEN_M. I have also decided to sort the result using the two columns Mun_No and Road_No. I also use the Round function to round the length of the roads to two decimals.
The query looks like this:
Select r.MUN_NO, r.ROAD_NO,
Round(CartesianObjectLen(obj, "m"), 0.01) "LEN_M"
From Roads As "r"
Order By r.MUN_NO, r.ROAD_NO
Into Selection
The result looks like this:
3. Calculating the area of polygons
You can of course also find functions for calculating the size of polygons. It is similar to working with polylines; you just have to use different functions. When you select a function for calculating areas, it will default to your area unit preference. You can change to square meters ("sq m"), square feet ("sq ft"), hectares ("hectare"), or a number of other distance units.
For calculating the area of objects, MapInfo Pro has these functions:
CartesianArea(object, area_unit)
SphericalArea(object, area_unit)
Use the Cartesian version for projected data, and the spherical version for data based on Lat/long data.
In the query, I decided to use square meters as my area unit and I used to Round function to round the values to the nearest 100 square meters.
The query looks like this:
Select ua.Name,
Round(CartesianArea(ua.Obj, "sq m"), 100) "Area_sqm"
From UrbanAreas As "ua"
Order By ua.Name
Into Selection
4. Aggregating calculations
When you look at the result when calculating the length of the roads, you might notice that some of the combinations of municipality code and road number appear multiple times. That is because a single road is divided into segments, typical from crossing to crossing. If you want to know the total length of each road, you will have to group the roads using the combination of municipality code and road number and aggregate the individual lengths.
Of course, you can do this with SQL Select too.
The aggregation you need to use is called Sum()
. You can also include the Count(*)
aggregation to see how many segments each road consists of.
And you can since MapInfo Pro v2019, even include a combined spatial object too. You can do this using the new AggregateCombine
aggregate.
The query looks like this:
Select ROADNAME
, Sum(CartesianObjectLen(obj, "m")) "SUM_ROAD_LEN_M"
, Count(*) "NUM_SEGMENTS"
From Roads
Group by ROADNAME
Order by ROADNAME
Into Selection
In the resulting query, you can see that each road name now only appears once. The more than 90,000 records have been combined into a bit more than 4,000 records.
5. Filtering data based on spatial conditions
You also you these spatial functions when looking for specific features in your dataset.
One check I often use when looking at a dataset is to find short lines, say lines shorter than 10 meters. These could be potential data errors that need to be visually inspected.
The query basically uses the CartesianObjectLen()
function as a condition but I also include it as a derived column in the output and finally, I sort the result using the value.
You cannot use an expression in the Sort By field but you can refer to the alias you give the expression or you can refer to the number of the column in the resulting query, like "COL2" if it is the second column.
The query looks like this:
Select r.MUN_NO, r.ROAD_NO,
CartesianObjectLen(obj, "m") "LEN_M"
From Roads As "r"
Where CartesianObjectLen(obj, "m") < 10
Order By LEN_M
Into Selection
Do you have any other examples of basic spatial queries you have used or even use often?
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------