MapInfo Pro

 View Only
  • 1.  MapInfo Monday: Five Basic Spatial SQL Select Statements

    Employee
    Posted 06-27-2022 06:16

    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:

    • Round(value, round_to)

    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
    ------------------------------


  • 2.  RE: MapInfo Monday: Five Basic Spatial SQL Select Statements

    Posted 07-07-2022 01:22
    Peter, 

    in the first example, Extracting Coordinates from Points, how do you display the results in DMS?

    ------------------------------
    Charlen McCarthy
    Knowledge Community Shared Account
    ------------------------------



  • 3.  RE: MapInfo Monday: Five Basic Spatial SQL Select Statements

    Employee
    Posted 08-03-2022 02:50
      |   view attached
    Hi Charlen

    Thank you for asking that question.
    There is no straightforward solution to this, unfortunately.

    There is no MapBasic function that can convert a lat/long value to DMS. We need to write our own MapBasic function to do so.

    Luckily, there is the Decimal/DMS Converter tool that can do this. The source code for this tool also comes with MapBasic so it was easy to borrow the code for this conversion.

    In order to make this functionality accessible directly in MapInfo Pro, in a SQL Select statement, for instance, you need to build a MapBasic application to publish such functionality to MapInfo Pro.

    The steps are quite easy if you know MapBasic:
    1. Create a new MapBasic application
    2. In the application create the function you want to publish
    3. Use the method IMBXRegisterPublicFunction from IMapInfoPro.def to publish the function
    4. Build and run the MapBasic application
    5. Use the published function.

    Here's a screenshot of how a SQL Select statement using such a function can look

    Here's a close look at the statement:
    Set CoordSys Earth Projection 1, 104
    Select a.Address
    , CentroidX(a.OBJ) "LONG"
    , CentroidY(a.OBJ) "LAT"
    , DMSFromLatLong(CentroidX(a.OBJ), "LONG") "LONG_DMS"
    , DMSFromLatLong(CentroidY(a.OBJ), "LAT") "LAT_DMS"
    From Addresses As "a"
    Into Selection

    The function DMSFromLatLong takes two parameters: the lat/long value and a string that tells the function if the value is lat or long value. That's used to determine the abbreviation for the direction. The tool also has a function that converts a DMS to a lat/long: DMSToLatLong.

    I have attached the tool I created including the source code

    I hope this helps

    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------

    Attachment(s)

    zip
    DMSConverter.zip   13 KB 1 version