1

I have a feature class of 20k records. trying to import it into postgis using arcpy and psycogp2. I have a pretty basic script

cur.execute('''drop table if exists developed_lands.daysemtric_test''')
cur.execute('''CREATE TABLE developed_lands.daysemtric_test
            (pid serial primary key,
            pop_dns float,
             shape geometry);''')
with arcpy.da.SearchCursor(shp, ["POP_DNS","SHAPE@WKT"]) as cur2:
    for row in cur2:
        sql = "insert into developed_lands.daysemtric_test(pop_dns,shape)values(%s,ST_GeomFromText(%s)"
        cur.execute(sql,(row[0],row[1]))
        x+=1
        print x
conn.commit()

error:

   Traceback (most recent call last):
  File "C:\Users\rizagha\Desktop\module1.py", line 16, in <module>
    cur.execute(sql,(row[0],row[1]))
InternalError: geometry requires more points
HINT:  "...6391.65285167098 737288.47085659206)," <-- parse error at position 13049795 within geometry

                                                                   ^

This script works because If I skip the first feature it inserts the records up to row 2,000 then throws the same error. I put the function st_makevalid() around ST_GeomFromText(%s) and it did nothing to help. running repair geometry on the feature class now, but that usually takes hours...

the first row where the error is happening, the geometry is extremely complex. is it possible the WkT is too long?

UPDATE

repair geometry finished and I reran the script and I got the same error

  • It looks like your sql = line is missing a closing bracket at the end before the end quote. – HeikkiVesanto Nov 1 '17 at 14:57
  • You could do a print of the sql command, instead of an execute. That might shine light on the issue. – HeikkiVesanto Nov 1 '17 at 14:58
  • @HeikkiVesanto woops that was an error when I posted the it on stackexchange. question updated – ziggy Nov 1 '17 at 15:03
  • What is the WKT for this geometry? The error message only shows a small snippet. – Mike T Nov 1 '17 at 18:17
  • @MikeT way to big to post here. I can barely open it up in notepad. this FC is from a massively dissolved layer. so each record especially the first few are extremely complex shapes – ziggy Nov 1 '17 at 18:29
1

TL;DR;: PostGIS restricts some kinds of invalid WKT geometries that are otherwise allowed by some formats and software, such as shapefiles and ArcGIS. You can sneak these in via WKB.


Let's take an invalid geometry, described by WKT:

POLYGON((5 5, 20 30, 30 5, 5 5), (22 13, 16 13, 22 13))

invalid

This shape can be loaded into Esri and GDAL/OGR software.

# Esri
import arcpy
e = arcpy.FromWKT('POLYGON((5 5, 20 30, 30 5, 5 5), (22 13, 16 13, 22 13))')
print(e.JSON)
# {"rings":[[[30,5],[20,30],[5,5],[30,5]],[[16,13],[22,13],[16,13]]],"spatialReference":{"wkid":null}}

# GDAL/OGR
from osgeo import ogr
g = ogr.CreateGeometryFromWkt('POLYGON((5 5, 20 30, 30 5, 5 5), (22 13, 16 13, 22 13))')
print(g.IsValid())  # False
print(g.ExportToJson())
# { "type": "Polygon", "coordinates": [ [ [ 5.0, 5.0 ], [ 20.0, 30.0 ], [ 30.0, 5.0 ], [ 5.0, 5.0 ] ], [ [ 22.0, 13.0 ], [ 16.0, 13.0 ], [ 22.0, 13.0 ] ] ] }

But JTS, GEOS and PostGIS do not allow this kind of invalid geometry in WKT form:

SELECT 'POLYGON((5 5, 20 30, 30 5, 5 5), (22 13, 16 13, 22 13))'::geometry;

ERROR:  geometry requires more points
LINE 1: SELECT 'POLYGON((5 5, 20 30, 30 5, 5 5), (22 13, 16 13, 22 1...
               ^
HINT:  "...0, 30 5, 5 5), (22 13, 16 13, 22 13))" <-- parse error at position 55 within geometry

However, you can load the WKB equivalent into PostGIS without issue:

SELECT '010300000002000000040000000000000000001440000000000000144000000000000034400000000000003E400000000000003E400000000000001440000000000000144000000000000014400300000000000000000036400000000000002A4000000000000030400000000000002A4000000000000036400000000000002A40'::geometry AS g
INTO TEMP geoms;
SELECT ST_AsEWKT(g) FROM geoms;

                     st_asewkt
---------------------------------------------------
 POLYGON((5 5,20 30,30 5,5 5),(22 13,16 13,22 13))
(1 row)

So you can modify your INSERT to use hex-encoded WKB instead of WKT. I'm not sure what Esri's e.WKB is (it's not ISO or EWKB), but you can use OGR for this: g.ExportToWkb().encode('hex')

And you should repair these geometries to avoid further complications:

SELECT ST_AsText(ST_CollectionExtract(ST_MakeValid(g), 3)) FROM geoms;

              st_astext
--------------------------------------
 MULTIPOLYGON(((5 5,20 30,30 5,5 5)))
(1 row)

(but use UPDATE instead).

valid

  • sweet il give this a shot. the first step would be how to get wkb from arcpy – ziggy Nov 2 '17 at 12:30
  • great the wkb worked for me seemlessly. but I have no idea why? – ziggy Nov 2 '17 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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