pyodbc insert spatial data - geometry or geography

[lar...@gmail.com]

Hi

I am trying to insert spatial data into SQL Server 2008 R2 using pyodbc, unfortunately I cannot get it to work, see code below.

When testing using SSMS everything works.

I think it is how I am treating the ' and ". I can connect, insert normal data even do geometry (ST_Intersect) selections.

 

The message returned is as follows:

Message File Name Line Position 

Traceback    

    <module> <module2> 12  

"ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate ""geometry"": \r\nSystem.FormatException: 24114: The label geometry::STGeomFrom in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.\r\nSystem.FormatException: \r\n   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)\r\n   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)\r\n   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)\r\n   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)\r\n. (6522) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)')"    

 

 

I have also tried the following for my variable:

            val3 = "geography::STGeomFromText('Point(151.175896549397 -33.8997910998778)',4326)"

Note the geometry/geography change, I have been flipping between each to see what does and does not work.

 

 

Has anyone successfully inserted geometry or geography data into SQL Server using pyodbc?

Is yes, can you post or send me some basic code please.

 

Any help would be greatly appreciated.

Lachlan

 

The basic code is as follows:

 

import pyodbc

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=XXXX;DATABASE=XXXX;UID=XXXX;PWD=XXXX')

cursor = cnxn.cursor()

val3 = 'geometry::Point(151.175896549397,-33.8997910998778,0)'

cursor.execute("INSERT INTO dbo.XXXX (GeomColXY) VALUES (?)",val3)

cnxn.commit()

cursor.close()

del cursor

cnxn.close()

 

[std...@utah.gov]

Did you ever get this working? I'm having the same issue.

[lar...@gmail.com]

Hi

No, I ended up writing a stored procedure and passing the vaues in:

        cursor.execute("{call dbo.sp_SpatialInsert(?,?,?,?)}", BufferDistance, GeomColXY, SeqNumber, GMLType)

 

The GeomColXY has the coords list.

[oneextra...@gmail.com]

Hi - you can do it with pyodbc, at least for a Point, the syntax is a string with format (Object Longitude Latitude SrsId)

'POINT( -73.4775101 40.8078824 4326)'