PostGIS Python – Importing JSON Rings Data to PostGIS Using Psycopg2

postgispostgresqlpsycopg2python

Pulling data from a arcgis web query and using python/psycopg2 to insert it into postgreSQL. Here is one of the geometry attributes

js = json.dumps(i['geometry']['rings'])

this line prints 

[[[336618.9998379126, 642371.4375939071], [336323.46893291175, 642018.6873151585], [336115.2813571617, 642248.7498471588], [336121.53134466335, 642254.3751639873], [336137.81248007715, 642268.6251354888], [336154.3436149955, 642282.6247794032], [336171.1250774972, 642296.2500802353], [336188.1558833271, 642309.6873893216], [336205.40617691353, 642322.7500272393], [336222.87497399375, 642335.500001736], [336240.56260266155, 642347.9999767393], [336267.0937175788, 642365.4999417365], [336292.5624986626, 642381.6872453243], [336318.28127924725, 642397.4375419021], [336344.2815553285, 642412.6875114068], [336370.56267074496, 642427.4998177364], [336397.0937856622, 642441.875117071], [336401.7814403288, 642443.8747849911], [336406.43759899586, 642445.937444903], [336411.06258974597, 642448.062768735], [336415.6564125791, 642450.3127642423], [336420.1875714958, 642452.6874314025], [336424.68756249547, 642455.0624266565], [336429.1560574956, 642457.5624216571], [336433.56254474446, 642460.1250805706], [336437.93753599375, 642462.7500753254], [336442.25019141287, 642465.4377339855], [336446.5313508287, 642468.2500643209], [336450.7813423276, 642471.0623946562], [336454.93750199676, 642474.0000528246], [336459.06249374524, 642477.0000468194], [336463.12514966354, 642480.062704742], [336467.15630957857, 642483.2500343248], [336471.1251336634, 642486.4373639077], [336475.03129382804, 642489.7500213236], [336478.8747900799, 642493.0626787394], [336482.6562785804, 642496.5000078231], [336486.37510316074, 642500.0000008196], [336490.0627598278, 642503.5623296574], [336491.18742949516, 642504.6876554862], [336618.9998379126, 642371.4375939071]]]

When I manually download this shapefile the geometry type is MultiPolygon.

the below code is within a for loop. 

js = json.dumps(i['geometry']['rings'])
osc =  i['attributes']['OS_CLASS']
qry = "insert into preserve (os_class,geom) values(%s,ST_SetSRID(ST_GeomFromText(%s),3424));"
cur.execute(qry,(osc,geojson2))
conn.commit()

this is the error i get

Traceback (most recent call last):
  File "<module1>", line 22, in <module>
InternalError: parse error - invalid geometry
HINT:  "{"" <-- parse error at position 2 within geometry

this js = json.dumps(i['geometry']) prints 

{"rings": [[[336618.9998379126, 642371.4375939071], [336323.46893291175, 642018.6873151585], [336115.2813571617, 642248.7498471588], [336121.53134466335, 642254.3751639873], [336137.81248007715, 642268.6251354888], [336154.3436149955, 642282.6247794032], [336171.1250774972, 642296.2500802353], [336188.1558833271, 642309.6873893216], [336205.40617691353, 642322.7500272393], [336222.87497399375, 642335.500001736], [336240.56260266155, 642347.9999767393], [336267.0937175788, 642365.4999417365], [336292.5624986626, 642381.6872453243], [336318.28127924725, 642397.4375419021], [336344.2815553285, 642412.6875114068], [336370.56267074496, 642427.4998177364], [336397.0937856622, 642441.875117071], [336401.7814403288, 642443.8747849911], [336406.43759899586, 642445.937444903], [336411.06258974597, 642448.062768735], [336415.6564125791, 642450.3127642423], [336420.1875714958, 642452.6874314025], [336424.68756249547, 642455.0624266565], [336429.1560574956, 642457.5624216571], [336433.56254474446, 642460.1250805706], [336437.93753599375, 642462.7500753254], [336442.25019141287, 642465.4377339855], [336446.5313508287, 642468.2500643209], [336450.7813423276, 642471.0623946562], [336454.93750199676, 642474.0000528246], [336459.06249374524, 642477.0000468194], [336463.12514966354, 642480.062704742], [336467.15630957857, 642483.2500343248], [336471.1251336634, 642486.4373639077], [336475.03129382804, 642489.7500213236], [336478.8747900799, 642493.0626787394], [336482.6562785804, 642496.5000078231], [336486.37510316074, 642500.0000008196], [336490.0627598278, 642503.5623296574], [336491.18742949516, 642504.6876554862], [336618.9998379126, 642371.4375939071]]]}

