Insert points into SQL Server?

Question

Brand new to python and loving it, and I imagine this might be a simple one.
I am currently inserting points into SQL Server 2008 via a Python script with the help of pymssql.

var1 = "hi"
lat = "55.92"
lon = "-3.29"

cursor.execute("INSERT INTO table (field1, x, y) VALUES(%s, %s, %s)", 
(var1 , lat, lon))

This all works fine. I need to also insert those coordinates into a GEOGRAPHY type field (called geog).

geog_type = "geography::STGeomFromText('POINT(%s %s)',4326))" % (lat, lon)

cursor.execute("INSERT INTO table (field1, x, y, geog) VALUES(%s, %s, %s, %s)", 
(var1 , lat, lon, geog_type))

This throws the following exception:

The label geography::STGeomFro in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON and FULLGLOBE (geography Data Type only).

From SSMS I can run an insert statement on the table to insert a point fine.

USE [nosde]
INSERT INTO tweets (geog) 
    VALUES(
        geography::STGeomFromText(
        'POINT(55.9271035250276 -3.29431266523898)',4326))

Let me know in the comments if you need more details.

Some of my workings on pastebin.

Is that the full error message? If so, is geog_type getting truncated in the executed SQL, since the error is complaining about "geography::STGeomFro", rather than "geography::STGeomFromText" — Talvalin, Nov 15 '12 at 05:35
I stepped through, and the string is correct, so not sure if its getting truncated elsewhere. http://pastebin.com/D5twLB5h — jakc, Nov 15 '12 at 08:46

score 2 · Accepted Answer · answered Nov 15 '12 at 11:53

Several issues - firstly, you're supplying the coordinates in the wrong order - the STPointFromText() method expects longitude first, then latitude.

Secondly, it may be easier to use the Point() method rather than the STPointFromText() method, which doesn't require any string manipulation - just supply the two numeric coordinate parameters directly. http://technet.microsoft.com/en-us/library/bb933811.aspx

But, from the error message, it appears that the value you're sending is attempting to be parsed as a WKT string. If this is the case, you don't want the extra geography::STGeomFromText and the SRID at the end anyway - these are assumed. So try just supplying:

geog_type = "'POINT(%s %s)'" % (lon, lat)

cursor.execute("INSERT INTO table (field1, x, y, geog) VALUES(%s, %s, %s, %s)", 
(var1 , lat, lon, geog_type))

I'm not sure if you need the extra single quotes in the first line or not, but don't have a system to test on at the moment.

Insert points into SQL Server?

1 Answers1