topic Re: Python and Geocoding in Python Questions

Python and Geocoding

JayKappy — Mon, 08 Oct 2012 14:25:22 GMT

I have a stand alone table in SQL server. I want to create a Python script to Geocode that table.

Can I set up a Geocoder to process that table and place XY coordinates into an XY Field in the table?
It has to process address and intersections.
I would have three fields House Number, Street, Intersecting Street

Any thoughts, examples on how to get started?

Do I create an mxd with the table in it and save it.
Then write soem python script to point to that mxd and process the Geocode?

Thanks

Re: Python and Geocoding

JayKappy — Mon, 08 Oct 2012 14:49:37 GMT

So Maybe something like this....

Grab the Database, then create a table view and then soemhow GEOcode
Although in my Case I would have to point to a SQL table, how different is the syntax for a SQL table versus an Access Database?

import arcgisscripting
gp = arcgisscripting.create()

gp.workspace = "d:/workspace/NFLD.mdb"
gp.maketableview "nf_roads", "TCH", "[ROAD_CLASS] = 'TCH'", "LEVEL ROAD_CODE VISIBLE, NAME NAME HIDDEN"

GEOCODE CODE HERE????

Re: Python and Geocoding

JayKappy — Mon, 08 Oct 2012 16:36:28 GMT

I am seeing some examples of Geocoding to Shapefile or another FC in the GDB...

But all I want to do is point to the Table View I create, then Geocode the XY into and XY field in the same table.

Can this be done?

    # Create the GeoProcessor
         import arcgisscripting
         gp = arcgisscripting.create()
    # Define Workspace
         gp.workspace = "SQL Server Table Location"
    # create the Table view from teh SQL Server table with a WHERE clause
         gp.maketableview "nf_roads", "TCH", "[ROAD_CLASS] = 'TCH'", "LEVEL ROAD_CODE VISIBLE, NAME NAME HIDDEN"

    Geocode and populate the XY fields with the XY location from the Geocode?

Can my input table of addresses be used as the output table where the XY coordinates are placed?

Re: Python and Geocoding

JayKappy — Tue, 09 Oct 2012 12:09:22 GMT

Anyone ever do this before? Is it possible to write the XY from Geocoding to the same table?

Re: Python and Geocoding

JamesCrandall — Wed, 10 Oct 2012 11:08:49 GMT

Although in my Case I would have to point to a SQL table, how different is the syntax for a SQL table versus an Access Database

jay,

Use pyodbc or pymssql libaries to make the connections to your SQL Server

http://sourceforge.net/projects/pyodbc/
http://pymssql.sourceforge.net/examples_pymssql.php

I'd also say you could probably just populate an in_memory table to geocode from.

Re: Python and Geocoding

JayKappy — Wed, 10 Oct 2012 11:48:40 GMT

THanks for your reply
THe SQL connection see very famililar to others so I dont think that is going to be much of a problem.

ON the other hand creating an in memory table and then Geocoding to that and then using that to update the original SQL table....not getting my brain around that one....can you explain more...are there examples for this in moemory table creation..and then how wold I apply that table to the SQL Table?

Thanks

EDIT:
1. Are you saying to create a table like in your second link with the values from the SQL Server table.
2. Geocode against this table creating an output Feature Class
3. Then copy over the XY from the new Feature Class to the Original Table in SQL Server
4. Then Delete the Created table and Feature Class

Re: Python and Geocoding

JamesCrandall — Fri, 10 Dec 2021 22:40:04 GMT

THanks for your reply
THe SQL connection see very famililar to others so I dont think that is going to be much of a problem.

ON the other hand creating an in memory table and then Geocoding to that and then using that to update the original SQL table....not getting my brain around that one....can you explain more...are there examples for this in moemory table creation..and then how wold I apply that table to the SQL Table?

Thanks

EDIT:
1. Are you saying to create a table like in your second link with the values from the SQL Server table.
2. Geocode against this table creating an output Feature Class
3. Then copy over the XY from the new Feature Class to the Original Table in SQL Server
4. Then Delete the Created table and Feature Class

