0

I think this is rather a formatting issue. I have a database with a geometry Column that sometimes contains points. Normally, it will be None/Null.

When I format the SQL query in psychopg2 and python (for inserting entries), it looks like this:

insert_sql = '''
        INSERT INTO "post" (post_guid, post_latlng)
        VALUES (%s,ST_GeomFromText(%s,4326))
        ON CONFLICT (post_guid)
        DO UPDATE SET
            post_latlng = COALESCE(EXCLUDED.post_latlng, "post".post_latlng),                            
        '''
cursor.execute(insert_sql,(post_guid,photo_latlng))

However, this does not work if photo_latlng = None I tried:

if geoinformation_available:
    photo_latlng = "POINT(%s %s)" % (photo_longitude,photo_latitude) #WKTconversion
else:
    photo_latlng = None
if not photo_latlng == None:
    photo_latlng = "ST_GeomFromText(" + photo_latlng + ",4326)"    
insert_sql = '''
        INSERT INTO "post" (post_guid, post_latlng)
        VALUES (%s,%s)
        ON CONFLICT (post_guid)
        DO UPDATE SET
            post_latlng = COALESCE(EXCLUDED.post_latlng,"post".post_latlng)                            
        '''
cursor.execute(insert_sql,(post_guid,photo_latlng))

.. but it gives me a psycopg2.InternalError: parse error - invalid geometry (HINT: "ST" <-- parse error at position 2 within geometry). I also doubt that this will work with COALESCE -> only fill row if entry is not Null.

1

photo_latlng will never be null as is always contains at least "POINT( )". You need to validate that the coordinates are or not null. If they are null, you insert a null geometry, else, you construct the proper geometry.

newgeom = "NULL"
if not photo_longitude == None and  not photo_latitude== None:
   newgeom = " ST_SetSRID(ST_MakePoint(%s %s), 4326) " % (photo_longitude,photo_latitude) 

#no change to the sql
insert_sql = '''
        INSERT INTO "post" (post_guid, post_latlng)
        VALUES (%s,%s)
        ON CONFLICT (post_guid)
        DO UPDATE SET
            post_latlng = COALESCE(EXCLUDED.post_latlng,"post".post_latlng)                            
        '''
#use the new geometry/null geometry
cursor.execute(insert_sql,(post_guid,newgeom ))
  • Thank you JGH, this is even better - I was not aware that I could use ST_GeomFromText outside the SQL statement! – Alex Nov 28 '17 at 14:19
  • I am not using it, I am just writing the text that is embedded in the SQL. All the geometry construction takes place in the DB, not in the python code – JGH Nov 28 '17 at 14:21
  • I should have tested this first - this approach does not work JGH, I am getting the error psycopg2.InternalError: parse error - invalid geometry LINE 3: ...'ST_GeomFr... ^ HINT: "ST" <-- parse error at position 2 within geometry, perhaps because newgeom is submitted in string-formatting - see my answer below – Alex Nov 28 '17 at 14:27
  • could you print insert_sql % (post_guid,newgeom) just before executing the query? – JGH Nov 28 '17 at 14:43
  • This is the output: ...],ST_GeomFromText(POINT(-3.530175 39.474307) ,4326))... it looks all correct. I don't understand why there's an error. This gives me the same SQL_output string but without the error: if photo_latlng == None: geoconvertOrNone = "%s" else: geoconvertOrNone = "ST_GeomFromText(%s,4326)" (see my solution) – Alex Nov 28 '17 at 14:54
0

See above (better) answer from JGH. This was my solution (also works, but kind of ugly):

photo_latlng = "POINT(%s %s)" % (photo_longitude,photo_latitude) #WKTconversion
if photo_latlng == None:
    geometry = "%s"
else:    
    geometry = "ST_GeomFromText(%s,4326)" 
insert_sql = '''
        INSERT INTO "post" (post_guid, post_latlng)
        VALUES (%s,''' + geometry + ''')
        ON CONFLICT (post_guid)
        DO UPDATE SET
            post_latlng = COALESCE(EXCLUDED.post_latlng, "post".post_latlng),                            
        '''
cursor.execute(insert_sql,(post_guid,photo_latlng))

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.