Performing SQL Server Geometry to geography conversion?

Question

I have imported a number of Shapefiles from the ordnance survey using shape2SQL

These have been imported as geometry however i would like to convert them to Geography and specifically to srid 4326

the shape when imported contains a number of polygons

ultimately i'm trying to ascertain if a number of lat/long coordinates i hold fall within one of these polygons (so i suppose i also need to alter the coordinates to geog (which i can see has been answered before))

finally the plan is to display this on an SSRS report or onto a Bing Map display (hence why at this stage i believe i require SRID 4326)

How do I import the shape as SRID 4326?

Answer 1

You cannot do that.

Geometry and Geography are two completely different types of data. You probably did read up on it..., but here is an explanation...

Geometry are points on a flat surface. If you would like to calculate the area of your bedroom, i.e. 3.5m by 6.8m, it would result in 23.8m2 - if you would have drawn it in a CAD program, you would start from 0,0 and draw a rectangle to 3.5 in width, and 6.8 length, which gives you the area of the flat surface, on a Z elevation of 0

When you get to Geography, we are talking about the way the earth or sphere are measured. Yes, the earth is round, but not completely round as a ball. It actually looks like a potato mixed with a watermelon. So, when you draw lines on the earth's surface, they are not flat. For the first couple of meters, its "ok" to measure a flat line, but after that you have to consider using calculations and Coordinate systems (i.e. WGS84, a.k.a. 4326 SRID)

Explanation: http://en.wikipedia.org/wiki/Earth_radius | http://en.wikipedia.org/wiki/Horizon

When you store something in Geometry, they are normally X,Y,Z, and when you store something in Geography they are normally Latitude, Longitude, Elevation. Also to note is, when you store in Geometry it is usually in units of measure, i.e. Meter. When storing in Geography is is usually in which datum the data reside, for example a province or state in America would not the be the same area if measured in Australia. The earth's curve is not the same, and you would sit with errors in your calculations. So, the idea was to use WGS84 as the major measuring unit across the world, but that has led to many errors locally on the ground for some surveyors. Measuring with a theodolite instrument, you would calculate the correct area of a surveyed place to the accuracy of less than 0.002m - which is acceptable. Imagine measuring an area of a place by only having 3 decimal digits after a lat/lon.

shape2SQL has a selection where you can choose between the two. BUT, you cannot compare the two against each other...

Answer 2

Disclaimer: this can be wrong, but... Once you have geography or geometry in srid 4326, you change change between those two systems in SQL Server without problems. Only thing where geometry vs. geography affects is calculations, geometry works on plane and georaphy works on ellipsoid, so geography type is preferred.

So Assuming that you have shp file in srid 4326. Use shape2SQL to import data into SQL server, then explode geom to text and input it to script which casts it to geograpghy (geography::ST_AstexT(geom.ST_AsText()) ? ), (simple "update datatable set geog = convert(geography, geom) " dosen't work)

If you data isn't allready in srid 4326, then you need better tools than MS SQL server for you data transformations

Answer 3

I started down a similar path with Shape2SQL, but now I am using the Natural Earth shape files - they are great and cover all countries down to an incredible level of detail (railroads!):

http://www.naturalearthdata.com

Laurent Dupuis has shared his amazing project which shreds ALL of the natural earth data (including all the dbf attributes) straight from the zip file you download, into SQL tables (that his code creates), including a common table with a Geography datatype column.

http://www.dupuis.me/node/32

I was able to start using this with minimal effort - I just added a few indexes for performance. He is a genius!

For what you are trying to do I suggest joining to one of the tables for admin states provinces (e.g. ne_10m_admin_1_states_provinces_shp). Follow Laurent's SQL example (substituting your chosen table and probably filtering for admin = 'United Kingdom' and your preferred shape type), and you are off and running.

Answer 4

If there are not so many shape-files, you can try to get geography "manually", with brigantine