[GIS] Inserting points into SQL Server using pymssql

arcgis-10.1arcpyspatial-databasesql server

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.

Best Answer

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