MapInfo Pro

 View Only
  • 1.  Aggregating Points

    Posted 07-13-2022 05:50
    Hi,

    I have a large table of points (emergency service call out destinations) which I would like to 'thin out'.
    The records are ordered descending in importance (by ".Count", referring to the number of incidents at those locations). 

    I would like MapInfo to:

    1) Fetch the first record
    2) Select all the records from the same table within 250m of it (not including itself)
    3) Delete them (or at least, mark them for deletion if this would cause a packing issue)
    4) Fetch the next record (obviously bypassing those already deleted or marked)
    5) Loop until end of table

    I was going to use a buffer but I'd rather a 'cleaner' way forward like utilizing the code "Select By Location" feature uses - ObjectDistance(). But alas I cannot get that to work.

    Any help would be greatly appreciated. 

    My name is Ryan and I work on behalf of emergency services around the world building simulation models which help improve their response times and cope with changes to their service and environment.

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------


  • 2.  RE: Aggregating Points

    Employee
    Posted 08-01-2022 03:07
    Edited by Peter Møller 08-01-2022 03:50
    Hi Ryan

    Here's a good starting point for you

    Include "MapBasic.def"

    Dim nRowID, nIncidents As Integer,
        sInputTable, sState As String,
        oPoint, oBuffer As Object

    sInputTable = "ThePointTable"

    Select * From sInputTable
      Order By NUM_INCIDENTS Desc
      Into _QUERY__SORTED NoSelect

    Fetch First from _QUERY__SORTED
    Do Until EOT(_QUERY__SORTED)
      nRowID = _QUERY__SORTED.ROWID
      sState = _QUERY__SORTED.STATE
      nIncidents = _QUERY__SORTED.NUM_INCIDENTS
      If sState = "" Then
        Print FormatNumber$(nRowID) & " of " & FormatNumber$(TableInfo(_QUERY__SORTED, TAB_INFO_NROWS)) & ": " & FormatNumber$(nIncidents)

        Update _QUERY__SORTED
          Set STATE = "Keep"
          Where ROWID = nRowID

        oPoint = _QUERY__SORTED.OBJ
        oBuffer = Buffer(oPoint, 24, 250, "m")

        Select * From sInputTable
          Where oBuffer Contains OBJ
          And STATE = ""
          Into _QUERY__TO__DELETE NoSelect Hide

        If TableInfo(_QUERY__TO__DELETE, TAB_INFO_NROWS) > 0 Then
          Print " Thinning " & FormatNumber$(TableInfo(_QUERY__TO__DELETE, TAB_INFO_NROWS)) & " records"
          Update _QUERY__TO__DELETE
            Set STATE = "Delete"
        End If
        Close Table _QUERY__TO__DELETE
      End If

      Fetch Next From _QUERY__SORTED
    Loop

    Close Table _QUERY__SORTED

    Select STATE, Count(*)
      From sInputTable
      Group By STATE
      Order By STATE
      Into Selection
    Browse * From Selection

    You may however find that it will need some additional tweaking as one point might mark other points with a similarly high number of incidents for deletion leaving you with points with a much lower number of incidents.

    Below, you can see a small area of the data that I aggregated using the tool. Note how the point with a value of 21, removes the points with similarly high values: 19, 17, 16, 15.

    My data may be skewed as I'm using addresses and using house numbers as my incident count. That means that the points typically are near points with similar values.


    I have used these names in the code:
    • ThePointTable: Table with points
    • NUM_INCIDENTS: Column holding the number of incidents used for sorting
    • STATE: Column to be updated with state: Keep or Delete. Do note that this column needs to be empty when running the tool
    I hope this helps

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



  • 3.  RE: Aggregating Points

    Posted 08-02-2022 12:21
    Hi Peter, this is brilliant. Thank you so much.

    Regarding your notes, the fact that a "high" incident location might absorb another for our purposes is fine - it simply means the absorbed point is already represented by another, more significant point. This 'master' point table will later be joined yo the non-aggregated incident table anyway, so only spatial information is being removed. 

    Thanks again, this gives me a great starting point for this and similar projects. 

    Ryan

    ------------------------------
    Ryan Cook
    Knowledge Community Shared Account
    ------------------------------



  • 4.  RE: Aggregating Points

    Employee
    Posted 08-03-2022 03:09
    Glad to hear you found this helpful, Ryan.

    Would love to hear more about your use case here. Maybe you would be willing to write a bit more about it once you have it working?

    Thanks

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