Convert JSON data to geometry

Question

I have been given some spatial data in a table in a format that I have never come across before and would like to convert it to geometry.

How can I convert to geometry? I could use 'text to columns' function in Excel to split the coordinates up then merge them back together to WKT format, but I was hoping there might be a faster way to convert using QGIS or ArcGIS Pro. I don't know how to write Python scripts, so looking for a plugin or tool or similar.

Here are two sample rows from the 'Location' field of the table:

{"poly":[],"lat":-43.90554038530789,"lng":171.7366969600843,"place_id":"ChIJO_-_46y7LW0RIiYCTH3DgpE","region":"Canterbury","district":"Ashburton District"}

{"id":"","place_id":"ChIJVebsRr8YbW0Ru9B5-9kqAaA","lat":-37.783528339369091,"lng":175.29208721117561,"poly":[{"lat":-37.784817198087467,"lng":175.29316009478157},{"lat":-37.784240611203458,"lng":175.29101432756966},{"lat":-37.783392681143624,"lng":175.29178680376594},{"lat":-37.782239480650716,"lng":175.2919584651429},{"lat":-37.783731854334924,"lng":175.29303134874885}],"region":"Waikato","district":"Hamilton City"}

Answer 1

This is a great opportunity to try out some python. This script will convert your json columns into 2 new columns - 1 for the centroid wkt (the lat/lng part of the json) and 1 column for the poly wkt. It assumes your input csv has 2 columns [id,geom] - edit the csv or script to match your data. Also, if you want to extract other parts of the json, you can use the appropriate key (i.e. j["place_id"]):

#import modules
import csv
import json

#input and output file paths
filename = r"E:\test.csv"
outfile = r"E:\test_mod.csv"


#open the input and output files
with open(filename, 'r', newline='') as csvFile, open(outfile, 'w', newline='') as newfile:
    #initialize a reader for the input csv
    reader = csv.reader(csvFile, delimiter=',', quotechar='"')
    next(reader, None) #skip the header row
    #initialize a writer for the output csv
    writer = csv.writer(newfile, delimiter=',', quotechar='"')
    #create and write a new header for the output csv
    newheader = ['id','centroid','polygon']
    writer.writerow(newheader)
    #loop through the rows - each row will be a list of cells
    for row in reader:
        #row[0] is id column, row[1] is geom column
        #read the json data into a python dictionary
        j = json.loads(row[1])
        #Check that lat and lng exist in the json
        if j.get('lng') and j.get('lat'):
            #create the centroid wkt
            centroid = 'POINT (' + str(j['lng']) + ' ' + str(j['lat']) + ')'
        else:
            centroid = ''
        #check that poly exists in the json
        g = j.get("poly")
        if g and len(g) > 0:
            #format coords for wkt
            coords = [str(x['lng']) + ' ' + str(x['lat']) for x in g]
            #create the polygon wkt
            polygon = 'POLYGON ((' + (', ').join(coords) + '))'
        else:
            polygon = ''
        print(centroid)
        print(polygon)
        #create the new output row and write it to output csv
        newrow = [row[0],centroid,polygon]
        writer.writerow(newrow)

sample output:

id	centroid	polygon
1	POINT (171.7366969600843 -43.90554038530789)
2	POINT (175.2920872111756 -37.78352833936909)	POLYGON ((175.29316009478157 -37.78481719808747, 175.29101432756966 -37.78424061120346, 175.29178680376594 -37.783392681143624, 175.2919584651429 -37.782239480650716, 175.29303134874885 -37.783731854334924))