I have a mapping website used to capture polygons using OpenLayers in Google projection (900913) and storing them in SQL server 2008 as geometry. The SRID on the SQL geometry column is set to 900913. I don't reproject from the captured data, it's just sent to the server as WKT format from the features in OpenLayers and inserted into a geometry column using geometry::STGeomFromText().
When I call STArea() on the geometry I get hugely inaccurate results. For example, we have a gridded ordnance survey tile service that projects to 900913. If I trace a polygon as a 1km grid square from this background image and save it, the coordinates of the square in 900913 come out as around 1660m apart instead of 1000m and the total area is 2.7km2 instead of 1km2. This is baffling. The units of the Google projection are supposed to be in metres but metres of what?
If I import the geometry into Quantum GIS, I can calculate the area using the measure area tool. If I have the 'Ellipsoidal' checkbox on, I get the right calculation (1km2 in example above) and with ellipsoidal off, I get the inaccurate result (2.7km2).
There's a big gap in my knowledge here, but it looks like, despite the fact Google is a projected coordinate system, you can't use geometry methods for distance and area. You have to take into account the ellipsoid/sphere to get anything accurate. Is this true? Are there any good resources to find out more about this (I've seen the discussion on Alistair Aitchison's blog but there's quite a bit of assumed knowledge that I don't have towards the end)
The polygon capture on the OpenLayers map needs to be in 900913 so I can use Bing background mapping etc. So should I re-project before saving to sql server? If so, what would be appropriate (the extent of the mapping is UK which is both OSGB and OSI grids)?
In short, how should polygons captured in Google projection be stored if you want accurate area and distance calculations?
Best Answer
You have to enable the geodesic option on the OpenLayers measure control to get correct measurements.
This discussion here should help you understand what is going on (tip: the Earth is not flat as Google/Bing Maps shows it ;-) ):
Using Geodesic Measurements for Circle Radii?
Make sure you read the comments, in particular this one.