2

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?

2 Answers 2

0

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.

0

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.