Inserting points into SQL Server using pymssql?

Question

I am collecting X and Y values from a web service (Twitter) via a python script. In the long run, this will run over a period of months and I intend on stopping at around the 6 million point mark.

The original coords im getting are geographic WGS84, but I will need to convert these to projected WGS Web Mercator. Ill be later publishing this table to an ArcGIS Server map service and caching it.

This is a personal project to learn python with no deadline and was wondering if it would be a good idea to solely make use of the native spatial types from SQL Server?

My current untested plan:

• CREATE a table with SSMS, with a GEOMETRY field setup (and some other attributes)
• In my python script, make use of arcpy or pyproj to convert the lat/lons in WGS84 to WGS84 Web Mercator (or can I avoid this somehow and its all achievable with SQL?)
• Make use of pymssql to INSERT records, and insert the points into the GEOMETRY field in the table.

My question is, what would be a good, simple and efficient approach to take a pair of lat/lons in WGS84, and then insert them into a SQL Server table making use of SQL Server spatial types and have a resulting points layer that is in WGS84 Web Mercator, so that I can render/query them in ArcGIS Desktop 10.1?

I do have access to arcpy/ArcSDE 10.1 if need be but was hoping to use this as an example of not requiring ArcSDE.

• simpleuser001: use pyproj data first then insert data into MS SQL database. geometry::STPointFromText('POINT(' +p.longitude + ' ' + p.latitude + ')' , 4326) will create WKT for it
• simpleuser001: And i would use PostGIS if you can
• jakc: Thanks, but please can you put this into an answer and elaborate on why you would use PostGIS as well pls.

Answer 1

I went ahead with my plan, as stated in Question.

For the purpose of inserting points into SQL Server, this post was very useful for me.

Here is what worked for me:

import pymssql
# connect to SQL Server instance
conn = pymssql.connect(host='localhost', user='sa', password='sa', database='nosde')
# commits every transaction automatically and setup cursor
conn.autocommit(True)
cur = conn.cursor()
# !!Chunk of code stripped out on how I get my coords, unrelated to Q.
# Store projected coords in a GEOMETRY type field
geom_type = "geometry::STPointFromText('POINT(%s %s)', 3857)" % (x, y)
 try:
 cur.execute("INSERT INTO tweets (geom) VALUES (%s)" % (geom_type))
 except TypeError:
 print "Could not INSERT", clean
 conn.close()

• Peter Horsbøll Møller: How did you manage to transform your coordinates from geographic WGS84 to Web Mercator? In the above it looks as if you have done so
• jakc: look at pyproj library. works a treat! ASk another Q if u want specifics.

Answer 2

I'm not sure of your entire workflow requirements, but if you have access to arcpy, then you could use arcpy.ConvertCoordinateNotation_management to take your SQL table of points and convert them into a point feature class at whatever projection you need it in. No need for SQL Server spatial types or ArcSDE.

• jakc: Possible solution. However, I was thinking that if I am collecting 6M points, then running this tool at the end could take awhile. Was assuming I could do it on the fly, that way I can actually view the points whilst the script is still collecting.

Answer 3

I assume that you have one or several big files filled with x y and some other data. First, to my knowledge ther is no projection support in MS SQL (2008 r2 or later). there is third party solutions and proj.net library which you can use to build one. Therefore i see two options when storing data to database, if using MS SQL , you need to reproject data into wanted projection before inserting database or you just dump data into PostGIS db and do transform there. PostGIS has much more better toolset in database than MS SQL

Answer 4

geoAlchemy is supposed to do the job using GeometryColumns. However, I was not able to make it work on Windows/Python 2.7/sqlalchemy 0.9.6 due to AttributeError: type object 'ColumnProperty' has no attribute 'ColumnComparator'