nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do you import an SQL query/view into arcgis

deleted-user-Pi5x1UjkS2PY

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

Find more posts tagged with

Accepted answers

DanPatterson_Retired

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

All comments

DanPatterson_Retired

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

deleted-user-Pi5x1UjkS2PY

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

DanPatterson_Retired

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

deleted-user-Pi5x1UjkS2PY

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

deleted-user-Pi5x1UjkS2PY

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

JamesCrandall

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.

JamesCrandall

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

DanPatterson_Retired

y_coords = "Longitude"

x_coords = "Latitude"

Interesting...these are reversed

deleted-user-Pi5x1UjkS2PY

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

JamesCrandall

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.