My goal for this script is to extract some information from an Overpass API query and ultimately join it with the table of another feature class I have created.

So far I have completed the following steps relevant to my problem:

  • Extract the desired data from the JSON dictionary
  • Put data into a pandas dataframe
  • convert said dataframe to a sorted numpy array, then back to nparray
  • export the data in my numpy array to an ArcGIS table via NumPyArrayToTable()

With an empty query the script seems to work and gives me an empty table as desired, which leads me to believe, that at least syntactically my code is correct.

import requests
import arcpy
import pandas as pd
import numpy as np

output = r'C:\Users\...\Output\test'

# GET request to overpass API
osmurl = 'http://overpass-api.de/api/interpreter'
osmrequest = {....}

osm = requests.get(osmurl, params=osmrequest)

# Extract data from JSON dictionary nested in elements
data = osm.json()
data = data['elements']

# rearrange osmdata so only relevant attributes in 'tags'can be extracted as dataframe
for i in data
        del i['tags']

# Create a dataframe from sorted JSON dictionary containing desired data
df = pd.DataFrame(data, columns=['data_1', 'data_2'])

# coerce dtype 'O' object values to np array native float64 type
df['data2'] = pd.to_numeric(df['data2'], errors='coerce')

# remove  all rows containing NaNs (no data2 information)
df = df[~np.isnan(osmdataframe).any(axis=1)]

# Create a sorted np array
sorted_osmNParray = np.array(osmdataframe.to_records())

# reconvert to np array so it can be passed to NumpyArrayToTable method
osmNParray = np.array(sorted_osmNParray, np.dtype([('index', '<i8'), ('data_1', '<i8'), ('data_2', '<f8')]))

# Convert NP Array to table
arcpy.da.NumPyArrayToTable(osmNParray, output)

The second I plug in real data though the following error gets thrown:

SystemError: error return without exception set

The cause of this is the method NumPyArrayToTable().

My Numpyarray looks like this before passing it to said method:

array([(20536,   4054816, 6.), (20537,   4054817, 2.),
       (20538,   4239284, 3.), ..., (23731, 581630073, 5.),
       (23732, 587279616, 4.), (23733, 590872784, 7.)],
      dtype=(numpy.record, [(u'index', '<i8'), (u'data_1', '<i8'), (u'data_2', '<f8')]))

I have tried the latter dataframe to numpy array conversion with a variation (NaNs still in my data) and it results in the same error:

df = pd.DataFrame(data, columns=['data_1', 'data_2'])

df['data_2'] = pd.to_numeric(df['data_2'], errors='coerce')

# Convert df to np record array with df.values, then reconvert to unstructured np array
n = np.array(np.rec.fromrecords(df.values))

# make list of column names to replace default nparray ones
list_names = df.dtypes.index.tolist()
n.dtype.names = tuple(list_names)

# Create table
arcpy.da.NumPyArrayToTable(n, output)

Could someone please tell me what exactly I'm doing wrong here? Do I have to maybe reference an already existing table?

  • 1
    That's a notoriously finicky operation. I recommend doing it yourself, simply by creating a table, adding the fields you'll need, and then inserting the rows with a cursor. Plus, that gives you more control. The only tricky part is mapping from numpy/python datatypes to ArcPy datatypes. – Tom May 29 '18 at 22:00

After some tinkering I found the answer to my problem: Simply referencing a global database where the output table would be exported to fixed the system error.

arcpy.da.NumPyArrayToTable(osmNParray, r'C:\Users\ArcGIS 10.3.1\DataBase.gdb\out_table')

It now also works with the other conversion I tried.

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.