2

I am trying to load a polygon feature class in a file geodatabse to a PostgreSQL table (extension PostGIS installed). This the process I'm following:

  • I create table in PostgreSQL with required fields
  • Read feature class using GeoPandas
  • Loop through geodataframe containing the polygons from the feature class and insert into postgres table.

I seem to be having problems when inserting the geometry field.

This is the code to create the PostgreSQL table:

import os, csv, logging, sys, time
import psycopg2
import geopandas as gpd


#this is the path to the feature class
fc_vml_polygs = .......\geodatabase.gdb\test'

conn_string = "host=XXXXX" + " port=" + "5432" + " dbname=postgres" + " user=postgres" + " password=postgres"
con = psycopg2.connect(conn_string)
cursor = con.cursor()

create_table = """CREATE TABLE public.vml_polygs
(
    objectid integer NOT NULL,
    fcode smallint,
    shape_length double precision,
    shape_area double precision,
    geom geometry(MultiPolygon,27700),
    CONSTRAINT vml_polygs_pkey PRIMARY KEY (objectid)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.vml_polygs
    OWNER to postgres;

CREATE INDEX vml_polygs_geom_geom_idx
    ON public.vml_polygs USING gist
    (geom)
    TABLESPACE pg_default;"""

cursor.execute(create_table)
con.commit()

Now I read feature class in geodatabase with geopandas and try to load to postgres

#Read polygons feature class
vml_polygs = gpd.read_file(r'........\geodatabase.gdb', driver='FileGDB', layer='test')

#Loop through polygons to load them into pstgres
for index, row in vml_polygs.iterrows():
    load_to_pg = "INSERT INTO public.vml_polygs \
                  VALUES ({0}, {1}, {2}, {3});".format(index, row[0], row[1], row[2])
    cursor.execute(load_to_pg)
    con.commit()

It throws the following error:

Traceback (most recent call last):
  File "footprint.py", line 70, in <module>
    cursor.execute(load_to_pg)
psycopg2.ProgrammingError: syntax error at or near "306481.5500000007"
LINE 1: ...816, 76.65149999882934, MULTIPOLYGON (((452640.71 306481.550...

If I don't try to load the geometry field it works fine. It seems I need to do soemthing with the geometry field of the geodataframe before loading it into PostgreSQL.

  • it seems you're not passing the WKT as string? try enclosing it and cast, e.g. '{3}'::GEOMETRY (don't forget to escape), or let format() make it a string literal – ThingumaBob Jul 12 at 12:29
  • can you use arcpy instead? or are you bound to geopandas? – ziggy Jul 12 at 12:40
  • @ ziggy - I can't use arcpy, it needs to be opensource. – Pitrako Junior Jul 12 at 12:58
  • @ThingumaBob - What do you mean with 'don't forget to escape'? And how can I let format() make it a string literal? – Pitrako Junior Jul 12 at 13:00
  • I have never used geopandas but you need make sure your geometry is in WKT or the binary version and then when inserting into the geometry column you need to call ST_GeomFromText – ziggy Jul 12 at 13:35
2

Another approach is to use one of the GDAL utilities - OGR2OGR. Using this you can upload your feature class to your database and create a new table. You can use this command line tool as follows

ogr2ogr -f PostgreSQL PG:"host=xxxx dbname=xxxx user=xxxx password=xxxx" path_to_gdb test -nln vml_polygs

This effectvely says, output to PostgreSQL format in the database xxxx the layer test from the geodatabase path_to_gdb. The -nln is a switch to say call the new table vml_poygs. You dont need to specify port because 5432 is the default.

Checkout the linked documentation for more options. It's a really powerful tool.

In this case as the source file is a geodatabase the above relies on your GDAL installation having a FileGDB driver. If, for whatever reason, this is not available you can always export your feature class to a shapefile and replace path_to_gdb test with path_to_shp.shp path_to_shp.

Alternatively you can use the dedicated shp2pgsql shapefile upload tool that should be in your PostgreSQL install /bin folder.

  • When I try to run the command line tool I get the following error: "Unable to open datasource path_to_gdb with the following drivers. -> 'PCDISK' -> 'netCDF' -> ....... (the list goes contains 70 or 80 drivers)" – Pitrako Junior Jul 12 at 20:06
  • Can you provide the full command that you're using? Can you also run ogrinfo --version and specify the version you have? – Ali Jul 13 at 8:21
  • ogr2ogr -f "PostgreSQL" PG:"host=XXXX dbname=postgres user=postgres password=xxxxx" c:\godatabase.gdb test -nln vml_polygs – Pitrako Junior Jul 13 at 9:10
  • You've misspelled geodatabase in your gdb filename - is that what your gdb is actually called? Also, what's your OGR version? – Ali Jul 13 at 11:14
  • Version => GDAL 2.3.3, released 2018/12/14 That's not the real path to the geodatabase, I was just wanted to simplify it to report it here. – Pitrako Junior Jul 13 at 11:43
0

You need to make sure Geopandas is returning the geometry in some sort of WKT or WKB or EWKT or even geojson format so it can be consumed by one of the postgis functions. assuming the geometry is WKT then you can use ST_GeomFromText to convert it into the proper geometry

"INSERT INTO public.vml_polygs VALUES (%s, %s, %s,ST_GeomFromText(%s));".format(index, row[0], row[1], row[2])

The geometry you provided me is valid when I ran this in Postgres...

select st_geomfromtext( 'MULTIPOLYGON (((452640.71 306481.5500000007, 452634.25 306474.8200000003, 452628.3499999996 306480.5600000005, 452634.71 306487.2599999998, 452640.71 306481.5500000007)))')

I think this should work

cursor.execute("""INSERT INTO public.vml_polygs VALUES values(%s,%s,%s,ST_GeomFromText(%s))""",(index, row[0], row[1], row[2]))
  • The geodataframe created when reading with geopandas contains the geometry like this => MULTIPOLYGON (((452640.71 306481.5500000007, 452634.25.............. I try to convert it with ST_GeomFromText but it gives a syntax error (Yes, row[2] contains the geometry) – Pitrako Junior Jul 12 at 13:58
  • I'm using this instruction: "INSERT INTO public.vml_polygs VALUES ({0}, {1}, {2}, ST_GeomFromText('{3}'::GEOMETRY,27700));".format(index, row[0], row[1], row[2]) -------- it throws a parse error at position 48 within geometry (??) – Pitrako Junior Jul 12 at 14:05
  • post one record of your geometry – ziggy Jul 12 at 15:04
  • like MULTIPOLYGON (((452640.71 306481.5500000007, 452634.25... but finish it off – ziggy Jul 12 at 15:04
  • MULTIPOLYGON (((452640.71 306481.5500000007, 452634. 1 306487.2599999998, 452640.71 306481.5500000007))) – Pitrako Junior Jul 12 at 15:13

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.