Insert a waypoint into a POSTGIS table with psycopg2 and ST_GeographyFromText

Question

I am trying to insert a waypoint into a table with 1 foreign key refererance and a geography point in PostGIS. I am having no luck.

The table structure is

CREATE TABLE route
                (id serial PRIMARY KEY,
                route_name varchar);

CREATE TABLE planned_waypoints
                (id serial PRIMARY KEY,
                route_id serial REFERENCES route(id),
                timestamp timestamp default current_timestamp,
                planned_waypoint geography(Point, 4326));

First to insert the route I use

cur.execute('INSERT INTO route (route_name) VALUES (%s)  RETURNING id;', (r_name,))
r_name_id = cur.fetchone()[0] 

to insert the waypoint, I have tried

cur.execute("""INSERT INTO planned_waypoints (route_id, planned_waypoint) VALUES (%s, ST_GeographyFromText(POINT(%s %s)) );""", (r_name_id, lat, long, )

However this returns an error message.

psycopg2.errors.SyntaxError: syntax error at or near "11.197973099081285"
LINE 1: ...(62, ST_GeographyFromText(POINT(48.07093992348414 11.1979730...

I have tried various approaches, including using Python string formatting (it failed), using a dictionary for the variables and so forth. None of these approaches have yielded a successful insert.

For example:

cur.execute("""INSERT INTO planned_waypoints (route_id, planned_waypoint) VALUES (%s, %s ) ;""", (r_name_id, ST_GeographyFromText(POINT(lat long)), ))

which produces:

cur.execute("""INSERT INTO planned_waypoints (route_id, planned_waypoint) VALUES (%s, %s ) ;""", (r_name_id, ST_GeographyFromText(POINT(lat long)), ))
                                                                                                                                                ^
SyntaxError: invalid syntax

I'm pretty new to sql and psycopg2.

results of

and

print(cur.mogrify("""INSERT INTO planned_waypoints (route_id, planned_waypoint) VALUES (%s, %s ) ;""",r_name_id, ST_GeographyFromText('POINT(lat long)'),  ).decode())

produces:

NameError: name 'ST_GeographyFromText' is not defined 



print(cur.mogrify("""INSERT INTO planned_waypoints (route_id, planned_waypoint) VALUES (%s, %s ) ;""",r_name_id, ST_GeographyFromText(POINT(lat long)),  ).decode())
                                                                                                                                                    ^
SyntaxError: invalid syntax

with a cursor pointing to long

Solution, as proposed by @swiss_knight’s answer,

from psycopg2 import sql
    sql_qry = sql.SQL("""INSERT INTO planned_waypoints
    (route_id, planned_waypoint)
    VALUES ({}, ST_GeographyFromText('POINT({} {})')));"""
).format(
    sql.Literal(r_name_id),
    sql.Literal(lat),
    sql.Literal(long)
)
    print(sql_qry.as_string(conn))

produces:

INSERT INTO planned_waypoints
    (route_id, planned_waypoint)
    VALUES (111, ST_GeographyFromText('POINT(48.07059854740867 11.198530422437894)'));

Which looks like it should look.

Answer 1

You need to enclose your Point into single quotes:

SELECT ST_GeographyFromText('POINT(48.07093 11.19797)')

->

vs

SELECT ST_GeographyFromText(POINT(48.07093 11.19797))

ERROR:  syntax error at or near "11.19797"
LINE 1: SELECT ST_GeographyFromText(POINT(48.07093 11.19797))
                                                   ^
SQL state: 42601
Character: 44

In addition, you cannot do it this way:

print(cur.mogrify(
    """INSERT INTO planned_waypoints
    (route_id, planned_waypoint)
    VALUES (%s, %s );
    """,
    r_name_id,
    ST_GeographyFromText(POINT(lat long))
).decode())

Try it like that:

from psycopg2 import sql
sql_qry = sql.SQL("""INSERT INTO planned_waypoints
    (route_id, planned_waypoint)
    VALUES ({}, ST_GeographyFromText('POINT({} {})')));"""
).format(
    sql.Literal(r_name_id),
    sql.Literal(lat),
    sql.Literal(long)
)
print(sql_qry.as_string(conn))

Which should print a valid SQL query:

INSERT INTO planned_waypoints (route_id, planned_waypoint)
VALUES (1, ST_GeographyFromText('POINT(48.7777 11.22222)')));

See: https://www.psycopg.org/docs/sql.html