topic Re: How do you import an SQL query/view into arcgis in Python Questions

How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY — Mon, 27 Oct 2014 14:34:56 GMT

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()

Re: How do you import an SQL query/view into arcgis

JamesCrandall — Mon, 27 Oct 2014 15:13:27 GMT

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.

GettingStarted - pyodbc - Quick Examples To Get You Started - Python ODBC library - Google Project Hosting

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()

Re: How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY — Mon, 27 Oct 2014 17:38:38 GMT

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

Re: How do you import an SQL query/view into arcgis

DanPatterson_Retired — Mon, 27 Oct 2014 17:45:15 GMT

y_coords = "Longitude"

x_coords = "Latitude"

Interesting...these are reversed

Re: How do you import an SQL query/view into arcgis

JamesCrandall — Mon, 27 Oct 2014 18:29:09 GMT

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

Re: How do you import an SQL query/view into arcgis

JamesCrandall — Mon, 27 Oct 2014 18:33:32 GMT

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.

Re: How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY — Mon, 27 Oct 2014 18:40:10 GMT

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')]))

Re: How do you import an SQL query/view into arcgis

JamesCrandall — Mon, 27 Oct 2014 18:44:48 GMT

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?

ArcGIS Help 10.1

Re: How do you import an SQL query/view into arcgis

DanPatterson_Retired — Mon, 27 Oct 2014 18:49:36 GMT

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

Re: How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY — Wed, 29 Oct 2014 16:50:28 GMT

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' ])

Re: How do you import an SQL query/view into arcgis

DanPatterson_Retired — Wed, 29 Oct 2014 18:39:22 GMT

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...

Re: How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY — Wed, 29 Oct 2014 18:53:38 GMT

Changed to this and it works like a charm

datarray.append(tuple(row))

One last question. I need to be able to add the spatial reference of webmercator to it. I know you do it somehow like this.

Just not sure what to put in for webmercator?

SR = arcpy.Describe("C:/data/texas.gdb/fd").spatialReference

Re: How do you import an SQL query/view into arcgis

DanPatterson_Retired — Wed, 29 Oct 2014 19:08:44 GMT

stealing from the help files

SR = arcpy.Describe("C:/data/texas.gdb/fd").spatialReference

# Export the numpy array to a feature class using the XY field to

# represent the output point feature

arcpy.da.NumPyArrayToFeatureClass(array, outFC, ['XY'], SR)

As long as you have a valid spatial reference, then you add it as SR when creating the output