MapInfo Pro

Expand all | Collapse all

Quick-Fire Webinar: Spatial SQL

  • 1.  Quick-Fire Webinar: Spatial SQL

    Posted 11-21-2018 02:48
    Next week I'm presenting at another Quick-Fire webinar. This time the focus will be around Spatial SQL.

    Any suggestions to what I should talk about or what you want to hear?
    I'm certainly open to suggestions and input.

    My initial idea was to start with some basic queries to calculate the size of object and extract coordinates from objects. We can then move on to converting objects or creating new objects based on the input objects and finally do some sub select and spatial joins and show how you can work with derived spatial objects.

    But as I said, I'm more than happy to cover specific request too.

    Find the webinar here and make sure you are signed up: Quick-Fire Webinear: Spatial SQL

    Peter Horsbøll Møller
    Pitney Bowes

  • 2.  RE: Quick-Fire Webinar: Spatial SQL

    Pitney Bowes
    Posted 11-22-2018 03:21
    Edited by Hakan Karlsson 11-22-2018 03:21
    Sounds like a great session Peter!

    I remember your post in the old MapInfo journal where you created a buffer and made a spatial join on the fly in SQL (slurry tanks near water stream, was it?). Very nice example, but I would not be able to write it without some repetition. :-)

    I do have one other idea, a question that came at an earlier Quick Fire seminar. Not sure if we can accomplish this using spatial SQL? Imagine you have a boundary, for example, an admin area. Inside you have a few region objects, maybe circular buffers, or catchments around schools. Can we somehow calculate the proportion of the larger area that is covered by 0, 1, 2, etc. of these buffers/catchments? Or for starters, can we at least calculate the proportion that is not covered by any of them?

    Hakan Karlsson
    Presales, Pitney Bowes Sweden

  • 3.  RE: Quick-Fire Webinar: Spatial SQL

    Posted 11-22-2018 09:37
    You are pushing it, Håkan :-)

    Unfortunately, MapInfo Pro doesn't come with a spatial aggregate function that can merge/combine objects - at least not out of the box.

    But with v17.0.1 you are able to create your own functions using MapBasic and publish these to MapInfo Pro.

    Here is the scenario, you talked about:
    A municipality is trying to compare the school district coverage. Is this case they have both primary and secondary schools (the blue circles) inside their admin boundary (the blue polygon).

    They want to know the total coverage of school districts compared to the admin boundary but are also looking for a detailed area based on school type.

    The problem is that the school districts overlap and some of the district is also located outside the admin boundary. That means we can't just calculate the area of these.

    MapBasic to the rescue

    I created a small function that can combine objects from a table and return the combined object:

    Function OBJCombineFromTable(   ByVal sTab As String, ByVal sCol As String , ByVal sID As String ) As Object
    Dim oCombined As Object, sCmd As String
    OnError GoTo ErrorOccured
       sCmd = "Select * From " & sTab
       If sCol Then
          If ColumnInfo(sTab, sCol, COL_INFO_TYPE) = COL_TYPE_CHAR Then
             sCmd = sCmd & " Where " & sCol & " = " & Chr$(34) & sID & Chr$(34)
             sCmd = sCmd & " Where " & sCol & " = " & Chr$(34) & sID & Chr$(34)
           End If
        End If

       If SystemInfo(SYS_INFO_MIVERSION) >= 1700 Then
           sCmd = sCmd & " Into __TO__COMBINE NoSelect Hide"
          sCmd = sCmd & " Into __TO__COMBINE NoSelect"
       End If

      ' Print sCmd Run Command sCmd
      Set Progressbars Off
       Create Object As Union
          From __TO__COMBINE
          Into Variable oCombined
       Set Progressbars On

       Close Table __TO__COMBINE
       OBJCombineFromTable = oCombined
       Exit Function
       Call ERRCreate(Err(), Error$(), "OBJCombineFromTable")
       Call ERRPrint()

    End Function

    Next, I published this function so that it will appear in the function lists in the SQL Select dialog:

       nResult = RBNRegisterFunctionAsPublic("OBJCombineFromTable", "OBJCombineFromTable", "Combines objects from a table, optionally specify a column and a value") 

    And now I can use it from with in the SQL Select dialog:

    Here is the basic query that calculates the total area of the admin boundary, the area of the school districts and the area where there is no school district:

    The query itself looks like this:
    Select Admin.NAME
       , CartesianArea(obj, "sq m") "AdminArea"
       , CartesianArea(OBJCombineFromTable("Schools", "", ""), "sq m") "SchoolArea"
       , CartesianArea(Erase(OBJ, OBJCombineFromTable("Schools", "", "")), "sq m") "NoSchoolArea"
       From Admin
       Into Selection

    The query where I group the result by the level of the school looks like this:

    Select Admin.NAME, Schools.NAME
       , CartesianArea(Admin.obj, "sq m") "AdminArea"
       , CartesianArea(OBJCombineFromTable("Schools", "NAME", Schools.NAME), "sq m") "SchoolArea"
       , CartesianArea(Erase(Admin.OBJ, OBJCombineFromTable("Schools", "NAME", Schools.NAME)), "sq m") "NoSchoolArea"
       From Admin, Schools
       Where Admin.Obj Intersects Schools.Obj
       Group By Admin.NAME, Schools.NAME
       Into Selection

    The query result looks like this:

    By default my tool is using a lat/long coordinate system but I can force it to use a cartesian/projected coordinate system by running this statement in the MapBasic window. If I don't the area calculations seem to fail to return a cartesian area:
    Print WINHSetCoordsysUsingTable("Schools")

    Peter Horsbøll Møller
    Pitney Bowes