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"}
  • 2
    that is json format
    – jbalk
    Commented Feb 6, 2023 at 20:42
  • 1
    There are an infinite number of UTF-8 formats available. The format is far less important than how it can be processed. You should Edit the Question to ask about that.
    – Vince
    Commented Feb 6, 2023 at 20:44
  • Place ID relates to developers.google.com/maps/documentation/javascript/place-id most likely.
    – Mapperz
    Commented Feb 6, 2023 at 20:47
  • It's quite wasteful to parse JSON with Excel when you have Python (which can trivially read JSON) available. Unfortunately, you've got a number of questions and platforms in this Question, which violates the One question per Question policy.
    – Vince
    Commented Feb 6, 2023 at 21:01

1 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']
    #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']) + ')'
            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) + '))'
            polygon = ''
        #create the new output row and write it to output csv
        newrow = [row[0],centroid,polygon]

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))
  • Looks ideal, but I am getting this error when I run the script: KeyError Traceback (most recent call last) In [7]: Line 28: g = j["poly"] KeyError: 'poly' . It sounds like I need to add "poly' to a dictionary but I have no idea how to do that in the context of this script. Any help much appreciated.
    – ffinnm
    Commented Feb 7, 2023 at 3:56
  • Can you see if any of the json is missing poly? Actually, I will edit the script to handle that. I think that is probably the issue.
    – jbalk
    Commented Feb 7, 2023 at 17:45
  • 1
    Also, for some background on the script - the json module is doing the work of converting the json data into a python dictionary. It's probably working correctly, but the json data is missing keys for some rows. If you try to get a key from the dictionary that doesn't exist, you'll get a key error.
    – jbalk
    Commented Feb 7, 2023 at 17:48
  • 1
    I edited the script to check that the keys exist in the json before trying to use them.
    – jbalk
    Commented Feb 7, 2023 at 19:09
  • 1
    I just had to add a bit to the 'open' function (something to do with encoding): "with open(filename, 'r', newline='',encoding='utf-8')" and it worked like a charm!
    – ffinnm
    Commented Feb 7, 2023 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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