Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

Make use of comments if you need more information and I will edit it into my Q.

share|improve this question
    
use pyproj data first then insert data into MS SQL database. geometry::STPointFromText('POINT(' +p.longitude + ' ' + p.latitude + ')' , 4326) will create WKT for it –  simplexio Nov 13 '12 at 14:14
    
And i would use PostGIS if you can –  simplexio Nov 13 '12 at 14:16
    
Thanks, but please can you put this into an answer and elaborate on why you would use PostGIS as well pls. –  Simon Nov 14 '12 at 7:19
add comment

3 Answers 3

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.

share|improve this answer
    
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. –  Simon Nov 13 '12 at 22:31
add comment
up vote 2 down vote accepted

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.

Im still taking baby steps with Python, but here is what worked for me. Hope it helps others.

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()
share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.