Accessing Place Dictionary Database From Arcgis

THL Toolbox > Developers' Zone > THL Database Technologies And Models > THL Databases and Database Management > Accessing Place Dictionary Database From Arcgis

Accessing Place Dictionary Database From Arcgis

Contributor(s): Andres Montano

Software Required

Since ArcGIS client software only runs in Windows (as of 2014), the following instructions will assume that you are connecting through a Windows machine.

Putty

Will be used to create a tunnel. Can be downloaded from: external link: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html (select putty.exe under Binaries -> For Windows on Intel x86). putty.exe does not use an installer and is the actual program.

After downloading it, move it to a folder location that makes sense such as My Documents\Applications .

PostgreSQL Binaries

psql libraries do not come with ArcGIS, so have to be downloaded separately. Can be downloaded from: external link: http://www.enterprisedb.com/products-services-training/pgbindownload (select Binaries from installer version 9.1.13 -> Win86-32). Select 32bit version even if you are running Windows under 64bits, since ArcGIS which will use such libraries runs under 32bits (as of 10.2.1) and 64bits won't work!

After having downloaded it double click and navigate to folder \pgsql\bin . From there copy the following files to C:\Program Files (x86)\ArcGIS\Desktop10.2\bin (you may not see the extensions of files, depending on how your windows machine is configured)

  • libiconv-2.dll
  • libintl-8.dll
  • libpq.dll
  • libeay32.dll
  • ssleay32.dll
  • psql.exe

Creating the Tunnel

  • Double-click on putty.exe
  • Under Connection -> Data -> Auto-login username, type "sds-deployer" (without quotes). Leave everything else as is.


  • In Connection -> Data -> SSH -> Tunnels, type under source port "5430" (without quotes), under Destination "localhost:5432" and click the Add button. Leave everything else as is.


  • Under Session -> Host Name type "sdsv5.its.virginia.edu" (without quotes). Then under Saved Sessions type "sdsv5" and click on the Save button. Leave everything else as is.


  • Close the Putty Configuration window by clicking on the red cross on the upper right corner.
  • Right-click on the putty.exe file and select Send to -> Desktop (create shortcut) in the popup menu.
  • Go to the desktop and find the "putty - Shortcut" file. Rename it to something that makes sense like "sdsv5 tunnel".
  • Right-click on the "sdsv5 tunnel" shortcut and select Properties from the popup menu.
  • After "putty.exe" and " -load sdsv5 -pw XXXX" (without the quotes, leaving one space after putty.exe), where XXXX represents sds-deployer's password for sdsv5.its.virginia.edu server. This will give you an easy access to the tunnel creation without the need for having to retype every time the password for the server.

Testing the Tunnel

  • If not on grounds, connect to UVa VPN.
  • Double-click on the "sdsv5 tunnel" shortcut you created on the desktop. It should display a putty console without prompting for any password.


  • Open the windows command-line program (you can find it by searching for "cmd.exe")
  • Type the following:

cd "\Program Files (x86)\ArcGIS\Desktop10.2\bin\"
psql.exe -U postgres --password -h localhost -d shanti_places_production -p 5430

  • When it prompts for a password, enter the postgres password for sdsv5.its.virginia.edu. If you then see a shanti_places_production=# prompt, it worked!


  • Exit the shanti_places_production=# prompt by typing "q" (without quotes) on the command line window. Exit the command line window by typing "exit". Exit the putty console by typing "exit".

Configuring ArcGIS

  • If not on grounds, connect to UVa VPN.
  • Double-click on the "sdsv5 tunnel" shortcut you created on the desktop. It should display a putty console without prompting for any password.
  • Open "ArcCatalog"
  • Double-click on "Add Database Connection"
  • In Database platform, select PostgreSQL
  • In Instance, type "localhost, 5430"
  • In Authentication Type leave "Database authentication"
  • In User name type "postgres"
  • In Password type the postgres password for sdsv5.its.virginia.edu.
  • Click the checkbox "Save username and password"
  • In Database type "shanti_places_production"
  • Click Ok. If you see a "Bad login user" error, don't worry.


  • Rename the Database Connection to something that makes sense such as "sdsv5.sde".
  • Double-click on sdsv5 under Database Connections to connect.

Only the shanti_places_production db is accessible using this method.

Also see Ron Bentley's documentation on accessing PostgreSQL-PostGIS databases.

SQL Query Syntax for Several Different Queries

To return all features (fid and roman popular name) that have Aufschnaiter map data (code auf.id): 

SELECT features.fid, feature_names.name
FROM features, feature_geo_codes, feature_names, cached_feature_names
WHERE cached_feature_names.feature_id = features.id AND
cached_feature_names.feature_name_id = feature_names.id AND features.id = feature_geo_codes.feature_id AND
cached_feature_names.view_id = (SELECT id FROM simple_props WHERE simple_props.type = 'View' AND simple_props.code = 'roman.popular') AND
feature_geo_codes.geo_code_type_id = (SELECT id FROM simple_props WHERE simple_props.type = 'GeoCodeType' AND simple_props.code = 'auf.id');

An example record that this query should return is: features.id = 16473. The fid = 16409 and the record on the public website is: external link: http://places.kmaps.virginia.edu/features/16409 . Names can be returned for the following view codes: roman.popular, roman.scholar, pri.tib.sec.roman, pri.tib.sec.chi, simp.chi, trad.chi, deva.

To query all features with Aufschnaiter map data that have location data: 

SELECT features.fid, feature_names.name, shapes.geometry
FROM features, feature_geo_codes, feature_names, cached_feature_names, shapes
WHERE cached_feature_names.feature_id = features.id AND shapes.fid = features.fid AND
cached_feature_names.feature_name_id = feature_names.id AND features.id = feature_geo_codes.feature_id AND
cached_feature_names.view_id = (SELECT id FROM simple_props WHERE simple_props.type = 'View' AND simple_props.code = 'roman.popular') AND
feature_geo_codes.geo_code_type_id = (SELECT id FROM simple_props WHERE simple_props.type = 'GeoCodeType' AND simple_props.code = 'auf.id');

To query all features that intersect (this includes all features within Lhasa and all features that contain Lhasa) with Lhasa Municipality (fid=317) that have location data: 

SELECT features.fid, feature_names.name, shapes.geometry
FROM features, shapes, feature_names, cached_feature_names, simple_props
WHERE shapes.fid = features.fid AND cached_feature_names.feature_id = features.id AND
cached_feature_names.feature_name_id = feature_names.id AND cached_feature_names.view_id = simple_props.id AND
simple_props.type = 'View' AND simple_props.code = 'roman.popular' AND
shapes.geometry && (SELECT shapes.geometry FROM shapes WHERE shapes.fid = 317)

To query all natural features (feature type 62 and below, see external link: http://subjects.kmaps.virginia.edu/features/62 ) that intersect with Lhasa Municipality (fid=317) that have location data: 

SELECT features.fid, feature_names.name, shapes.geometry
FROM features, shapes, feature_names, cached_feature_names, simple_props, cumulative_category_feature_associations
WHERE shapes.fid = features.fid AND cached_feature_names.feature_id = features.id AND
cached_feature_names.feature_name_id = feature_names.id AND cached_feature_names.view_id = simple_props.id AND
simple_props.type = 'View' AND simple_props.code = 'roman.popular' AND
cumulative_category_feature_associations.feature_id = features.id AND
cumulative_category_feature_associations.category_id = 62 AND
shapes.geometry && (SELECT shapes.geometry FROM shapes WHERE shapes.fid = 317)

Provided for unrestricted use by the external link: Tibetan and Himalayan Library