MapInfo Professional / Oracle Spatial Integration

Click here to download zip file (~ 60KB)

The zip file above contains several files that will allow you to demo the Oracle Spatial Option in Oracle 8i (and previous versions) against the latest version of MapInfo Professional 5.0.

The included MapBasic program - which should be run from within MapInfo (use the File-->Run MapBasic Program menu choice) will allow you to demo Oracle Spatial from within MapInfo. For instance if you have the debug mode turned on all the queries and sql statements can be printed out to an ASCII file or the screen so the user sees the steps needed to create and populate a Spatial table. You can also visually show how Spatial indexes the data by uploading a MapInfo table to Oracle and then using the 'Manage Spatial Indexes' menu choice to use different tiling levels and you can then use the 'Map Spatial Table's Tiles' menu choice to show the actual tiles visualized on a map - show how a tiling level of 12 differs from a tiling level of 4 or how fixed-sized tiles differ from variable-sized tiles - you can also use the MapInfo menu choice under the 'File' menu called 'Open ODBC table' to show how a query where you want to return all the Spatial objects within the current screen dimensions will vary the time to complete based on the tile level chosen (this last step only works with the pre-8i spatial cartridge since MapInfo 5.0 isn;t integrated yet with Oracle 8i Spatial).

As you can see from the directions below this combination of products isn't the easist to demo (there are a lot of steps you have to complete to even get started), but please call me if you run into any bugs or have any questions. Someone should really have a basic familiarity with MapInfo in order to demo this properly (also MapInfo doesn't work right with the Oracle8i ODBC drivers that ship on the CD) - so you need a listener to be running if you are running againt Oracle8i and you need to use the ODBC drivers that ship with MapInfo. Good luck and thank you to all the Spatial folks who have provided me with assistance.

• oracle.mbx - Mapbasic application to run from MapInfo (File-->Run MapBasic Program)
• oracle.ini - .ini file to use with Mapbasic application
• spatial_mi.sql - script to help access Spatial routines from within MapBasic
• my_sdo_admin.sql - script to help index fixed tiled points

1. make sure Oracle8i is installed including the Spatial option
2. run following scripts from SQL*Plus for complete Spatial install (logged in as mdsys/mdsys)
   
   for 8i these scripts are in $ORACLE_HOME\md\admin\
   
   @sdowin.sql
   @prvtwin.plb
3. install MapInfo products (see above about getting products)
   
   - run setup.exe from MapInfo Professional 5.0 CD and click on 'Install Products...'
   
   - click on 'MapInfo Professional' to install
   - click on 'ODBC Support' to install ODBC drivers (be sure to check 'MapInfo Win32 Oracle7 Driver3.10' in component list)
   - click on 'Data' to install data products
4. run Control Panel->Services to make sure both Oracle Service and Oracle Listener are up and running
5. make sure have good entry in tnsnames.ora to your instance of Oracle (under $ORACLE_HOME\network\admin\ for Oracle 8i) - test it using tnsping.exe
6. run Control Panel-->ODBC to set up a data source for the downloaded ODBC driver
   
   go to 'System DSN' tab and press the 'Add...' button
   select the 'MapInfo Win32 Oracle7 Driver3.10' driver and set up the information under the 'General' tab with your database information (for 'Data Source Name:' use 'MapInfo Win32 Oracle7')
   under the 'Advanced' tab be sure to check 'Enable Scrollable Cursors'
7. run following scripts (included in zip file) from SQL*Plus for features the attached MapBasic application uses
   
   @my_sdo_admin.sql (logged in as mdsys)
   @spatial_mi.sql (logged in as user whose schema will contain spatial tables)
8. run d:\mapinfo\tools\miodbccat.mbx from MapInfo and then go to go to Tools -> ODBC Catalog -> ODBC Catalog... abd click on Create Catalog button (creates MapInfo/MapInfo user and MAPINFO_MAPCATALOG table)

• 'Upload MapInfo Table to Oracle Spatial'
  
  use this option to upload an existing MapInfo table to Oracle (the table needs a column called SDO_GID of type Decimal(10,0) so it can be used as the primary key in Oracle - use the 'Add SDO_GID Column to MapInfo Table Based on RowID' option below to create the column)
