MapInfo Pro

 View Only

MapInfo Monday: Offsetting Points through SQL Select

  • 1.  MapInfo Monday: Offsetting Points through SQL Select

    Posted 05-15-2023 08:51

    Happy #MapInfoMonday.

    In today's article, I will show you how you can create virtual objects through SQL Select to create better-looking maps without too much clutter.

    Sometimes your points are just too close together, especially when you want to display these points with bigger icons from the Customer Symbols folder. This leaves you with two options: Move the points to a different position than where actually are located or use smaller symbols in your map. None of these aren't the perfect solution.

    I found a third option: Create a copy of your points, move this, and create a pointer back to the actual location of the point. It will look like this:

    I found myself in this situation where the points were too close. Below you can the initial map where many of my symbols overlap eachother.

    At first, my idea was to create a copy of my table and move the points manually. But then I realized that I had to maintain this table for all changes I made to the original and for all points I added too. I know my self good enough to see that wouldn't work.

    Then I started considering using a Select statement to create this copy of my dataset. I quickly saw some benefits in this. I could maintain the two datasets through my base table, and with a few additional attributes, I could control how the copy would behave or look.

    I decided to use the CartesianOffset() function to move my points to a different location. This function takes the original point as input, and the direction and distance you want the point moved. This makes it easy to set up how to move the points if they clutter. If you keep these two values to zero, the point will basically stay where it is. If you are using Latitude/Longitude data, use the OffSet() function instead.

    In the image above, you can see my table with the two added columns at the end Offset_Direction and Offset_Distance. I ended up mostly varying the direction spreading the points around a set of cluttered points. Most of the points are offset the same distance. Only a few are offset 200 meters instead of just 100 meters If you can use the same distance overall, you don't need the column for the distances.

    Now I have the information needed to create a new query from the original table using the CartesianOffset() function.

    This is the query I ended up using to offset my points based on the values in the two columns:

    Select o.*, CartesianOffset(o.obj, o.Offset_Direction, o.Offset_Distance, "m") object
    From Oksen_Opholdssteder As "o"
    Into _oksen_opholdssted NoSelect

    After running the query, I add the query table to my map and use the Type column to add a thematic layer to this new layer. For each type, I select a custom symbol that I have added manually to the Custom Symbols folder.

    Here's the result where you can see the points being offset. I ended up with this map after a few trials and errors after which I changed the direction and a few times also the distance for the points to avoid the symbols overlapping.

    But this map also made me realize that it would be good to have a line connecting the bigger symbols back to their actual location. This would make it easier to find out where the symbol belongs.

    So I needed another Select statement to create these lines based on the two tables, the original table and the query table with the offset points. Below you can find the statement I created to connect the objects in the two tables. Note that the link between the two tables is the ID attribute which needs to be unique.

    Select o.*, CartesianConnectObjects(o.OBJ, t.OBJ, 1) object
    From Oksen_Opholdssteder As "o", _oksen_opholdssted As "t"
    Where o.ID= t.ID
    Into _oksen_opholdssted_link NoSelect

    Again, I add the resulting query table to my map, and I can style it like any other layer.

    Below you can see a different version of my map where I have styled the pointer as a red dotted line with a white background. I control this via the Style Override for the layer.

    Finally, remember to save this to a workspace. The workspace will store the query tables and rerun these the next time you open the workspace.

    I hope you found this small cartographic tip useful.

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