Page Comparison

...

• Tables without a geometry type constraint will allow users to insert any geometry type into the table which may not be desirable. Clients such as QGIS and iShare GIS enforce a single geometry type per layer while MapInfo does not. To avoid this a geometry type constraint can be added to the table.

• Default geometry type constraints such as those added by ogr2ogr when data is loaded via a Spatial Data Transformation Workflow Task may be too restrictive for MapInfo. If a table is loaded as MULTIPOLYGON but contains a mix of POLYGON and MULTIPOLYGON features (as is quite common) saving an edit to a polygon in MapInfo will fail as the update statement issued by MapInfo Professional will attempt to insert a POLYGON which will fail the constraints check. This can be overcome by dropping the MULTIPOLYGON constraint and replacing it with a check for MULTIPOLYGON or POLYGON

  Code Block
  language sql title Drop existing constraint and add new inclusive geometry type check
  ALTER TABLE countryside.tpo DROP CONSTRAINT enforce_geotype_wkb_geometry;   ALTER TABLE countryside.tpo ADD CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'POLYGON'::text OR geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR wkb_geometry IS NULL);

  
  

• MapInfo Professional has a hard limit to the total number of characters defined across all character fields in a table. If the value of adding the size of all character fields combined is greater than 4049 (discussion on MapInfo mailing list)

...

...

System Category	GIS
Source	Astun Spatial Data Warehouse
Available since	Status subtle true colour Green title 2015
Organisation	Image Removed

...

...

...

...

...

...

• A brief guide to the iShare Spatial Data Warehouse

The iShare Spatial Data Warehouse (SDW) is a database provided by PostgreSQL/PostGIS. Within a SDW there will be various schemas that relate to business functions within an organisation such as 'environmental health' or 'planning'. Within each schema spatial data is held, access to which is controlled by the GIS Administration team of the organisation. They will create user accounts for each user and assign them permissions to read and write to the schemas as appropriate. All tables in each schema have the same security controls.

An end user will be provided with the connection details that are required to connect to the SDW. They will also notify each end user of the schemas that they have read and write access to. 

All users have the right to create new tables in the staging schema. By default any tables that a user creates in the staging schema will not be accessible to other users unless the GIS Administration team have set it to be so. Consider the staging schema a private area where data can be created before it is considered ready for corporate consumption. Once ready the GIS Administration team will move the table to a business schema and it will adopt the permissions of the schema that it resides within.

• Install MapInfo Professional v12.0

Install MapInfo Professional v12 on the local machine.

Image Removed

• Download and Install PostgreSQL 32-Bit ODBC drivers

Download the Windows 32-Bit drivers from www.postgresql.org

Image Removed

Download link:  The version for iShare v5.x / MapInfo v12 is psqlodbc_09_00_0310.zip

• Create a 32-Bit ODBC connection

From the windows desktop go to Start -> Run (or Win+R to open the Run dialog)

Enter the following command to open the 32-bit ODBC Administrator

C:\Windows\SysWOW64\odbcad32.exe

Image Removed

You should now see this dialog:

Image Removed

• Connect to the database

• Select the Add Data Source option on the System DSN tab. 

Image Removed

• Select a PostgreSQL ANSI Data Source.

Image Removed

• Click Finish.

Image Removed

• Example of opening an PostGIS layer in MapInfo

From the MapInfo menu go to File -> Open DBMS Connection

Image Removed

Image Removed

• Select ODBC and click New...

Image Removed

Now you have a connection to the database you can Open various tables that have been loaded.

• Select the Data Source Name and click OK.
• Either use the DBMS toolbar and select the Open DBMS Table option.

Image Removed

• or use File Open (Ctrl+O)

Image Removed

• From the Files of type drop down select your database connection.

Image Removed

• Now you can select the tables you wish to open.

• Example of creating a PostGIS layer in MapInfo

• In MapInfo go to File > New Table
• In the New Table dialog > check the 'Open New Mapper' box and select the 'Create New' radio button to create a new table structure' 

Image Removed

• Click Create.
• In the New Table Structure dialog in the Field Information at the bottom of the dialog add ogc_fid as the Name and select the Type of Integer and click the Add Field button. Now check the Indexed box to add this as an index field. Add any other attribute fields that are necessary.

Image Removed

• Click the Projection button and check projection is set to British National Grid EPSG:27700.
• Click Create.
• In the Create New Table dialog select Save As Type and choose the ODBC connection to the Spatial Data Warehouse, select the staging schema and provide a suitable name for the table.

Image Removed

• In the New DBMS Table Options dialog under the General tab select ogc_fid as the Primary Key column.
• Under the Spatial tab select PostGIS as the Index Type and wkb_geometry as the Index Column.

Image Removed

• Click OK and the new table will be created. MapInfo will notify you that the table has already been added to the map catalog. You can ignore this message.

• Modifying MapInfo workspaces to point to PostGIS layers

Users that are transitioning to iShare SDW will naturally have tab files either holding local data or referencing data held in other DBMSs. One can either edit the workspace .wor file in a text editor to point to a new PostGIS tab file or overwrite the existing tab file with a PostGIS tab file.

...

Check the location of the file you wish to overwrite by opening the workspace .wor file in a text file. The tables are listed towards the top such as;

Open Table "\\path\to\the\tab\file" As <an_alias> Interactive

...

• Open the workspace .wor file in notepad.
• In MapInfo use File > Open Table to select a table from the SDW
• Save the table as a new .tab file.
• Edit the workspace .wor file to use the new location.
  • The alias can remain the same
  • If the table definition is different from the original .tab table definition then you will need to check usage of the table within the workspace such as Map definitions where a field name is used for labelling.
• Save the workspace .wor file.
• Load the workspace into MapInfo to check. MapInfo will report any errors or warnings that can be corrected.

...

Data considerations

The following considerations apply when editing data stored in PostgreSQL within MapInfo Professional.

...

Default geometry type constraints such as those added by ogr2ogr when data is loaded via a Spatial Data Transformation Workflow Task may be too restrictive for MapInfo. If a table is loaded as MULTIPOLYGON but contains a mix of POLYGON and MULTIPOLYGON features (as is quite common) saving an edit to a polygon in MapInfo will fail as the update statement issued by MapInfo Professional will attempt to insert a POLYGON which will fail the constraints check. This can be overcome by dropping the MULTIPOLYGON constraint and replacing it with a check for MULTIPOLYGON or POLYGON

ALTER TABLE countryside.tpo DROP CONSTRAINT enforce_geotype_wkb_geometry;
 
ALTER TABLE countryside.tpo ADD CONSTRAINT enforce_geotype_wkb_geometry CHECK (geometrytype(wkb_geometry) = 'POLYGON'::text OR geometrytype(wkb_geometry) = 'MULTIPOLYGON'::text OR wkb_geometry IS NULL);

...