MapInfo Pro

 View Only

MapInfo Monday: Connecting Objects in two Tables with a Line

  • 1.  MapInfo Monday: Connecting Objects in two Tables with a Line

    Posted 11 days ago
    Happy #MapInfoMonday!

    Working with GIS is often about finding or highlighting the spatial relationship that isn't always visible. The spatial relationship you are looking for may not always be that objects in one table should intersect or be within objects in another table. Sometimes they just need to be near each other, within a given distance. I discussed this in an earlier #MapInfoMonday post on finding objects nearby.​

    To take this to the next level, you may sometimes want to illustrate this relation more clearly by connecting the objects with a line. This shows the relationship in an easy-to-understand way. We have earlier discussed a dedicated tool, the SpiderGraph tool, that can create these lines between objects in two tables where you have a value that connects the two tables. That could be hospitals and patients, schools and students, or as in this example shops and customers.

    In this article, we do however not have any attribute that connects the two tables. We are looking to find objects nearby and highlight the relationship with a line. In our example, it is addresses and cell towers.

    We will use Select by Location to create the initial query that joins the two tables and finds the addresses that are closer than 2,000 meters to a cell tower.

    The resulting map gives you an idea of where the addresses are within a 2,000-meter distance of a cell tower. All the green dots are the addresses that a near a cell tower. What this map however doesn't show is the relation between the addresses and the cell towers: which cell tower is the address nearby? Is the address nearby more than one cell tower?

    Let's extend the query to all show this information.

    From the SQL dropdown, select SQL to open up the SQL Window.

    From the list of Scripts, you can now select the first one from the top. That would be the most recent SQL statement executed. When You select the script it will get loaded into the Edit field. After loading it, I recommend that you right-click in the Edit field and use Format to format the statement.

    Now we can modify the statement slightly. I only modify the elements following the Select keyword. I change it to select all columns from the cell tower table and all columns from the address table:
    Cell_Towers.*, Addresses.*

    I could have changed this to only include a few columns from each table.

    I also add a new expression that will create the line between the cell tower and the address that are "nearby" each other:
    CartesianConnectObjects(Addresses.obj, Cell_Towers.obj, 1) Object

    Note the keyword Object that I added after that expression. This keyword tells MapInfo Pro to use this spatial expression as the object for the query result. If you omit this keyword the resulting query will use the object from the first table, the addresses, and not the calculated line.

    Here is the final Select statement:
    Select Cell_Towers.*, Addresses.*
    , CartesianConnectObjects(Addresses.obj, Cell_Towers.obj, 1) Object
    From Addresses, Cell_Towers Cross Join
    Where CartesianObjectDistance(Addresses.obj, Cell_Towers.obj, "m") <= 2000
    Into Selection

    Often I would simplify this by giving my tables an alias that I can use throughout the statement instead of the actual table name. It makes the query easier to read for me and it also makes it easier to replace one table with another as I only have to change the table name in one place:
    Select ct.*, a.*
    , CartesianConnectObjects(a.obj, ct.obj, 1) Object
    From Addresses As "a", Cell_Towers As "ct" Cross Join
    Where CartesianObjectDistance(a.obj, ct.obj, "m") <= 2000
    Into Selection

    The resulting map now displays lines between the addresses and the cell towers that are "nearby". You can also see where an address is nearby multiple cell towers as it will have multiple lines

    I hope this gives you some ideas on how you can visualize the spatial relationship in your data.

    Stay tuned for more tips for MapInfo Pro in next week's #MapInfoMonday post!​

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