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.