How to refer to 2 column names in python?-CodePudding

I'm trying to retrieve a database with geometry information from a SQL server in Python using sqlalchemy and geopandas. There are 2 columns in geometry format and I want to retain both. The code I'm using is

import geopandas as gpd
from sqlalchemy import create_engine

db_connection_url = "postgresql://username:password@host:5432/database"
con = create_engine(db_connection_url)  
sql = 'SELECT osm_id, way, tags, way_centroid FROM osm.bldg WHERE height IS NOT NULL;'
df = gpd.read_postgis(sql, con, geom_col='way')

I want to achieve something similar in R which is geom_col = c('way', 'way_centroid') for the functiongpd.real_postgis, but I'm aware that it's not going to work this way in python. How can I achieve this in python?

CodePudding user response：

I somehow managed to modify the sql query to change the geometry to WKT before I fetch the data in python using psycopg2 instead...

import psycopg2
import pandas as pd

connection = psycopg2.connect(user="username",
                                  password="password",
                                  host="host",
                                  port="5432",
                                  database="database")
cursor = connection.cursor()
postgreSQL_select_Query = 'SELECT osm_id, ST_ASTEXT(way), tags, ST_ASTEXT(way_centroid) FROM osm.bldg WHERE height IS NOT NULL;'

cursor.execute(postgreSQL_select_Query)
print("Selected rows from database")
records = cursor.fetchall()

df = pd.DataFrame(records)
df.columns=['osm_id', 'way', 'tags', 'way_centroid']
df

CodePudding user response：

Not a direct way to store two geometries, but you can directly convert centroid coordinates to floats using the st_x and st_y geography functions:

db_connection_url = "postgresql://username:password@host:5432/database"
con = create_engine(db_connection_url)  

sql = '''
SELECT (
    osm_id,
    tags,
    st_x(way_centroid) as centroid_x,
    st_y(way_centroid) as centroid_y,
    way
)
FROM osm.bldg
WHERE height IS NOT NULL;
'''

df = gpd.read_postgis(sql, con, geom_col='way')