How to pass parameters to PostGIS queries containing geography type in Python using psycopg2

Question

I have the following example (adapted from the PostGIS manual (version 2.2):

CREATE TABLE global_points ( 
    id SERIAL PRIMARY KEY,
    name VARCHAR(64),
    location GEOGRAPHY(POINT,4326)
  );

INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );
INSERT ..........
c = conn.cursor()
try:
  query ="SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);"
  c.execute(query)
  l = c.fetchall()

................. How shall I modify the query to allow for specifiyng the variables lon and lat in POINT in order to be able to iterate over a set of geographic locations?

Answer 1

Here are the basic usage patterns for inserting values with parameters with psycopg2:

http://initd.org/psycopg/docs/usage.html

For example:

data = 'value'
sql_insert = """INSERT INTO testtable (num) VALUES (%s);"""
cur.execute(sql_insert, data)

So where you want to insert LAT and LON, you need to create two variables and then two parameters to the insert query, then where you execute the query, pass in the variables holding the lat and lon values.

And we do this using this pattern:

addressid = row[0] #from the cursor
sql = 'insert into dqmt.newaddresses (addressid, lat, lon) values (%s, %s, %s);'
data = (addressid, result.latitude, result.longitude) #result is from geopy 
curDQMT.execute(sql, data)

Answer 2

Assuming you will be defining long and lat as python variables - this should work...

c.execute('''SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(%s, %s)'), 1000000);''',(long,lat))