Join/Relate in ArcGIS 9.3.1 ArcEditor through a python script - is it possible?

Question

I'm working on a ArcGIS 9.3.1 ArcEditor and I want to write a python script that will do a Join or Relate. Problem is, that join table\join field are restricted to ARCINFO (higher license), and relate isn't doable by code (or so it seems from the ARCGIS help). As upgrading the license isn't an option, Does anybody have a way I can do a join between two shapefiles in ArcGIS or Opensource tools?

Answer 1

If you import the shapefiles into a geodatabase, you can join them using SQL with the Python ODBC library. See this page for help using pyodbc.

Here is some example code:

import pyodc

cnxn = conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<path to MDB or ACCDB>;") #access 2007
cursor = cnxn.cursor()  

query = """
   SELECT  *
   FROM    table1
   JOIN    table2
   ON      table1.field1 = table2.field1
 """

cursor.execute(query)

rows = cursor.execute(query).fetchall() 

Answer 2

You're right that in Arc 9.3, the Join Field and Add Join gp tools require an ArcInfo license. However, in Arc 10, both the Join Field and Add Join tools licensing has been changed and are now available to all license levels (ArcView & ArcEditor).

For open-source, take a look at ogr2ogr. I recommend downloading FWTools (see cheatsheet here), which includes ogr2ogr in the install and makes it very easy to get going quickly. From the ogr2ogr online help in the JOIN section:

OGR SQL supports a limited form of one to one JOIN. This allows records from a secondary table to be looked up based on a shared key between it and the primary table being queried. For instance, a table of city locations might include a nation_id column that can be used as a reference into a secondary nation table to fetch a nation name.