I am unsure of how to manipulate the data and which postgis function to use to insert it into postgresql. I also do not know what the data type rings means

here is a link to the data http://geodata.state.nj.us/arcgis/rest/services/Applications/NJ_Highlands/MapServer/34/query?where=OS_CLASS+%3D+%27MUNICIPAL%27&text=&objectIds=&time=&geometry=&geometryType=esriGeometryPolygon&inSR=&spatialRel=esriSpatialRelIntersects&relationParam=&outFields=&returnGeometry=true&returnTrueCurves=false&maxAllowableOffset=&geometryPrecision=&outSR=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&returnDistinctValues=false&resultOffset=&resultRecordCount=&f=pjson

Additionally, I have used the ST_GeomFromText function but I got the same error

UPDATE

got this too run and the layer is now in postgresql. However when I try and bring the data into QGIS nothing shows up at all. The coordinates seem to be NJ state plane, which is SRID 3424. 

js = json.dumps(i['geometry']['rings'])
data = {"type": "MULTIPOLYGON",
    "coordinates":js}
osc =  i['attributes']['OS_CLASS']
qry = "insert into preserve (os_class,geom) values(%s,ST_SetSRID(ST_GeomFromGeoJSON(%s),3424));"
cur.execute(qry,(osc,json.dumps(data)))
conn.commit()

Best Answer

A bunch of things...

That's not GEOJSON

That's actually an obscure and proprietary ArcGIS api. There are ways to handle that if you want (which may be a better idea than my quick and dirty solution). Just to get it out of the way, I already tested on your REST url that you provided f=geojson is not supported (ArcGIS REST API did not enable it or is otherwise too old).

We can read the ArcGIS in PostgreSQL too

SELECT ST_AsText(ST_MakeLine(
  ST_MakePoint((element->0)::text::double precision, (element->1)::text::double precision)
  ORDER BY elementord
))
FROM (
  SELECT $$[[[336618.9998379126, 642371.4375939071], [336323.46893291175, 642018.6873151585], [336115.2813571617, 642248.7498471588], [336121.53134466335, 642254.3751639873], [336137.81248007715, 642268.6251354888], [336154.3436149955, 642282.6247794032], [336171.1250774972, 642296.2500802353], [336188.1558833271, 642309.6873893216], [336205.40617691353, 642322.7500272393], [336222.87497399375, 642335.500001736], [336240.56260266155, 642347.9999767393], [336267.0937175788, 642365.4999417365], [336292.5624986626, 642381.6872453243], [336318.28127924725, 642397.4375419021], [336344.2815553285, 642412.6875114068], [336370.56267074496, 642427.4998177364], [336397.0937856622, 642441.875117071], [336401.7814403288, 642443.8747849911], [336406.43759899586, 642445.937444903], [336411.06258974597, 642448.062768735], [336415.6564125791, 642450.3127642423], [336420.1875714958, 642452.6874314025], [336424.68756249547, 642455.0624266565], [336429.1560574956, 642457.5624216571], [336433.56254474446, 642460.1250805706], [336437.93753599375, 642462.7500753254], [336442.25019141287, 642465.4377339855], [336446.5313508287, 642468.2500643209], [336450.7813423276, 642471.0623946562], [336454.93750199676, 642474.0000528246], [336459.06249374524, 642477.0000468194], [336463.12514966354, 642480.062704742], [336467.15630957857, 642483.2500343248], [336471.1251336634, 642486.4373639077], [336475.03129382804, 642489.7500213236], [336478.8747900799, 642493.0626787394], [336482.6562785804, 642496.5000078231], [336486.37510316074, 642500.0000008196], [336490.0627598278, 642503.5623296574], [336491.18742949516, 642504.6876554862], [336618.9998379126, 642371.4375939071]]]$$::jsonb->0
) AS t(json)
CROSS JOIN LATERAL jsonb_array_elements(json)
  WITH ORDINALITY
  AS jae(element,elementord);

Here we're

  1. Casting the text string as binary json and drilling down a level with ::jsonb->0
  2. Turning the JSON into a result set.
  3. Making an ST_Point out of the array by ST_MakePointing the first element the x cord, and the second element the y cord.
  4. Calling the aggregate version of ST_MakeLine to make a line.
  5. Displaying it as text to show it's done.

We can also read the ArcGIS KMZ

That REST URL you provided (nice question) supports .kmz. You may find it better to download that and unzip it. Then you have to extract just the geometry component and use ST_GeomFromKML

ST_GeomFromKML works only for KML Geometry fragments. It throws an error if you try to use it on a whole KML document.

Related Question