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!

  • 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 '16 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 '16 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. – Richard Law Sep 29 '16 at 0:07
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.

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

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.