0

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.

1 Answer 1

0

You need to enclose your Point into single quotes:

SELECT ST_GeographyFromText('POINT(48.07093 11.19797)')

-> enter image description here

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

0

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.