1

I am trying to create a MULTIPOLGYON in PostgreSQL from WKT format using arcpy.da module and psycopg2. I have had success creating point and multi point shapes this way however I cannot seem to get how to format the code for a MULTIPOLGYON.

I have read over the documentation http://postgis.net/docs/manual-1.4/ST_GeomFromText.html but it does not give any examples of inserting a variable holding the WKT geometry.

this is my part of the code I am having trouble with

cur = conn.cursor()  #setting up connection cursor
cur.execute('''drop table polygon''')
cur.execute('''CREATE TABLE polygon
            (pid serial primary key,
             Name TEXT,
             geom geometry);''')
conn.commit()
for row in arcpy.da.SearchCursor(shp, ["name","SHAPE@"]):
    name =  row[0]
    geome = row[1].WKT
    cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText('MULTIPOLYGON'(%s)))''', (name,geome))
    conn.commit()

this is the error

traceback (most recent call last):
  File "<module1>", line 20, in <module>
ProgrammingError: syntax error at or near "("
LINE 1: ...values('Minnesota', ST_GeomFromText('MULTIPOLYGON'('MULTIPOL...

I have tried taking out the 'MULTIPOLYGON' but it still has not worked! what else can I do!

3
  • Your error message says that it has a syntax error on line 20 but the code you presented does not have that many lines. Please always provide error messages that come from running the precise code snippet that you present.
    – PolyGeo
    Sep 29, 2016 at 0:04
  • 1
    Based on the error it looks like the word Multipolygon is in your wkt string at least twice, it should only be in there once. Should look similar to: ST_GeomFromText('MULTIPOLYGON(((-71.1031880899493 42.3152774590236, -71.1031627617667 42.3152960829043,-71.102923838298 42.3149156848307,...
    – Brian
    Sep 29, 2016 at 0:05
  • 1
    Definitely take out the MULTIPOLYGON in code, because that is included in the WKT. You're probably adding one too many sets of brackets. ST_GeomFromText(%s) is all you need for that portion. Sep 29, 2016 at 0:07

1 Answer 1

1

You have too many brackets. Based on your code and comments,

cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText('MULTIPOLYGON'(%s)))''', (name,geome))

should be

cur.execute('''insert into polygon(Name, geom)values(%s, ST_GeomFromText(%s))''', (name,geome))

I also recommend looking into GeoAlchemy 2 as a nicer, more generic, and safer way of writing this.

1
  • ahh gotcha, so geome is going to have MULTIPOLYGON(COORDS)
    – ziggy
    Sep 29, 2016 at 0:12

Your Answer

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

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