• 'Spatial-ize Existing Oracle Point Table'
  
  converts an Oracle table with lat/long data stored in separate columns into an Oracle Spatial table
• 'Select Spatial Points Within Area'
  
  select spatial records (those with points only) from Oracle based on coordinates passed from MapInfo
• 'Calculate Spatial Points Distance from Point'
  
  select the great circle distance of Oracle spatial records (those with points only) based on the coordinates of a selected point from MapInfo
• 'Oracle Ad Hoc Query'
  
  use this option and type out an Oracle query this won't create objects though, plus it can't download them
• 'Show Spatial Table Properties'
  
  use this option to query an existing Oracle Spatial tbale for it's current properties including information on its indexes
• 'Validate 8i Spatial Geometries'
  
  use this option to make sure all the geometries stored in an Oracle 8i Spatial table are valid
• 'Manage Spatial Indexes'
  
  this will re-index an existing Spatial table based on a new tiling level - this has the a lot of the same options as when you use the 'Upload MapInfo Table to Oracle Spatial' dialog, but you have to explicity tell this routine if you want to use the alternative method if indexing points provided in the my_sdo_admin.sql script (only check this option if all geographies in the table are points)
• 'Map Spatial Tables's Tiles'
  
  this will create a MapInfo table of tiles based on an existing Spatial table in Oracle - you can then map this (both fixed-size and variable-sized tiles)
• 'Drop Oracle Spatial Tables'
  
  use this option wisely - all tables associated with a Spatial table will be dropped (for pre-8i Spatial this includes <>_SDOLAYER, <>_SDODIM, <>_SDOGEOM, and <>_SDOINDEX and for both versions the actual attribute table can be dropped
• 'Drop MI Spatial Tables'
  
  use this option wisely - tables downloaded from Oracle Spatial that were created in MapInfo will be deleted (the tables begin with some user defined text and end with either _SDOLAYER, _SDOGEOM, _SDOINDEX, _VAR_TILE, _VAR_TILES, _FIX_TILE, or _FIX_TILES)
• 'Get Node Statistics For Table Objects'
  
  does the same thing as pressing the 'Table Node Statistics' button in the 'Upload MapInfo Table to Oracle Spatial' dialog
• 'Add SDO_GID Column to MapInfo Table Based on RowID'
  
  to upload a MapInfo table to pre-8i Oracle Spatial this program requires you have an SDO_GID column in the MapInfo table first (Decimal(10,0)) - this column must have unique numbers for each row in the table since it will be the primary key column when the attribute table gets uploaded to Oracle - this procedure will automatically add this column to the MapInfo table (if it doesn't exist already) and will update the column to reflect the MapInfo row number - also run this procedure after the MapInfo table has had rows inserted or deleted
• 'Select MapInfo Table (Make Columns UpperCase)'
  
  before you upload a MapInfo table to Oracle Spatial I usually use this option first to make all mixed case column names uppercase case since it's easier to query these tables from within Oracle tools
• 'Create New Fence Layer'
  
  this will create a new fence layer in Oracle Spatial for this MapBasic program to use (or it will clean out the existing one) - this must be run before the 'Select Spatial Objects' menu choice is run against pre-8i Oracle Spatial
• 'Run Tile Estimation' and 'Show Last Tile Estimation Answer'
  
  these menu choices are provided since sometimes the option to estimate the tiling level takes a long time to complete and MapInfo doesn't return back the answer - you can first run the 'Run Tile Estimation' option and then if an answer doesn't come back after the hourglass disappears then run 'Show Last Tile Estimation Answer' to see what was returned
• 'Clear Cosmetic Layer'
  
  use this option to clear a MapInfo map of objects you drew in the cosmetic layer automatically (MapInfo users will understand why this option is here)
• 'Debug Options'
  
  use this option to tell the program whether SQL statements sent to Oracle should be shown on the screen and if you should also send these statements out to an ASCII text file (this really slows down the running of the application for large uploads though)
• 'View Output File'
  
  view the output file in notepad if you used debug mode to log SQL statments to the ASCII file mentioned above
• 'About Oracle'
  
  splash screen
• 'End Oracle'
  
  get rid of 'Oracle' menu (also disconnects the ODBC connection you had to Oracle)

for questions/comments: kgmahoney@yahoo.com   © 2001-2017 kmahoney.com