[GIS] Migrate datatypes from geometry to geography in MS SQL

arccatalogarcgis-10.2enterprise-geodatabasesql server

I have three ESRI feature classes (POINT, LINE and POLYGON) that contain a geometry datatype column with data in a projected coordinate system. I'm looking for a way that I can convert & project from Geometry datatype to Geography.

Does anyone know of a tool or script I can use to convert all records permanently?

My research has show that I will not be able to do this with a simple SQL statement. So i'll probably need to use an Arc Toolbox or Python script or something.

Best Answer

You could always do the conversion on the fly by casting the geometry object to a geography object. For example:

SELECT geography::STGeomFromText(SHAPE.ToString(),4326) FROM featureclass;

Related Solutions

[GIS] Using MS SQL 2005 as the data store in GeoServer

Well, I have read the article again and I think I finally understand. Indeed, converting the points from WKB to WKT is necessary, as in the former case GeoServer will ignore the varbinary(MAX) column. In the latter case, GeoServer will publish the point - marked in WKT - as an attribute (metadata) of type String. Therefore, when requesting the WFS service, the point coordinates are really present in the returned XML, they are just not marked as <gml:point>. It is still possible to parse them out manually, if one wishes to.

However, it is probably not possible to persuade GeoServer to recognize that this column is a geometry and not some metadata. Therefore, technologies such as OpenLayers which do expect to find a <gml:point> tag in the returned XML will not work with this.

[GIS] Inserting points into SQL Server using pymssql

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

Best Answer

Related Solutions

[GIS] Using MS SQL 2005 as the data store in GeoServer

[GIS] Inserting points into SQL Server using pymssql

Related Question