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

jaykapalczynski · ‎08-03-2022

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.

Anonymous User · ‎08-04-2022

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)

View solution in original post

Anonymous User · ‎08-03-2022

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

json-to-features

jaykapalczynski · ‎08-03-2022

@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 · ‎08-03-2022

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 · ‎08-03-2022

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

jaykapalczynski · ‎08-03-2022

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

Anonymous User · ‎08-03-2022

When reading the docs, items in {} mean they are optional. They also have (Optional) listed under them in the parameter table. If you don't have a value for it, you can omit it.

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

Since the x,y keys have different names in the txt you provided, you'll have to do some manipulation before this method will work.

jaykapalczynski · ‎08-03-2022

If I do this I get this error

import arcpy
import os
arcpy.env.workspace = "C:/Users/t/Desktop/JSONSamples/"
outfeatureLocation = "C:/Users/t/Desktop/JSONSamples/OutputGeodatabase"
arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation)

Traceback (most recent call last):
File "C:/Users/t/Desktop/JSONSamples/AddPoints.py", line 7, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation)
File "C:\Program Files (x86)\ArcGIS\Desktop10.8\ArcPy\arcpy\conversion.py", line 602, in JSONToFeatures
raise e
ExecuteError: ERROR 001558: Error parsing json file 'JSON.json'.
Failed to execute (JSONToFeatures).

If I do this:

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

Traceback (most recent call last):
File "C:/Users/tjv36463/Desktop/JSONSamples/AddPoints.py", line 7, in <module>
arcpy.JSONToFeatures_conversion("JSON.json", outfeatureLocation, 'POINT')
TypeError: JSONToFeatures() takes at most 2 arguments (3 given)

Anonymous User · ‎08-03-2022

Read the entire post- At the bottom it says "Since the x,y keys have different names in the txt you provided, you'll have to do some manipulation before this method will work." I provided an example to get you started on how to do it in an earlier post.

...

To answer why these two didnt work as expected:

The first attempt, you have to load the file in as JSON using json.loads() or it doesn't know its JSON. It's saying it can't read it.

The second attempt, is because that parameter is only used for GeoJSON files, which your json is not GeoJSON. The method expects only 2 parameters for JSON files, as noted in the docs and in previous posts.

'The geometry type (the third parameter) 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.'

Anonymous User · ‎08-03-2022

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