0

I'm downloading a building set from openstreetmap using osmnx into a geopandas dataframe and then attempting to write this dataframe to sql server. This question has been asked and answered but the code from the answer seems to not be working for me. The original question can be found here:

Geopandas export to sql server

It uses SQLAlchemy to convert the geometry field to WKT and then attempts to convert it to native sql geometry during export. I'm trying to implement the second set of code on this page but run into trouble with the last line df.to_sql. I get a "name 'geom' is not defined" error. The field exists in the dataframe. I re-named it earlier in the code to avoid it being a SQL Server keyword. I'm not sure what I'm doing wrong but here's my code. Why is geom not recognized?

from geoalchemy2 import Geometry, WKTElement
from sqlalchemy import *
import geopandas as gpd
import osmnx as ox

# get the building set
bldg = ox.footprints.footprints_from_place(place = 'Potsdam, NY, USA')
ox.plot_shape(ox.project_gdf(bldg))                                           
bldg = bldg.rename(columns={'geometry': 'geom'}).set_geometry('geom')

# Use GeoAlchemy's WKTElement to create a geom with SRID
def create_wkt_element(geom):
    return WKTElement(geom.wkt, srid = 4326)

bldg['geom'] = bldg['geom'].apply(create_wkt_element)

# iterating the columns to be sure that geom exists in the df
for col in bldg.columns: 
    print(col) 


db_url = 'mssql+pyodbc://MyServerName/NyDatabaseName?driver=SQL Server Native Client 11.0'
engine = create_engine(db_url, echo=False)

# Use 'dtype' to specify column's type
# For the geom column, we will use GeoAlchemy's type 'Geometry'
bldg.to_sql('buildingsTest', engine, schema = 'mySchemaName', if_exists='append', index=False
            , dtype={geom:Geometry('POLYGON', 4326)})

Here's the error message

NameError Traceback (most recent call last) in 32 bldg.to_sql('buildingsTest', engine, schema = 'mySchema', if_exists='append', index=False ---> 33 , dtype={geom:Geometry('POLYGON', 4326)})

NameError: name 'geom' is not defined

jport
  • 170
  • 12
  • 1
    `{geom:` should likely be `{'geom':`, in other words the key is the string "geom". – Ilja Everilä Nov 16 '19 at 17:18
  • quoting it generates a longer error string including. ProgrammingError: ('Invalid parameter type. param-index=1 param-type=_FunctionGenerator', 'HY105') – jport Nov 16 '19 at 17:34
  • You should then proceed to fix those as well. – Ilja Everilä Nov 16 '19 at 17:39
  • I guess I've got bigger problems then quotes. the original post didn't have them. I dropped the first column because that was the issue and the errors just keep coming – jport Nov 16 '19 at 18:59
  • It does generate a create table command but does so with an error: CREATE TABLE [Schema].[buildingsTest] ( geom geometry(POLYGON,4326) NULL, I assume it should be geom geometry() NULL but my code is not generating that. – jport Nov 16 '19 at 19:08

0 Answers0