| System Category | GIS |
|---|---|
| Source | Astun Spatial Data Warehouse |
| Available since | |
| Organisation |  |
|
|
|
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 on the local machine. This can either be the MapInfo Professional 64-bit version or the 32-bit version (as shown below).
Download the Windows PostgreSQL ODBC drivers from www.postgresql.org or click the Download link below that applies to your version of MapInfo.
Download links:
64-bit download psqlodbc_09_06_0400-x64.zip
32-bit download psqlodbc_09_00_0310.zip.
MapInfo Professional v12.0 is a 32-Bit application and as such cannot access 64-bit ODBC drivers. If you try this you will get an 'architecture mismatch' error message in MapInfo Professional similar to the following
|
| MapInfo 64-bit | MapInfo 32-bit | |
|---|---|---|
| Use the application Search to look for ODBC Data Sources and select the 64-bit result. | 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
| |
Select the System DSN tab and click Add... |
Select the System DSN tab and click Add... | |
Select the PostgreSQL ANSI driver and click Finish. |
Select the PostgreSQL ANSI driver and click Finish. | |
Enter the details of the PostgreSQL database you wish to connect to and click Test. |
Enter the details of the PostgreSQL database you wish to connect to and click Test. | |
| ||
| Click Save. | Click Save. | |
The user has experience with MapInfo Professional, PostGIS, ODBC configuration and relevant Administrator rights on the client PC |
| MapInfo 64-bit | MapInfo 32-bit |
|---|---|
From the Open menu select Database Table.
| From the MapInfo menu go to File -> Open DBMS Connection
|
Select ODBC and click New... |
Select ODBC and click New... |
Click on the Machine Data Source tab and select the correct connection for your database and click OK. |
Click on the Machine Data Source tab and select the correct connection for your database and click OK. |
Now you have a connection to the database you can Open various tables that have been loaded.
| Now you have a connection to the database you can Open various tables that have been loaded.
|
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.Click the Projection... button.
| MapInfo 64-bit | MapInfo 32-bit |
|---|---|
Select New Table from the TABLE ribbon.
| Select New Table from the File menu. |
Check the Open New Mapper box and select the Create New radio button to create a new table structure. Click Create... |
Check the Open New Mapper box and select the Create New radio button to create a new table structure. Click Create... |
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. Click the Projection... button. |
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. Click the Projection... button. |
Select the Category British Coordinate Systems and then click the British National Grid [EPSG: 2700] Category Member and click OK. | Select the Category British Coordinate Systems and then click the British National Grid [EPSG: 2700] Category Member and click OK. |
| Click Create. | 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. |
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. |
n the New DBMS Table Options dialog under the General tab select ogc_fid as the Primary Key column. Click on the Spatial tab.
Select PostGIS as the Index Type and sp_geometry as the Index Column. 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. | n the New DBMS Table Options dialog under the General tab select ogc_fid as the Primary Key column. Click on the Spatial tab.
Select PostGIS as the Index Type and wkb_geometry as the Index Column. 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. |
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 |
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); |
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)
As the Postgres SDW database is commonly Unicode which is not fully supported by MapInfo Professional, character fields over 127 characters long (half of 254) are not currently editable. |
