4

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()
Improve this question
1
  • why is this array elements deep? eww.
    – Evan Carroll
    Jan 12, 2017 at 6:36

1 Answer 1

Reset to default
3

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.

Improve this answer
2
  • 1
    thanks for the response. i couldn't find anything on 'rings' data type. as for the SQL, your answer is a tad bit over my skill level. why do you use a cross join? and the what exactly does lateral do?
    – ziggy
    Jan 12, 2017 at 14:51
  • 2
    A CROSS JOIN is a method in SQL that explicitly multiplies the rows. It's clear then that what we're doing is generating a Cartesian product. If I put it in the SELECT clause which I can do, the ordering is unspecified and the intent is harder to infer. This way. You see I'm selecting 1 row (from values), and the multiplying that by the result of jsonb_array_elements(row) and I'll have rowct1*rowct2 total rows. The select is just changing what is return. Not the quantity.
    – Evan Carroll
    Jan 13, 2017 at 5:49

Your Answer

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

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