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