MapInfo Pro

 View Only
  • 1.  MapInfo Monday: Proximity Search

    Employee
    Posted 3 days ago
    Edited by Peter Møller 3 days ago

    MapInfo Pro has great support for running SQL queries. You can do this through various dialogs that allow you to build the query simply by selecting from dropdown values or you write more advanced queries by hand through the SQL Select dialog or the SQL Window.

    Then you can combine your Select statement with MapBasic statements to convert your Select statement into a MapBasic script. We will investigate this today.

    Today's article originated from a request on Precisely Ideas: Proximity Search created by @Caroline Hilton from our partner Pelican GeoGraphics Ltd. The base request was this: "Find objects which have an identical object (or even a different object) within a given distance, in the same table."

    As Caroline points out in the request the problem lies in joining a table with itself. This is where the script shows its value. It allows you to combine a Select statement with other statements such as saving a table into a copy.

    The structure of my script looks like this:

    1. Save a copy of the input table
    2. Create buffers around the objects in the copy table
    3. Update a column in the copy table with a count of objects in the original table
    4. Select the records from the original table that are inside a buffer with a count higher than 1

    Oh, and Caroline also suggested making the script flexible to allow the user to select the input table and set the distance and tolerance. Of course! We will use the variables in the SQL Window for this.

    Happy #MapInfoMonday!

    The Find Nearby Records Script

    Caroline shared a sample dataset of stations created at 30 meters along roads. The issue is that at the road intersections points got quite close. She would like to find the points that are within 20 meters of other points to exclude these.

    My final script ended up looking like this.
    Let me take you through the script.
    At the top, I define two variables to hold the table's name and the file holding the buffers around the original objects.
    Dim sTabFile As String
    Dim sTabBuffer As String
    Next, you can see the query I used to create a copy of the input table but replacing the input objects with buffers. The Print statement in the first line writes a comment in the Message window. This was added for timing purposes and can be removed. The object keyword after the Buffer expression tells MapInfo Pro to use the expression as the object in the resulting query. The variable sTab is one of the variables allowing the user to pick what table to use. See more in the Variables section below. The fDistance variable also allows the user to configure the script with a search distance.
    Print Time(24) + " Buffer Query..."
    Select q.*, Buffer(q.obj, 36, fDistance, "m") object
    From sTab As "q"
    Into __qBuffers NoSelect
    I save the resulting query into a base table in the temp folder and open this table. I use the two variables, sTabFile and sTabBuffer, to hold the name of the file and the table.
    Print Time(24) + " Saving Buffer Query Into Table..."
    sTabFile = PathToDirectory$(TempFileName$("")) & sTab & " Nearby.tab"
    Commit Table __qBuffers As sTabFile
    Open Table sTabFile
    sTabBuffer = PathToTableName$(sTabFile)
    Close Table __qBuffers

    I add a new column to the buffer table and update this with the count of objects from the original table that are inside the buffer. To get this Add Column statement right, I used the Update Column dialog to run the statement with the MapBasic window open. After this, I could copy the statement from the MapBasic window.

    Print Time(24) + " Adding Column to Buffer Table..."
    Alter Table sTabBuffer
      (Add COUNT_WITHIN Integer)
    
    Print Time(24) + " Updating count in Buffer Table..."
    Add Column sTabBuffer (COUNT_WITHIN)
      From sTab Set To Count(*) Where within
    Commit Table sTabBuffer

    Finally, I can run a sub select query selecting all the records from the original table that are Within an object in the buffer table where the COUNT_WITHIN column has a value larger than the nTolerance variable. I use the AggregateCombine() spatial aggregate to combine all the objects into a single object in the sub select part of the query. This speeds up the query. The third variable used in the script is nTolerance. This allows the user to set the tolerance of how many records should be within the given distance for them to get selected. The typical value would be one meaning that as soon as more than 1 record is within the distance, these records would get selected.

    Print Time(24) + " Querying Table against Buffer Table..."
    Select * From sTab
       Where OBJ WithIn (
          Select AggregateCombine(OBJ) From sTabBuffer 
             Where COUNT_WITHIN > nTolerance)
    
    Close Table sTabBuffer
    Print Time(24) + " All done..."

    Variables

    You can access the variables in a script from the Variable list in the SQL window. If the script already has variables defined they will show up here. You can also use the Edit Variable to open the Edit Variables dialog to add new or change existing variables.

    The variables that you define can be of different types. I created variables of type Table, Float, and Integer. Each type makes the control for these variables behave differently in the Input dialog shown to the user.
    These are the settings for the sTab variable of type Table. The expression in the Values field controls what tables the user can select. The expression I used, allows the user to select normal (or seamless) tables.
    For the variable fDistance, the user can enter a floating value. I have used the Properties field to control the minimum and maximum values.
    For the variable nTolerance, I have set the minimum value to 1 via the Properties field. It makes no sense to allow the user to select values below this.
    With the variables defined in the Edit Variables dialog, I can now use them in my script. In this way, the same script can be used for various tables using various values for the distance and tolerance.

    Running the Script

    When you are working on your script, you can execute the script via the Run button in the SQL Window to test the script as it expands.

    It will show the Find Nearby Records dialog to let the user pick the input table and set the variable.

    When you on the OK button, the script will run through the statements.
    The result will be a selection holding the records that are near other records. As I have the WindowHelper tool running, I can in the statusbar see that the script found 206 records that were near other records.
    In the SQL Window, you can enable Display script result in a new browser window. This will open a browser window with the selection after running the script.
    You can also remove this line from the script if you want to keep the table with the buffers open. Sometimes seeing these can be helpful.
    Maybe the script could be improved in a way to ask the user if he wants to browse the result and keep the buffer table open. Any ideas on how this could be done?
    If you mark the script as a favorite by clicking the small Star in the Scripts list, you can find it in the SQL dropdown menu and run it from there.
    This is handy if you use the script often.
    I have attached the MIS file to this article. Download and unzip it, and load it from the Open a saved script button in the SQL window. If you save the file into the Saved Scripts folder, MapInfo Pro will pick it up automatically. You may have to restart MapInfo Pro before appears.



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



  • 2.  RE: MapInfo Monday: Proximity Search

    Employee
    Posted 3 days ago
      |   view attached

    And here is the Script file.



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

    Attachment(s)

    zip
    Find Nearby Records.zip   908 B 1 version