Converting PostGIS table to Feature Set using ArcPy

Question

I am writing an ArcPy script (ArcGIS Pro) to export tile in the area of interest with help of arcpy.ExportTileCache_management(). I am getting the area of interest from a table in PostGIS. As far as I read in the documentation are the area of interest must be Feature Set data type.

I tried FeatureSet.from_json to convert my table in PostGIS but it was not successful. I kept getting the following error

File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\IPython\core\interactiveshell.py", line 3326, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-2-90516b90cd60>", line 1, in <module>
    runfile('C:/Users/ncipa/PycharmProjects/arcpy/test.py', wdir='C:/Users/ncipa/PycharmProjects/arcpy')
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.5\helpers\pydev\_pydev_bundle\pydev_umd.py", line 197, in runfile
    pydev_imports.execfile(filename, global_vars, local_vars)  # execute the script
  File "C:\Program Files\JetBrains\PyCharm Community Edition 2018.3.5\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)
  File "C:/Users/ncipa/PycharmProjects/arcpy/test.py", line 518, in <module>
    areaofinterest = FeatureSet.from_json(polygon)
  File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\features\feature.py", line 694, in from_json
    return FeatureSet.from_dict(json.loads(json_str))
  File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\json\__init__.py", line 348, in loads
    'not {!r}'.format(s.__class__.__name__))
TypeError: the JSON object must be str, bytes or bytearray, not 'dict'

How can I convert the record in my table in PostGIS to Feature Set?

# Connect and query tables in Postgresql
try:
    connection = psycopg2.connect(user='xxx',
                                  password='xxx',
                                  host="xxx",
                                  port="5432",
                                  database="xxx")
    cursor = connection.cursor()

    # Fetch records
    postgreSQL_select_Query1 = "select pgnr,  ST_AsGeoJSON(geom) from xxx"
    cursor.execute(postgreSQL_select_Query1)
    records = cursor.fetchall()


except (Exception, psycopg2.Error) as error:
    print("Error while connecting to PostgreSQL")

finally:
    # closing database connection.
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

for i, row in enumerate(records):

    polygon = json.loads(row[1])

    cacheSource = r"E:\xxx_cache"
    cacheTarget = r"E:\_xx"
    cacheName = "Target"
    cacheType = "TILE_CACHE"
    storageFormat = "Compact"
    scales = "100000"
    areaofinterest = FeatureSet.from_json(polygon)

    arcpy.ExportTileCache_management(cacheSource, cacheTarget, cacheName,
                                     cacheType, storageFormat, scales, areaofinterest)

    if i == 0:
        break

Answer 1

There are a few things here that might be causing your problem.

1. I assume this is an actual arcpy FeatureSet: areaofinterest = FeatureSet.from_json(polygon) The proper way to "put something" into a featureset is via load. Documentation reference That said, I'm not entirely sure what it is you're getting out of your postgres connection. Its very possible that output simply cannot be pumped into a feature set. It may require some transformation. I just don't know. Or maybe you can use FeatureSet.load(), and it'll just work. If that's the case, you're done.

2. Input tool parameters can sometimes be confusing. You're right, that yes, the area of interest parameter requires a feature set as input. However, in the Esri-GP-Tool world, a feature set can be multiple things. The feature set parameter is a subset of feature class. It's used because when you're in the app (ArcMap / Pro) it allows one to draw features directly on the map right before they run the tool. This allows a user to skip the process of going and making a brand new featureclass and digitizing a shape. So, you could provide a featureclass as input into this parameter instead of an arcpy featureset.

Suggestion -- (this is based on what I think you can get out of your postgres connection) Throw the feature set idea away, just forget about it completely. Try to figure out how to get the postgres connection >> data (layer) into a "esri featureclass". Apologies, I can't offer a true solution as I'm not familiar with the psycopg2 module and what it provides. There are some hits here on GIS.SE that may provide some start points.

• using psycopg2 with arcgis
• Lots of other posts, but the majority specific to pushing data INTO postgres, not getting it out