TransWikia.com

Passing dynamic query parameters to an SQL query string in GeoPandas read_postgis() method

Geographic Information Systems Asked on May 31, 2021

Up to now, I used to query geospatial tables with psycopg2 and then convert the geometries to my needs using, e.g. Shapely.

Per the psycopg2 documentation, you never format your query string yourself. Instead, you let the .execute() method of your cursor doing the actual job.

import psycopg2
sql_query = ("SELECT * FROM table WHERE id = %(id)s;")

with psycopg2.connect(**params) as conn:
  with conn.cursor() as curs:
    curs.execute(sql_query, {'id': id})
    res = curs.fetchall()

Now, I’d like to use .read_postgis() of GeoPandas to make an equivalent clean and secure call to my database.

But this method is a little bit skinny regarding the query string:

GeoPandas' query string

Is there a way to dynamically pass a dictionary of parameters to a call of GeoPandas ‘ .read_postgis() method? Maybe relying on the same mechanism that psycopg2 is using?

For the moment, my workaround is to take advantage from the psycopg2 way of doing by "mogrifying" the execution:

psycopg2 mogrify

as such:

import geopandas as gpd
gdf = gpd.read_postgis(
        cursRemote.mogrify(sql_query, {'id': id}), # <- passing this guy instead of my raw query
        connector, geom_col='mygeometrycolumn'
    )

I would be glad to know if it exists a direct GeoPandas’ way?

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP