0

I am trying to insert points geometry into PostGIS database. Well I tried to use a function just to read point coordinates and attributes and save them in a table in PostGIS. It works for attributes but not for geometry. This is my python code:

insert(id, Height, X, Y)
....
def insert(gid,height, X, Y):
   gid=str(gid)
   connection = psycopg2.connect(host='', dbname='postgres',   user='', password='')     
    cur = connection.cursor()
   cur.execute ("""INSERT INTO gronkarta.test5 (gid, height, geom) VALUES (""" + gid + """,""" + str(height) + ""","""+ST_GeomFromText('POINT(X Y)', 3008)+""");""") 
  • I used Python for this code. – Mozafar Veysipanah Aug 3 '16 at 6:51
  • Use something like stackoverflow.com/questions/902408/…. There is no need to build up a string like that. – John Powell Aug 3 '16 at 8:10
  • Thanks for your response. I changed the code like this: cur.execute ("INSERT INTO gronkarta.test5 (gid, height, geom) VALUES (gid, str(height), ST_GeomFromText('POINT(X Y)', 3008))") Error: There is a column named "gid" in table "test5", but it cannot be referenced from this part of the query. – Mozafar Veysipanah Aug 3 '16 at 8:25
  • is gid an autonumber column? If so, then just leave that column out and let the database assign the id: cur.execute ("INSERT INTO gronkarta.test5 (height, geom) VALUES (str(height), ST_GeomFromText('POINT(X Y)', 3008))") – kttii Aug 3 '16 at 12:42
4

Something more like this would be better, IMO. Creating the connection is more expensive, you should do that earlier in the program and pass it in. As noted by a commenter, you shouldn't compose SQL strings using string concatenation, you should use placeholders for more secure use of the database.

def db_insert_point(conn, height, x, y):
    sql = "INSERT INTO gronkarta.test5 (height, geom) VALUES (%s, ST_SetSRID(ST_MakePoint(%s, %s), 3008))"
    with conn.cursor() as cur:
        cur.execute(sql, (height, x, y))
    conn.commit()

If you're running db_insert_point() in a tight loop, you'll want to call conn.commit() at the end of the loop, rather than once per insert, for performance purposes, so you could move the commit outside the function in that case, to get multiple inserts into a single transaction.

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.