[GIS] Insert XY table data into SQL Spatial column

sql serversqlserver.types

ArcGIS Server 10.1.1 SQL Server Standard 2012
Current workflow is user submits a table (text file) containing lat-long data (same spatial reference each time); SQL Server Integration services pushes this data into an SQL table. Data is viewed as an XY Event Layer in ArcGIS Desktop and published to AGS.

The ideal workflow is to to utilize a Query Layer instead of a XY Event Layer (according to ESRI).

My question is what is the best to get these XY (Lat-Long) fields into the spatial column?

At this point in the workflow, I think we would be limited to SQL commands (or something within SSIS); the table is a text file going into SSIS so I doubt we'd be able to add an appropriately formatted column/field which would could then be mapped/loaded into the spatial column of the target SQL table.

[The question is much less about ArcGIS Server but I added that background for context.]

Best Answer

The answer to this was to simply create a computed column using the Geometry type in SQL. The computed text in this case was: ([geometry]::STGeomFromText(((('POINT('+CONVERT(varchar,[Long]))+' ')+CONVERT(varchar,[Lat]))+')',(4326)))

Now the table can be added as a query layer within ArcMap. Works like a charm.

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

There seems to be a lack of a clear overview on the OGR Virtual Format help page. The phrase it can "provide an anchor file for access to non-file oriented datasources" seems to imply any connection type can be used, but I'm not sure this is the case.

Its original function was to allow spatial data to be created from non-spatial sources, for example by combining an X and Y column in a database table to create point features.

However there is no reason why a spatial database can't also be used for this purpose, but you should then use a standard ODBC connection rather than the MSSQL driver.

To do this create a DSN (Data Source Name) in Windows. Then use the ODBC connection and the DSN in the virtual layer configuration. So if your DSN is named OSVectorMap:

<OGRVRTDataSource>
  <OGRVRTLayer name="AASQLlayer">
    <SrcDataSource>ODBC:username/password@OSVectorMap</SrcDataSource>
    <SrcSQL>SELECT geom27700.STAsText() AS geomWKT FROM TG11_Settlement_Area</SrcSQL>
    <GeometryField encoding="WKT" field="geomWKT"/>
    <LayerSRS>EPSG:4326</LayerSRS>
    <GeometryType>wkbPolygon</GeometryType>
  </OGRVRTLayer>
</OGRVRTDataSource>

There are a few more details on creation of Virtual Layers in the MapServer wiki and Virtual Spatial Data page.

Great blog post by the way. I wouldn't worry too much about using Python 3.x - nearly all the useful GIS libraries are still in 2.x And the SQL Server Geometry and Geography types are great. The only downsides are that SQL Server unfortunately seems a second class citizen in the opensource world in terms of blog posts, and integration with other tools.

[GIS] Create a geography/geometry column from x and y fields – SQL Server 2008

You can add a computed column like this

alter table yourTable add geographyColumn as geography::STGeomFromText('POINT('+convert(varchar(20),Long)+' '+convert(varchar(20),Lat)+')',4326)

I have add the conversion from lng or lat because I store the Long and Lats as numbers.

Best Answer

Related Solutions

[GIS] Can Mapnik render spatial data from SQL Server

[GIS] Create a geography/geometry column from x and y fields – SQL Server 2008

Related Question