Archive for July, 2014

Creating a Polygon Geometry value for SQL Server

Who doesn’t love messing with geometries, latitudes, longitudes, and omelets? Because of the vast numbers of people who love those things, this post if for you.

I needed a geometry for Hong Kong. I had been working with POINTs, but not POLYGONs. The big difference is that you use STGeomFromText instead of STPointFromText. So once you have all the longitude / latitude points you need, you build the final string like so:

SELECT geometry::STGeomFromText(‘POLYGON((113.864 22.472, 113.951 22.518, 114.037 22.504, 114.225 22.547, 114.307 22.591, 114.384 22.606, 114.460 22.547, 114.491 22.452, 114.478 22.153, 113.912 22.146, 113.816 22.216, 113.864 22.472))’, 4326)

In this instance, I am using the EPSG coordinate system, thus the “4326” at the second parameter of the call to STGeomFromText. You can then use the Geometry type returned to update your database:

UPDATE CountryDataSET Geom= 0xE610000001040C0000009EEFA7C64B775C4046B6F… WHERE CountryName = ‘Hong Kong’

And no, there is no “…” in the geometry, but those strings get pretty big. Thus, truncation.

Leave a comment