0

I am receiving the following error when trying to execute ST_GeomFromText(wkt)

psycopg2.ProgrammingError: syntax error at or near "1187780.76" LINE 1: ...0822.05,(ST_GeomFromTEXT(POLYGON ((7458952.505715 1187780.76...

The project interpreter displays an arrow pointing at the 1 in 1187780.76 to show where it runs into the "error".

Here is the code

def import_shape(db_conn, db_cur, file_input, SRID, output_name):
     connection = db_conn 
     cursor = db_cur 
     cursor.execute("""DROP TABLE IF EXISTS {output_name}""".format(output_name=output_name))
     srcFile = file_input 
     shapefile = osgeo.ogr.Open(srcFile)
     layer = shapefile.GetLayer(0)
     for i in range(layer.GetFeatureCount()):
         feature = layer.GetFeature(i)
         name = feature.GetField("CTUID").decode("Latin-1")
         wkt = feature.GetGeometryRef().ExportToWkt()
         cursor.execute("""INSERT INTO {output_name} (name, geom) VALUES ({name},(ST_GeomFromTEXT({outline})))""".format(output_name=output_name,name=name,outline=wkt,srid=SRID))

     connection.commit()

I have also tried ST_GeomFromEWTK and it returns the same error.

I am using PostGIs 2.4.4 Postgres 9.6 Geos 3.6.2

2

Try adding single quotes around the WKT:

cursor.execute("""INSERT INTO {output_name} (name, geom) 
  VALUES ({name},(ST_GeomFromTEXT('{outline}')))""".format(output_name=output_name,name=name,outline=wkt,srid=SRID))
  • Thank you! this is exactly what was missing! This was one of those NO-DUH moments where after reading your comment I realized trying quotes to turn the input into a string would make sense for a well-known-TEXT input..DUHHH :) Thanks! – Marshall Jan 31 at 16:23
2

Formatting SQL queries should be avoided (e.g., see Bobby Tables), which can be done by passing the data using parameters. For example:

sql = """\
    INSERT INTO {output_name} (name, geom)
    VALUES (%s, ST_SetSRID(%s::geometry, %s))
""".format(output_name=output_name)
cursor.execute(sql, (name, wkt, SRID))

In this case, output_name is formatted to the SQL string to add the table name to the query, which is something that cannot be done with parameters. And the second part passes three parameters (each marked with a %s) to the query. For the geometry parameter, a simple %s::geometry will work fine for WKT or hex-encoded WKB strings. Also, the SRID is set on the server side in this example.

Furthermore, this example could be sped up considerably by creating a list of parameter tuples, then after the for-loop, use one curs.executemany.

  • This is the more correct answer, even though both work. – John Powell Jan 31 at 16:29

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.