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; } } }

Read and write python file using X and Y fields in the data.

jaykapalczynski

I have a json string that is being read from a word press API URL.... it looks like the attached.

I need to get this to a feature class. In addition to all the fields, it only has an X and Y field which I have to convert to point geometry. Any thoughts on how I can achieve this via Python?

So I need to read the URL for the json string then create a FC from it.

Python to Table and then Table to FC? Any help would be appreciated.

JSON.txt

Trouver plus de publications taguées avec

Accepted answers

Anonymous User

I found out this morning JSONToFeatures wants a file so you'll need to write it to disk and load it from there. I did a little bit of data cleanup using an update cursor, which I assume would be your next step once you got the data loaded. You can alter fields from TEXT to other datatypes as well if you want.

Edit to note: You'll need to import re for some regex to fix the values. I think your gdb path is ok and the order is ok, I truncated my esri_json a bit to avoid redundancy.

converted_list = []
for json_dict in distr:
    feat_dict = {}
    feat_dict["geometry"] = {"x": json_dict["site_longitude"], "y": json_dict["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    json_dict["site_facilities"] = str(json_dict["site_facilities"])
    feat_dict["attributes"] = json_dict
    converted_list.append(feat_dict)

new_dict = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in distr[0].items()],
            'features': converted_list}

print(new_dict)

esri_json = r'C:\Users\...\Documents\ArcGIS\cnvrted.json'

with open(esri_json, 'w') as f:
    json.dump(new_dict, f)

output_fc = r'C:\Users\...\Documents\ArcGIS\Projects\Clerk_LocalData\Clerk_LocalData.gdb\test'

arcpy.JSONToFeatures_conversion(esri_json, output_fc)

#####  Clean up the fields.
flds = [f.name for f in arcpy.ListFields(output_fc)]

p_tag = re.compile(r'.*?>(.*)<')

def extract_p_tag(invalue):
    result = p_tag.search(invalue)
    newVal = result.group(1)
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return newVal

def fix_website(invalue):
    newVal = re.sub(r'/', r'\\', invalue)
    newVal = newVal.replace('\\\\', '\\')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return str(newVal)

def fix_facilities(invalue):
    newVal = invalue.replace('[', '').replace(']', '').replace(r"'", r'')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return newVal

def fix_na(invalue):
    newVal = invalue.replace('\/', '\\')
    arcpy.AddMessage(f'Fixing: {invalue} to {newVal}')
    return str(newVal)

with arcpy.da.UpdateCursor(output_fc, flds) as uCur:
    for row in uCur:
        row[flds.index('site_description')] = extract_p_tag(row[flds.index('site_description')])
        row[flds.index('site_website')] = fix_website(row[flds.index('site_website')])
        row[flds.index('site_ebird_url')] = fix_website(row[flds.index('site_ebird_url')])
        row[flds.index('site_facilities')] = fix_facilities(row[flds.index('site_facilities')])
        row[flds.index('seabirds')] = fix_na(row[flds.index('seabirds')])

        uCur.updateRow(row)

All comments

jaykapalczynski

OK you can see my result in the other post... the resulting JSON is out of order as to what is specified in the python script....I am using this code to try and create the Feature Class but getting an error...

I get this error:

Traceback (most recent call last):

File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 47, in <module>

arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures

raise e

ExecuteError: Failed to execute. Parameters are not valid.

ERROR 000814: Invalid file type

Failed to execute (JSONToFeatures).

import arcpy
import requests
import json
import uuid
import os

arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"

dataInput = r"C:/Users/t/Desktop/JSONSamples/JSON4.json"

f = open(dataInput)
data = json.load(f)

data1 = {"BirdTrailLocations":  data }

converted_list = []
for i in data1['BirdTrailLocations']:
    
    feat_dict = {} 
    feat_dict["geometry"] = {"x": i["site_longitude"], "y": i["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    i["site_facilities"] = str(i["site_facilities"])

    feat_dict["attributes"] = i
    converted_list.append(feat_dict)

# Closing file
f.close()

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
            'features': converted_list}

json_object = json.dumps(esri_json)

print json_object
print ""

arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

jaykapalczynski

OK so great making progress here...

So I added/modified the code and its reading ok but not in the order you specified

I was wondering if that was OK????

You can see it writes Fields - geometry type - features - spatial reference

But in the code I specified

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in data[0].items()],
            'features': converted_list}

