MapInfo Pro

 View Only

MapInfo Monday: Opening DBMS Tables

  • 1.  MapInfo Monday: Opening DBMS Tables

    Posted 02-05-2024 05:31
    Edited by Peter Møller 02-05-2024 10:04

    Happy #MapInfoMonday!

    We have now been through how to prepare a spatial database for spatial data and how to register existing spatial tables in the MapInfo MapCatalog. Now we want to see how we can access and open tables from a spatial database into MapInfo Pro.

    Opening a DBMS Connection

    When accessing data in a spatial database, we first need to open a connection to the database.

    From the Open dropdown you can click on Open Database Table which will prompt you to select the connection to use.

    If you don't have an open connection, you will be asked to select the type and then select the data source too. In our example, we are using ODBC but you could also have used Oracle/Orainet for example.

    You can also use the normal Open dialog and here click on the Open DBMS Connection button in the top right corner.
    You will be asked to select the type and then select the data source just like discussed above.

    Using an open DBMS Connection

    If you already have opened the connection to your database using the Open Database Table control in the Open dropdown will take you straight to the next step.

    From the Open dialog, you can also select an already open DBMS connection. You will find these connections in the Files of type list as you can see below.

    A final option is to use the context menu on the Connections list in the Explorer window. If you right-click on your DBMS Connection, you will be able to go directly to the dialog allowing you to open tables from this connection.

    Selecting DBMS Tables to Open

    The next step is to select the tables that you want to open. You do this through the Select One or More Table to Open dialog.

    You can select one or more tables that you want to open. But let me run you through the dialog as it holds a couple of small secrets.

    At the top, you can switch between different open DBMS connections (Database). And in the top right corner, you can even open a new DBMS Connection if needed.

    Just below the Database, you can select the Schema in the database you want to select tables from. Do note that the list is sorted alphabetically so it may start with one that doesn't hold any tables.

    Next, I'd recommend that you check the Common Options to make sure the general settings applied to the tables are okay. These settings consist of how you want to open the tables (Linked or Live with/without Cache), the Folder where the tab files should be created, and the Preferred View when opening the tables.

    Finally, it's time to select the table(s) to open.

    Notice that the list of tables is sorted alphabetically and that the small thumbnail next to the tables shows if the tables are mappable or not and what type of spatial object they hold. The table Buildings is mappable using regions, the table Highways is not yet mappable, and the table Addresses is mappable using points.

    You select the tables to open using the checkbox to the left of the thumbnail and table name. This allows you to select multiple tables.

    When you highlight a single table by clicking on its name, you can use the Style button above to control the style for the table when it's opened into a map in MapInfo Pro. If the table is defined to use a Per Row Style, changing the style here only affects the records where the Per Row Style is empty.

    You can also use the Options button to configure how to open the selected table. Let's look a bit closer at that.

    Open DBMS Table Options

    In the Open DBMS Table Options dialog, you can specify a few things around opening the database table into MapInfo Pro.

    First, you can control the mode in which you query the table. Standard Mode allows you to select the columns to include and the records to bring back.

    If you click on the Column Filter... button, you can specify which columns to bring into MapInfo Pro by selecting them in the Column Picker dialog. By default, MapInfo Pro will bring over all columns specified via the *. You can change this by double-clicking on the fields in the Available list which will add them to the Selected list.

    If you click on the Row Filter... button, you can specify a few conditions your records must meet to be brought over. You are configuring a SQL statement that will be passed over to the database. Below I have specified that the values in the column FOT_ID must be lower than 100000.
    I can also use a spatial condition. If I have a map window open I can as an example use the current extent of the map window in my condition.
    I can also switch to Expert Mode. This will allow me to write a SL statement that will be sent to the database for execution. Keep in mind that the query you write needs to be using the SQL dialect of the database. It's not using MapInfo Pro's SQL. Below,  you can see the default query that is passed over to the database to select all columns and all records from the table called Buildings.
    The remaining controls in the Open DBMS Table Options dialog are similar to the Common Options we discussed earlier. You can however also control the CharSet of the MapInfo table and if you want to use a MapInfo Native Table or a MapInfo Extended Native Table. The benefit of the latter is amongst other things that it can hold far more data both record-wise and column-wise.
    If you are connecting to Oracle there are two specific options that we will not cover in this article.
    Back in the Select One or More Table to Open dialog, you can now click on the OK button to open the selected tables. Remember to set the options for all the tables if you make changes. You can only change these one table at a time.
    Below you can see the table opened into a map window. Note that the table is listed as a Linked table in the Table List in the Explorer Window.

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