PostGIS & Python SQL Query formatting: Create Geometry only for entries that are not NULL

Question

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.

Answer 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 ))

• Alex: Thank you JGH, this is even better - I was not aware that I could use ST_GeomFromText outside the SQL statement!
• JGH: 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
• Alex: 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
• JGH: could you print insert_sql % (post_guid,newgeom) just before executing the query?
• Alex: 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)

Answer 2

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))