{'fields': [{'alias': 'site_name', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_name'}, {'alias': 'insects', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'insects'}, {'alias': 'site_number', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_number'}, {'alias': 'site_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_id'}, {'alias': 'seabirds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'seabirds'}, {'alias': 'black_bear', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'black_bear'}, {'alias': 'site_ebird_url', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_ebird_url'}, {'alias': 'shorebirds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'shorebirds'}, {'alias': 'loop_name', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'loop_name'}, {'alias': 'last_updated', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'last_updated'}, {'alias': 'lizards', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'lizards'}, {'alias': 'hawks', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'hawks'}, {'alias': 'frogs', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'frogs'}, {'alias': 'site_ebird_site_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_ebird_site_id'}, {'alias': 'site_access', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_access'}, {'alias': 'site_loop', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_loop'}, {'alias': 'site_season', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_season'}, {'alias': 'carto_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'carto_id'}, {'alias': 'cms_id', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'cms_id'}, {'alias': 'general_birding', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'general_birding'}, {'alias': 'phase', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'phase'}, {'alias': 'site_contact', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_contact'}, {'alias': 'site_latitude', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_latitude'}, {'alias': 'marine_mammals', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'marine_mammals'}, {'alias': 'bald_eagle', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'bald_eagle'}, {'alias': 'site_longitude', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_longitude'}, {'alias': 'site_website', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_website'}, {'alias': 'site_facilities', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_facilities'}, {'alias': 'site_description', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'site_description'}, {'alias': 'waterfowl', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'waterfowl'}, {'alias': 'grassland_birds', 'length': 255, 'type': 'esriFieldTypeString', 'name': 'grassland_birds'}], 'geometryType': 'esriGeometryPoint', 'features': [{'geometry': {'y': 37.44901, 'x': -77.581618}, 'attributes': {u'site_name': u'Rockwood Park', u'insects': u'Unknown', u'site_number': u'1', u'site_id': u'CAP01', u'seabirds': u'Na', u'black_bear': u'Unknown', u'site_ebird_url': u'https:ebird.orgbarchart?r=L617671&yr=all&m=', u'shorebirds': u'Na', u'loop_name': u'Appomattox Loop', u'last_updated': u'May 20, 2022', u'lizards': u'Unknown', u'hawks': u'Na', u'frogs': u'Unknown', u'site_ebird_site_id': u'L617671', u'site_access': u'Free, Daily', u'site_loop': u'CAP', u'site_season': u'All', u'carto_id': u'01', u'cms_id': u'4799', u'general_birding': u'Available', u'phase': u'Coastal Phase', u'site_contact': u'Chesterfield', u'site_latitude': 37.44901, u'marine_mammals': u'Na', u'bald_eagle': u'Unknown', u'site_longitude': -77.581618, u'site_website': u'www.chesterfield.gov3824Rockwood-Park', u'site_facilities': "[u'environmental_study_area', u'handicap_accessible', u'hiking_trails', u'information', u'interpretive_nature_program', u'parking', u'phone', u'picnic', u'restrooms']", u'site_description': u'This 160-acre', u'waterfowl': u'Na', u'grassland_birds': u'Unknown'}}], 'spatialReference': {'wkid': 4326}}

Anonymous User

no worries... it looks like esri is pretty serious wanting an ESRI-JSON formatted JSON...

You can create this format similar to how we popped the out the site_long and site_lat. Using your txt as an example but we build the geometry, fields, and attributes list:

converted_list = []
for json_dict in distr:
    feat_dict = {} 
    feat_dict["geometry"] = {"x": json_dict["site_longitude"], "y": json_dict["site_latitude"]}
    # the list created a value type is incompatible with the field type. [site_facilities] so we nee dto convert it to a string
    json_dict["site_facilities"] = str(json_dict["site_facilities"])

    feat_dict["attributes"] = json_dict
    converted_list.append(feat_dict)

then create the dict that you will convert to esri-JSON, setting the fields to an item from the json and converted_list to the features:

esri_json = {"geometryType" : "esriGeometryPoint",
             "spatialReference" : {"wkid" : 4326},
            'fields': [{"name": str(k), "type": "esriFieldTypeString", "alias": str(k), "length": 255} for k, v in distr[0].items()],
            'features': converted_list}

which should give you something like the format they want:

