2

I want to import shapefiles to PostGIS using Python with arcpy & psycopg2 to connect to PostGIS. I don't want to use the existing shapefile loader nor ogr, just write my own import tool.

My steps and where I got stuck:

  1. Define shapefile
  2. Select column names from shapefile into a list
  3. Create SQL CREATE TABLE query with column names of shapefile
  4. Insert values from shapefile into PostGIS database

I got stuck at point 4 since I don't really know how to extract the values from the shapefile and insert it into the database taking account of the correct column.

Any idea?

Code example:

import arcpy
import psycopg2

arcpy.env.workspace = r"path\to\data"

shp = "BRD.shp"
shp2 = shp[:3]

desc_shp = arcpy.Describe(shp)

#set variables and create list wo OID and Shape attributes
if desc_shp.hasOID:
    print desc_shp.OIDFieldName
    shp_OID = desc_shp.OIDFieldName

print desc_shp.shapeFieldName    
shp_geom = desc_shp.shapeFieldName

fields = desc_shp.fields
field_names = []
for field in fields:
    if not (field.name == shp_OID or field.name == shp_geom):
        field_names.append(field.name)
new_list = field_names
print new_list

sc_da_cursor = arcpy.da.SearchCursor(shp,shp_geom)
for sc_da_row in sc_da_cursor:
    print sc_da_row[0]

# PostGIS part after here

user = "postgres"
pw = "postgres"
db = "pypg"
host = "localhost"
port = 5432

conn_obj = psycopg2.connect(database=db, user=user, password=pw, host=host, port=port)
cur_obj = conn_obj.cursor()

sql_createtable = "CREATE TABLE " + shp2 + "(" + shp_OID + " serial PRIMARY KEY," + 
shp_geom + " geometry," + new_list[0] + " VARCHAR(100)," +  new_list[1] +
" VARCHAR(100))"

sql_insert = "INSERT INTO " + shp2 + "(" + shp_geom + ") VALUES(" + str(sc_da_row[0]) +
")"
cur_obj.execute(sql_insert)

conn_obj.commit()
conn_obj.close()
cur_obj.close()
1

I just used the below code to transfer a shapefile into PostGIS. I saw your post and thought it might help. The Shape@WKT makes it really easy to transfer the geometry. Everything is hard coded and works which I am happy with and will probably revisit at a later date when my coding improves. If anything is not clear please let me know.

import psycopg2, arcpy
# shapefile to get data from
data = ("C:/shapefiles/georgia_counties.shp")
# fields I want from shapefile
fields = ["AREANAME", "TotPop90", "PctRural", "PctBach", "PctEld", "PctFB", "PctPov", "PctBlack", "ID", "Shape@WKT"]

# pscopg2 connection, replace *** and *** with your values
connection = psycopg2.connect("dbname=*** user=***")
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS georgia")
cursor.execute("""
    CREATE TABLE georgia (
        id SERIAL,
        name VARCHAR,
        totpop INTEGER,
        pctrural DOUBLE PRECISION,
        pctbach DOUBLE PRECISION,
        pcteld DOUBLE PRECISION,
        pctfb DOUBLE PRECISION,
        pctpov DOUBLE PRECISION,
        pctblack DOUBLE PRECISION,
        county_id VARCHAR,

        PRIMARY KEY (county_id))
""")

cursor.execute("""
    SELECT AddGeometryColumn('georgia', 'geom', 32616, 'MULTIPOLYGON', 2)
""")

# use arcpy to get attribute data, populate PostGIS using psycopg2
with arcpy.da.SearchCursor(data, fields) as da_cursor:
    for row in da_cursor:
        wkt = row[9]
        # the id was transferring as a float so this is just to remove decimal
        id = int(row[8])
        # this was tough - everything needs to be a string and text being inserted wrapped in '' including wkt
        cursor.execute("INSERT INTO georgia (name, totpop, pctrural, pctbach, pcteld, pctfb, pctpov, pctblack, county_id, geom) VALUES (" + '\'' + row[0] + '\'' + ", " + str(row[1]) + ", " + str(row[2]) + ", " + str(row[3]) + ", " + str(row[4]) + ", " + str(row[5]) + ", " + str(row[6]) + ", " + str(row[7]) + ", " + str(id) + ", ST_GeometryFromText(" + "'" + wkt + "', 32616))")

connection.commit()
  • 3
    You can clean up the cursor.execute call a bit by passing your variables in as parameters...checkout the psycopg2 docs. You might also want to consider using SHAPE@WKB and ST_GeomFromWKB so that you're transferring an exact copy of the geometry to the database. – dbaston Jul 31 '15 at 21:46
  • While you're at it, you can add a spatial index as well. – nickves Jul 10 '16 at 0:51
0

I am pretty new to the whole pyscopg2 python connection but it is super useful. I recently wrote this script. I hard coded it(would be more useful if I wrote a function) but nevertheless it works totally fine.

*id would recommend defining your database connection at the top of your scripts up by your import statements, for a more organized code structure

*when creating tables and inserting values into columns avoid using 'columnname'+value+'columname2'+value2 etc... it gets clunky and hard to read. try using the .format() function or %s to insert values and create tables

import arcpy
import psycopg2
import arcgisscripting
gp = arcgisscripting.create()
conn = psycopg2.connect("dbname='shapefile' user='postgres' host='localhost' password='mypass'") #connecting to DB
cur = conn.cursor()  #setting up connection cursor
cur.execute('''drop table drugpoints''')
cur.execute('''CREATE TABLE drugpoints
            (id serial primary key,
             Name text,
             Type  text,
             x float,
             y float,
             geom geometry);''')
conn.commit()
arcpy.env.workspace = "path"
shp = "path\\drugfreezone.shp"
for row in gp.Searchcursor(shp):
    ty = str(row.type_)
    name = str(row.name)
    x = float(row.x)
    y = float(row.y)
    print ty, name, x, y
    cur.execute('''insert into drugpoints(Name, Type, x, y, geom)
                    values(%s,%s,%s,%s,ST_SetSRID(ST_MakePoint(%s,%s), 4269))'''
                    ,(ty, name, x,y,x,y))
    conn.commit()

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.