Welcome to Voyager Help

Use the Search field below or select a Category from the list at the left

Indexing SQL Server Databases



Dependencies

Python (32-bit) must be installed on the Voyager Server machine. Ideally, the Python version should be 2.7.

DO NOT USE EARLIER VERSIONS OF PYTHON

Alternatively, the Python installer shipped with ArcGIS 10.1/10.2 can be used to install Python. NOTE: If ArcGIS 10.1 or 10.2 is installed, Python is likely to already be installed.

Indexing Data

After confirming the dependencies, you need to add a new location.

To create a new location: 

  1. Go to Manage Voyager > Locations
  2. Click New Location
  3. Select Databases (Advanced)
  4. Select SQL Server in the Connections drop-down list 
  5. Edit the configuration

Configuration Examples

Example 1

This configuration indexes all the tables in a SQL Server database. It includes all the fields and maps only one field for all tables. The use of the asterisk (*) in this configuration means to include all fields, all tables and apply the field mapping in all tables.


{
    "name": "SQLServer_Sample1",
    "type": "python",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "*",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example 2

Index a single table.


{
    "name": "WORLD_CITIES",
    "type": "python",
    "config": {
        "fields": {
            "include": ["*"]
        },
    "tables": [
    {
        "name": "CITIES",
        "action": "INCLUDE"
    },
    {
        "name": "*",
        "map": {
            "NAME ": "name"
            }
    } ],
    "sql": {"connection": {
      "driver": "SQL Server",
      "server": "VOYAGER",
      "database": "VOYAGERTEST",
      "uid": "",
      "pwd": ""
      }
    }
  }
}

Example 3

This example demonstrates the following:

  • Index the STATES and CITIES tables
  • Include all fields
  • Mapping STATE_NAME and CITY_NAME to name
  • Use queries to limit the number of rows that are indexed. See the Usage notes below for further explanation.

{
  "name": "A SQL Server Location",
  "type": "python",
  "config": {
    "fields": {"include": ["*"]},
    "tables": [
      {
        "name": "STATES",
"action": "INCLUDE", "map": {"STATE_NAME": "name"}, "query": "select * from STATES where STATE_NAME = 'California'" }, { "name": "CITIES", "map": {"CITY_NAME": "name"}, "constraint": "select * from CITIES where POP1990 > 100000 and STATE_NAME = 'California'" } ], "sql": {"connection": { "driver": "SQL Server", "server": "VOYAGER ", "database": "VOYAGERTEST", "uid": "", "pwd": "" }} } }

Example 4

SQL Server supports the ability to index related tables as links. This example demonstrates the following:

  • Index a table that with a one to many relationship using a SQL Query 
  • Include all fields
  • Mapping STATE_NAME and CITY_NAME to name
{
  "name": "A SQL Server Location",
  "type": "python",
  "config": {
    "fields": {"include": ["*"]},
    "tables":[
{
"name":"STATES",
"action": "INCLUDE",
"map":{"STATE_NAME":"name"},
"related_tables": ["CITIES"],
"query":"select * from STATES,CITIES where STATES.STATE_NAME=CITIES.STATE_NAME ORDER BY STATES.STATE_NAME"
},
{
"name": "CITIES",
"map": {"CITY_NAME": "name"}
}
], "sql": {"connection": { "driver": "SQL Server", "server": "VOYAGER ", "database": "VOYAGERTEST", "uid": "", "pwd": "" }} } }

As shown in this illustration, the state will now have linked items. For example, the state of California will contain cities such as San Diego, Fresno, etc.


Usage Notes

  • In Example 3, a SQL Query statement is entered limiting how many items are index.
  • The percent symbol (%) is used to perform wild card searches to limit tables, fields and field mapping. However, use an asterisk (*) when you want to include everything.
  • Geographic information is included in the index for tables with geometry. For point features, the X and Y coordinate location is indexed. For other geometries, the extent coordinates (bounding box) is recorded.
Web Design and Web Development by Buildable