{'geometryType': 'esriGeometryPoint', 'spatialReference': {'wkid': 4326}, 

'fields': [{'name': 'site_id', 'type': 'esriFieldTypeString', 'alias': 'site_id', 'length': 255}, ...], 

'features': [{'geometry': {'x': '-77.581618', 'y': '37.44901'}, 'attributes': {'site_id': 'CAP01', 'site_name': 'Rockwood Park', 'site_loop': 'CAP', 'site_number': '1', ... }]}

write it to a txt file on disk, or assign to a variable:

with open(r'C:\Users\...\Documents\ArcGIS\cnvrted.json', 'w') as f:
   json.dump(esri_json, f)

json_object = json.dumps(esri_json)

Then pass it to the method as before.

I have another version that iterates over the json features using a an insert cursor that I can share but this method is more dynamic.

This gave me two points, and a lot of stuff in the attribute table.

jaykapalczynski

OK this is what I have and getting another error.....sorry for my ignorance...tryin to learn here.

Its rewriting to the X and Y field names so thats good...

But getting error

Traceback (most recent call last):
File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 44, in <module>
arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures
raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000814: Invalid file type
Failed to execute (JSONToFeatures).

Do I have to convert the JSON Object to something before I use the JSONToFeatures_conversion
Maybe its my output location and gdb. I have a file gdb called outgdb.gdb at the same location as where the python file is located.

import arcpy
import requests
import json
import uuid
import os

arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"
dataInput = r"C:/Users/t/Desktop/JSONSamples/JSON3.json"

f = open(dataInput)
data = json.load(f)
converted_list = []
for i in data['BirdTrailLocations']:

    i['x'] = i.pop('site_longitude')
    i['y'] = i.pop('site_latitude')
    converted_list.append(i)

    varsearchSiteID =  i["site_id"]
    varsearchSiteName =  i["site_name"]
    print ("site_id: " + varsearchSiteID)
    print ("site_name: " + varsearchSiteName)

    print ""

    print(i)

    print ""
  
# Closing file
f.close()

json_object = json.dumps(converted_list)

print json_object
print ""
print converted_list

outfeatureLocation = "C:/Users/t/Desktop/JSONSamples/OutputGeodatabase/"
arcpy.JSONToFeatures_conversion(json_object, os.path.join("outgdb.gdb", "myfeatures"))

jaykapalczynski

Tried this

import arcpy
import os
arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"
arcpy.JSONToFeatures_conversion("JSON.json", os.path.join("outgdb.gdb", "myfeatures"), {POINT})

Error:

Traceback (most recent call last):
File "C:/Users/tjv36463/Desktop/JSONSamples/AddPoints.py", line 4, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", os.path.join("outgdb.gdb", "myfeatures"), {POINT})
NameError: name 'POINT' is not defined

jaykapalczynski

What is "myfeatures" referring to in the example you gave me? I thought that it took 3 parameters and the third would be POINT, LINE, POLYGON etc.

arcpy.conversion.JSONToFeatures(in_json_file, out_features, {geometry_type})

EXAMPLE

arcpy.JSONToFeatures_conversion("myjsonfeatures.json", os.path.join("outgdb.gdb", "myfeatures"))

Anonymous User

@jaykapalczynski wrote:
In addition to all the fields, it only has an X and Y field which I have to convert to point geometry.

ok, so the actual keys are 'site_longitude' and 'site_latitude' as the x/y and I assume that is what you are referencing. Thought you meant they were X and Y and didn't look that deeply.

After reading the initial JSON, iterate over the them to change the keys creating a new dictionary, and converted list. Something like this:

converted_list = []
for json_dict in distr:
    json_dict['x'] = json_dict.pop('site_longitude')
    json_dict['y'] = json_dict.pop('site_latitude')
    converted_list.append(json_dict)

convert back to JSON:

json_object = json.dumps(converted_list)

then into the method JSON to features...

or use an insert cursor to insert the feature during the first iteration, mapping the site_long and site_lat to a point geometry and inserting it into the fc.

Anonymous User

geometry_type is an optional parameter. From the docs:

'The geometry type to convert from GeoJSON to features. This option is only used when the input is a GeoJSON file. If the GeoJSON file does not contain any of the selected geometry type, the output feature class will be empty.'

Did you give the method a try with your json and see if the method is able to parse the x/y keys as long/lats?

jaykapalczynski

@Anonymous User did you look at my attachment... I am not sure how to specify the X Y field to get this to work... there is no geometry in the json file.

Anonymous User

There is a JSON to features arcpy method that should get you started.

json-to-features

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

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