How to input geospatial data into MS SQL Server via Python and SQLAlchemy

Question

I'm trying to add a GeoJSON file into the SQL Server database. I have this following script that works fine for inputting the geospatial data into MYSQL Database, thus creating a GEOMETRY type column in the DB.

import geopandas as gpd
import pymysql
import pymssql
from sqlalchemy import *
from sqlalchemy import func
from sqlalchemy.types import UserDefinedType

class Geometry(UserDefinedType):
  def get_col_spec(self):
      return "GEOMETRY"

  def bind_expression(self, bindvalue):
      return func.ST_GeomFromText(bindvalue, type_=self)

  def column_expression(self, col):
      return func.ST_AsText(col, type_=self)

geodataframe = gpd.read_file(r"my_geojson_file.geojson")

DATABASE_URL_SQLSERVER = 'mssql+pymssql://username:password@<server>.database.windows.net/database?charset=utf8'
DATABASE_URL_MYSQL = 'mysql+pymysql://username:password@server/database?charset=utf8'

engine = create_engine(DATABASE_URL_MYSQL)

geodataframe.to_sql('my_new_geom_table', engine, if_exists='replace', index=False,dtype={'geometry':Geometry})

However, when I follow this same structure to add it to sqlserver database (I used the variable DATABASE_URL_SQLSERVER instead of DATABASE_URL_MYSQL [see code above] containing the string connection), It gives me the following error:

File "C:\Users\matheus.barros\Anaconda3\envs\geo_env\lib\site-packages\sqlalchemy\engine\default.py", line 689, in do_executemany
    cursor.executemany(statement, parameters)
  File "src\pymssql.pyx", line 476, in pymssql.Cursor.executemany
  File "src\pymssql.pyx", line 450, in pymssql.Cursor.execute
  File "src\_mssql.pyx", line 1070, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1101, in _mssql.MSSQLConnection.execute_query
  File "src\_mssql.pyx", line 1218, in _mssql.MSSQLConnection.format_and_run_query
  File "src\_mssql.pyx", line 1240, in _mssql.MSSQLConnection.format_sql_command
  File "src\_mssql.pyx", line 1879, in _mssql._substitute_params
  File "src\_mssql.pyx", line 1856, in _mssql._quote_data
  File "src\_mssql.pyx", line 1832, in _mssql._quote_or_flatten
ValueError: expected a simple type, a tuple or a list

Someone has a hint in how to overcome that?

Answer 1

My best guess is that your bind_expression method does not work correctly as you need another function in MSSQL and you are not able to use func. here since "::" is in the function name needed in MSSQL (see also the discussion here).

The following geometry class definition worked for me:

import geopandas as gpd
import sqlalchemy as sal

# custom sqlalchemy usertype to convert wkt-column to geometry type
class Geometry(sal.types.UserDefinedType):
    
    def __init__(self, srid: int = 4326):
        self.srid = srid

    def get_col_spec(self):
        return "GEOMETRY"

    def bind_expression(self, bindvalue):
        return sal.text(f'geometry::STGeomFromText(:{bindvalue.key},{self.srid})').bindparams(bindvalue)


# connect to and write gdf to database
con = engine.connect()
gdf.to_sql(name='NEW_TABLE',
           #if_exists='append',
           index=False,
           dtype={'geom': Geometry(srid=crs_input)},
           con=con,
           schema = 'dbo')
con.close()

Answer 2

The issue is because Geometry is not an instantiable data type in SQL server - see the docs

Regarding a solution (and sorry for the edit, I fat fingered send), assuming you want to use your own classes, your best bet is likely to make a class for all the relevant types - Point, LineString, etc. Also, since I'm not an expert on SQL server, I'd double check to make sure the ST_xx functions exist on SQL server.

You could probably make everything a GeometryCollection to avoid having to make a bunch of classes, but I'm sure you know this brings a menagerie of potential pitfalls when, eg, converting to WKT, performing geometric operations, and really doing anything with a sizable data set.