2

I am trying to load a geodataframe to a PostgreSQL table, but I am struggling with the geometry field.

First I read feature class using Geopandas:

geodata = (r'path_to_gdb', driver='FileGDB', layer='test')

Then I try to export it to an existing PostgreSQL table called "vml_polygs":

for index, row in geodata.iterrows():
    load_to_pg = "INSERT INTO public.vml_polygs VALUES ({0}, {1}, {2}, {3}, ST_GeomFromText('{4}'::GEOMETRY,27700));".format(index, row[0], row[1], row[2], row[3])
    cursor.execute(load_to_pg)
    con.commit()

row[3] contains the geometry of the geodataframe. It's in WKT. With the expression ST_GeomFromText('{4}'::GEOMETRY,27700)I am trying to convert it to WKB since I believe that's the required format of the geometry for PostgresSQL (please, correct me here if I'm wrong).

I am getting the following error:

Traceback (most recent call last):
  File "footprint.py", line 72, in <module>
    cursor.execute(load_to_pg)
psycopg2.InternalError: parse error - invalid geometry
HINT:  "...100000001030000000100000005000000713D" <-- parse error at position 48 within geometry

And if I try with the following instruction:

load_to_pg = "INSERT INTO public.vml_polygs VALUES ({0}, {1}, {2}, {3}, ST_GeomFromText({4}, 27700));".format(index, row[0], row[1], row[2], str(row[3]))

I get this error message:

Traceback (most recent call last):
  File "footprint.py", line 71, in <module>
    cursor.execute(load_to_pg)
psycopg2.ProgrammingError: syntax error at or near "306481.5500000007"
LINE 1: ...882934, ST_GeomFromText(MULTIPOLYGON (((452640.71 306481.550...
  • Don't use format to general SQL. Use parameterized queries. Formatting in this manner creates security vulnerabilities. – jpmc26 Jul 13 at 19:23
  • I think you should look into the the geodata.to_sql method – Paul H Jul 14 at 15:16
2

I finally found out what the right instruction is. Please, see below:

   load_to_pg = "INSERT INTO public.vml_polygs (objectid, fcode, shape_length, shape_area, geom) VALUES ({0}, {1}, {2}, {3}, ST_GeomFromText('{4}', 27700));".format(index, row[0], row[1], row[2], str(row[3]))

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.