You should never underestimate the power of using SQL when working with data, be that alphanumerical data or spatial data. SQL can help you in many ways to analyse your data, find specific records and even to create derived data.
In the earlier article, we went thru five basic SQL statement to get you started. In this article, we will add the spatial context to the basic queries when we look 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 Select dialog or if you prefer, you can type the functions directly into the fields where you want to use them.
See Attachment
In the query, I give my expressions an 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.
See Attachment
The query looks like this:
Select StreetName, HouseNo, PostalCode,
CentroidX(obj) "X", CentroidY(obj) "Y"
From Addresses
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.
See Attachment
2. Calculating 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 the distance unit preference of yours. 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 you 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)
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: LENGTH_M. I have also decided to sort the result using the ROADNAME column. I also use the Round function to round the length of the roads to two decimals.
See Attachment
The query looks like this:
Select ROADNAME, Round(CartesianObjectLen(obj, "m"), 0.01) "LENGTH_M"
From Roads
Order by ROADNAME
Into Selection
In a floating browser window, the result looks like this:
See Attachment
3. Calculating 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 the area unit preference of yours. 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)
In the query, I decided this time to use square meters as my area unit and I used to Round function to round the values to the nearest 100 square meters.
See Attachment
The query looks like this:
Select UrbanAreaName, Round(CartesianArea(obj, "sq m"), 100) "Area_sqm"
From Urban_Areas
Order by UrbanAreaName
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 road names 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 name 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.
See Attachment
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.
See Attachment
5. Filtering data based on spatial conditions
You also you these spatial functions when looking for specific features in your dataset.
One check that 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 needs to 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.
See Attachment
The query looks like this:
Select ROADNAME, CartesianObjectLen(obj, "m") "LEN_M"
From Roads
Where CartesianObjectLen(obj, "m") < 10
Order by LEN_M
Into Selection
Do you have any other examples of basic spatial queries you have used?