I was mainly pointing out ways to acquire the SQL server data, but yes you could populate a temporary table or an in_memory table with those rows and Geocode from this. You'll have to deal with the field mapping (depending upon the database, it may not map directly to the temp table you need to create) --- here's how I deal with a cursor from an Oracle table (sorry for formatting issues. it doesn't paste well into the editor here and python needs good formatting!):

gp.createtable_management("IN_MEMORY", "TempDT", "", "")

           
         cxRows = cursor.fetchall()
 rowcount = cursor.rowcount
 for row in range(1, rowcount - 1):
 
  tables = gp.ListTables()
  for tbl in tables:   
     if tbl=="TempDT":
                               ### add the fields
        for i in range(0, len(cursor.description)):
   val1 = str(cursor.description[0])
   val2 = str(cursor.description[1])
   val3 = str(cursor.description[2])

   if val2=="<type 'cx_Oracle.STRING'>":
      fldType = "Text"
      val3 = cursor.description[2]
      gp.AddField(tbl, str(cursor.description[0]), fldType, val3)


   if val2=="<type 'cx_Oracle.NATIVE_FLOAT'>":
      fldType = "Float"
      gp.AddField(tbl, str(cursor.description[0]), fldType)


   if val2=="<type 'cx_Oracle.DATETIME'>":
      fldType = "Date"
      gp.AddField(tbl, str(cursor.description[0]), fldType)

                         ### now populate the table
     insRows = gp.InsertCursor(tblNew)
     for cxRow in cxRows:
        insRow = insRows.newRow()

        for i in range(0, len(cursor.description)):
     insRow.setvalue(str(cursor.description[0]), cxRow)
        insRows.insertRow(insRow)



  cursor.close() 
  db.close()

***The above are snippets pulled from a larger def() so it may not work directly and require you to modify for your needs. It is intended to be a general outline/view as to how I go from Oracle table to in_memory table.

Good luck!

Re: Python and Geocoding

JayKappy — Wed, 10 Oct 2012 13:48:06 GMT

Thanks for your help....

So thats part 1 I guess...

1. Get the SQL Table into memory
2. Geocode that table in Memory to new Feature Class?
3. Join Feature Class with SQL Table and update XY fields.

I think this will work....Anyone else out there have any ideas. Maybe something that I am not thinking about.
Thanks

Re: Python and Geocoding

JayKappy — Wed, 10 Oct 2012 13:53:33 GMT

Question...why would I have to create a table in memory...

If I have a table in SQL Server.

Cant I simply Geocode that table and create a Feature Class.

This new feature class will then have the XY location.

Then simply Join the new FC back to the original SQL table (by unique value) and popuate the XY into the original table

Re: Python and Geocoding

JamesCrandall — Wed, 10 Oct 2012 14:22:52 GMT

Question...why would I have to create a table in memory...

If I have a table in SQL Server.

Ok. As long as it is in a Geodatabase. From, http://webhelp.esri.com/arcgiSDEsktop/9.3/index.cfm?TopicName=Geocode_Addresses_%28Geocoding%29

"The input address table can be any format supported by ArcGIS including INFO, dBASE, and geodatabase tables"

So for my implementation, that Oracle data/table is not registered with ArcSDE or in any supported format (it's just a non-spatial table that resides outside of any GIS infrastructure). This means that I have to transform it into that supported format and I assumed you needed to do the same because in your OP you mention:

"...I have a stand alone table in SQL server."

Re: Python and Geocoding

JayKappy — Wed, 10 Oct 2012 18:58:24 GMT

This is what I was trying to explain.
SO it seems that I would have to create a temp table

The Table to Geocode is a Standalone SQL Server table (not SDE)
This would create a Feature Class as output once Geocoded.
Then take the FC and join back to the Standalone SQL Server table to append the XY corrdinates.

Thanks for your thoughts and input.

Re: Python and Geocoding

BradShore1 — Wed, 10 Oct 2012 20:28:37 GMT

You could make an OLEDB connection to the SQL Server and geocode off of that. Then use the add XY Coordinates tool to get the XY Coordinate you want.