Have a view in SQL that I want to bring into ArcGIS using python. It is part of a process of a longer python script. how can I do this using python.
In 9.3 I used my ODBC connection "AR" to do this and then brought in my file "GISWEB.VW_MCYWEB" as an XY Event and then exported it as a shapefile with a coordinate system of WebMercator so that I could use it in my web maps.
I've tried several things but nothing is working for me at the moment. I'm sure this is simple but I'm not see how to do this.
I thought maybe this would work.
import arcpy
import win32api, win32con
import sys
import pyodbc
from arcpy import env
def defAddSQLTable_CreateShapefile():
### Works Makes a list of all tables in SQL Database for that DSN Connection ###
cnxn = pyodbc.connect("DSN=AR")
arcpy.env.workspace= 'C:/temp'
in_Table = "AR.GISWEB.VW_MCYWEB"
y_coords = "Longitude"
x_coords = "Latitude"
out_layer = "Stores"
arcpy.env.overwriteOutput = 'true'
arcpy.MakeXYEventLayer_management(in_Table, x_coords, y_coords, out_layer)
defAddSQLTable_CreateShapefile()
Solved! Go to Solution.
Numpy lesson part 2....
If you are producing an array from a list of lists, you can run into some problems...
>>> import numpy as np
>>> data_list = [[0,'Walmart',-75.0,45.0],[1,'Costco',-75.5,45.1],[2,'Best Buy',-75.1,45.2],[3,'Shoppers',-75.2,45.3]]
>>> dtype = np.dtype([('ID','<i4'),('Store','|S25'),('Long','<f8'),('Lat','<f8')])
>>> np_array = np.array(data_list,dtype)
Traceback (most recent call last):
File "<interactive input>", line 1, in <module>
TypeError: expected a readable buffer object
>>>
you can correct for this by using a generator to convert the list of lists to a list of tuples...and life is good
>>> data = [ tuple(row) for row in data_list]
>>> data
[(0, 'Walmart', -75.0, 45.0), (1, 'Costco', -75.5, 45.1), (2, 'Best Buy', -75.1, 45.2), (3, 'Shoppers', -75.2, 45.3)]
>>> np_array = np.array(data,dtype)
>>> np_array
array([(0, 'Walmart', -75.0, 45.0), (1, 'Costco', -75.5, 45.1),
(2, 'Best Buy', -75.1, 45.2), (3, 'Shoppers', -75.2, 45.3)],
dtype=[('ID', '<i4'), ('Store', 'S25'), ('Long', '<f8'), ('Lat', '<f8')])
>>>
The stores in no way sponsor this thread...
AR.GISWEB.VW_MCYWEB is not a table (as in how you perceive your "in_Table" variable). You have to issue SQL against this to fill a cursor object and then use the cursor to build your table.
We do something similar, but we access Oracle using cx_Oracle library. basically once you get your cursor you can just append rows to a new list and/or numpy array which is easy to convert to a feature class with arcpy.da.NumPyArrayToFeatureClass() or arcpy.da.NumPyArrayToTable()
Thanks but I need further guidance please. I can use what is below to print out a list of Storenames from my table. So it is connecting which is good. One step closer.
However, I want all of my field names and values to go into this array to make the feature class which will hopefully make my shapefile. Should be a simple way to loop through the table adding all of the column names and field values into the array. Any help would be greatly appreciated.
def defAddSQLTable_CreateShapefile():
cnxn = pyodbc.connect("DSN=AR")
cursor = cnxn.cursor()
cursor.execute("select * from GISWEB.VW_MCYWEB")
fc = "c://temp/myfc
for row in cursor:
##grab field value print row.column_name
# print 'name:', row[1] # access by column index
# print 'name:', row.STORENAME # or access by name
Something like this (untested and just typed out from memory and other snippets):
datArray = []
for row in cursor:
datArray.append(row)
cursor.close()
del cursor
if len(datArray)>0:
npyarr = np.array(datArray, np.dtype([('storename', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))
outFC = ws_mem + "\\MyFeatureClass"
if arcpy.Exists(outFC):
arcpy.Delete_management(outFC)
print "deleted " + str(outFC)
arcpy.da.NumPyArrayToFeatureClass(npyarr, outFC, ("xCoord", "yCoord"))
print "created..." + outFC
Also, please don't use "SELECT * FROM Table/View..." --- not only is it bad SQL, it's going to make your life really difficult when setting up your arrays and/or conversion into GIS-objects. Be sure to specify the fields you want... ALWAYS.
Thanks but I want all of the fields and we will add to these fields as we grow this product.
I don't understand this line below. what is the |S25' for and what is the <f8 for?
npyarr = np.array(datArray, np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))
but I want all of the fields and we will add to these fields as we grow this product
Then add them, don't be lazy. "SELECT *" is fine for quick look at things, but not a great idea for production code.
what is the |S25' for and what is the <f8 for?
He is producing a numpy array using the arcpy.da.NumPyArrayToFeatureClass.... line
This line npyarr = np.array(datArray, np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')]))
sets the data type so that STORENAME is a string, 25 characters wide and xCoord, yCoord are floating point numbers (details in numpy dtype documentation)
in essence, you will end up with an array (npyarr) that you can query by field name and or coordinates.
for example
>>> data = [ ("Walmart", -75.0, 45.0),("Costco", -75.5,45.1)]
>>> import numpy as np
>>> dtype =np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
>>> npyarr = np.array(data,dtype)
>>> npyarr
array([('Walmart', -75.0, 45.0), ('Costco', -75.5, 45.1)],
dtype=[('STORENAME', 'S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
>>> npyarr['STORENAME']
array(['Walmart', 'Costco'],
dtype='|S25')
>>> npyarr[0]
('Walmart', -75.0, 45.0)
>>> npyarr[1]
('Costco', -75.5, 45.1)
>>>
such arrays can be extremely powerful
y_coords = "Longitude"
x_coords = "Latitude"
Interesting...these are reversed
augh. I'm getting an error I'm not sure what to do about. I've tried a lot of different samples so my code looks a little different. Everything works fine when I do a print for the array but I continue to have the problem below.
I am getting this error below
TypeError: expected a readable buffer object
On this line:
npyarr = numpy.array(datArray, numpy.dtype([('storename', '|S25'), ("xCoord", '<f8'), ('yCoord', '<f8')]))
def defAddSQLTable_CreateShapefile():
cnxn = pyodbc.connect("DSN=AR")
cursor = cnxn.cursor()
cursor.execute("Select STORENAME, LATITUDE, LONGITUDE FROM GISWEB.VW_MCYWEB WHERE DISTRICTID = 944")
datArray = []
for row in cursor:
datArray.append(row)
dtype = np.dtype([('STORENAME', '|S25'), ('xCoord', '<f8'), ('yCoord', '<f8')])
npyarr = np.array(datArray, dtype)
out_fc = "C:/inetpub/mystores3"
arcpy.da.NumPyArrayToFeatureClass(npyarr, out_fc, ['xCoord','yCoord' ])