Answered by:
Conversions numeric for spatial data
Question

Hi All,
The code that I am reviewing test whether a certain point belongs in a specific polygon. It should also return polygons that is within a certain range of the point.
I am passing in feet value. In the code it translate the feet into metre ( I am assuming. That is the unit said in the sys.spatial_reference_systems for SRID 4326).
What confuses me is the feet value that is pass in is multiply by 0.00000272. So 750 feet is convert to 750 X 0.00000272. Does anyone know where this numeric transformation is derived from?
I have polygons loaded under SRID 4326 and I need to test a certain point. All polygons that is 750 feet from this point needs to be return. Any help is much appreciated.
thanks,Wednesday, September 30, 2009 3:53 PM
Answers

Ah  The geometry datatype explains everything!
When you're using the geometry datatype, the unit_of_measure of the spatial reference system doesn't apply. geometry is used for storing planar (x,y) coordinates, so the results of any calculations are given in the same unit of measurement as the coordinate values themselves are stated in.
Suppose you have a geometry coordinate at (0,0), and a coordinate at (3,4). The distance between them will always be 5 units, whatever unit of measurement the coordinates are measured in.
 If (3,4) means 3ft along the x axis and 4ft up the y axis, then the distance between them is 5 ft.
 If (3,4) means 3miles along the x axis and 4 miles up the y axis, then the distance is 5 miles.
You're using data defined using SRID 4326, which is a geographic coordinate system that gives coordinates in angular degrees of latitude and longitude. If you store this kind of data using the geometry datatype, then the distance between two points is also going to be measured in degrees...
... so, the conversion factor of 0.00000272 is an attempt to convert from angular degrees to feet. Unfortunately, this is a pretty flawed conversion:
A factor of 0.00000272 suggests that the length of each degree is 1/0.00000272 = 367,647 ft
The problem is that the actual distance represented by each degree of longitude on the surface of the earth differs is not constant  it differs with latitude.
 At the equator, one degree of longitude corresponds to about 365,221 feet, so your approximation is not too far off.
 As you move north to a latitude of 30 degrees (say, about level with Cairo, Egypt), then the distance covered by one degree of longitude is only 316,555 ft
 Further north at 45 degrees latitude (Milan, Italy), one degree on longitude = 258,683 ft
 By the time you get to 60 degrees (Oslo, Norwich), one degree = 183,070 ft
 And, if you ever make it to the North Pole, one degree of longitude is effectively zero distance along the ground.
So, your approximation is only going to hold if you are only working with data that lies along the equator (and, even then, it's an approximation).
You've got a choice of two solutions:
a.) Very simple. Use the geography datatype. That will allow you to continue to define positions using geographic coordinate of latitude and longitude, but the results of any calculations such as STDistance() will be given in the unit_of_measure given in the sys.spatial_reference_systems table corresponding to the SRID you use. For 4326, (and most other SRIDs), this is the metre.
b.) If you want to continue using the geometry datatype, instead you should convert your coordinate data to use a projected coordinate system where the coordinates themselves are measured in feet. A lot of the US State Plane coordinates use this, but bear in mind that the projection will be distorted if you include data that lies outside the region of use of the projection.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Proposed as answer by Alex Feng (SQL) Friday, October 2, 2009 7:11 AM
 Marked as answer by Anonymous_189 Thursday, October 8, 2009 1:03 PM
Wednesday, September 30, 2009 6:50 PMAnswerer
All replies

It's a bit hard to understand your description. So your code looks like this?:
DECLARE @Point geography = 'POINT(0.75 52)'; DELCARE @Distance decimal = 750; SELECT * FROM Table WHERE PolygonColumn.STContains(@Point) = 1 OR PolygonColumn.STDistance(@Point) < @DistanceInFeet * 0.00000272;
I've no idea what that conversion factor meant to do, but it certainly isn't right for converting feet to metres!
As you rightly say, SRID 4326 requires distances to be expressed in metres, so if you want your code to accept a parameter in feet, you'll have to multiply it by 0.3048 before passing to any geography methods (1 metre = 0.3048 feet).
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290Wednesday, September 30, 2009 5:15 PMAnswerer 
DECLARE @Point geometry= 'POINT(0.75 52)' ;
DELCARE @Distance decimal = 750;
SELECT * FROM Table WHERE PolygonColumn.STContains(@Point) = 1
OR PolygonColumn.STDistance(@Point) < @DistanceInFeet * 0.00000272;
Yes this is what it is doing. Only difference is it is using the geometry type.
Is there any specific forms of conversion that goes on with the geometry type? thanks,
Wednesday, September 30, 2009 6:07 PM 
Ah  The geometry datatype explains everything!
When you're using the geometry datatype, the unit_of_measure of the spatial reference system doesn't apply. geometry is used for storing planar (x,y) coordinates, so the results of any calculations are given in the same unit of measurement as the coordinate values themselves are stated in.
Suppose you have a geometry coordinate at (0,0), and a coordinate at (3,4). The distance between them will always be 5 units, whatever unit of measurement the coordinates are measured in.
 If (3,4) means 3ft along the x axis and 4ft up the y axis, then the distance between them is 5 ft.
 If (3,4) means 3miles along the x axis and 4 miles up the y axis, then the distance is 5 miles.
You're using data defined using SRID 4326, which is a geographic coordinate system that gives coordinates in angular degrees of latitude and longitude. If you store this kind of data using the geometry datatype, then the distance between two points is also going to be measured in degrees...
... so, the conversion factor of 0.00000272 is an attempt to convert from angular degrees to feet. Unfortunately, this is a pretty flawed conversion:
A factor of 0.00000272 suggests that the length of each degree is 1/0.00000272 = 367,647 ft
The problem is that the actual distance represented by each degree of longitude on the surface of the earth differs is not constant  it differs with latitude.
 At the equator, one degree of longitude corresponds to about 365,221 feet, so your approximation is not too far off.
 As you move north to a latitude of 30 degrees (say, about level with Cairo, Egypt), then the distance covered by one degree of longitude is only 316,555 ft
 Further north at 45 degrees latitude (Milan, Italy), one degree on longitude = 258,683 ft
 By the time you get to 60 degrees (Oslo, Norwich), one degree = 183,070 ft
 And, if you ever make it to the North Pole, one degree of longitude is effectively zero distance along the ground.
So, your approximation is only going to hold if you are only working with data that lies along the equator (and, even then, it's an approximation).
You've got a choice of two solutions:
a.) Very simple. Use the geography datatype. That will allow you to continue to define positions using geographic coordinate of latitude and longitude, but the results of any calculations such as STDistance() will be given in the unit_of_measure given in the sys.spatial_reference_systems table corresponding to the SRID you use. For 4326, (and most other SRIDs), this is the metre.
b.) If you want to continue using the geometry datatype, instead you should convert your coordinate data to use a projected coordinate system where the coordinates themselves are measured in feet. A lot of the US State Plane coordinates use this, but bear in mind that the projection will be distorted if you include data that lies outside the region of use of the projection.
Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290 Proposed as answer by Alex Feng (SQL) Friday, October 2, 2009 7:11 AM
 Marked as answer by Anonymous_189 Thursday, October 8, 2009 1:03 PM
Wednesday, September 30, 2009 6:50 PMAnswerer 
Wow thanks a lot for the information.
I think using choice B is out of the question. I try select geography::STGeomFromWKB(geom.STAsBinary(),geom.STSrid) and it return the conversions are not valid geography data types. Is there a way to test whether they are valid for geometry > geography? If they are not is there a simple fix?
thank you for all the help.Wednesday, September 30, 2009 7:13 PM