Some GIS calculation in SQL Server (2005)

[Fuma]

I need to perform some simple radius searches based on the Latitude and Longitude from a database table.
The table has LatitudeSouth, and LongitudeEast fields

my stored procedure accepts 3 parameters

@LatitudeSouth float,
@LongitudeEast float,
@Radius int

SELECT ...... FROM Location as ML ........
WHERE 

		(
			(
				( 6371 * acos( cos( radians(@LatitudeSouth) ) *  
				cos( radians( ML.LatitudeSouth ) ) * cos( radians( ML.LongitudeEast) -  radians(@LongitudeEast) ) +  
				sin( radians(@LatitudeSouth) ) * sin( radians(  ML.LatitudeSouth))))
			) <= @Radius
		)

I got this code and it works 80% on the data. It doesn't work on some coordinates (even though they are correct). And if I increase the Radius to 100KM, it bombs out/or takes forever to load.

Is there a simpler formula that you think I can use and guarantee exact/correct result?

 

[shogun]

I need to perform some simple radius searches based on the Latitude and Longitude from a database table.
The table has LatitudeSouth, and LongitudeEast fields

my stored procedure accepts 3 parameters

@LatitudeSouth float,
@LongitudeEast float,
@Radius int

SELECT ...... FROM Location as ML ........
WHERE 

		(
			(
				( 6371 * acos( cos( radians(@LatitudeSouth) ) *  
				cos( radians( ML.LatitudeSouth ) ) * cos( radians( ML.LongitudeEast) -  radians(@LongitudeEast) ) +  
				sin( radians(@LatitudeSouth) ) * sin( radians(  ML.LatitudeSouth))))
			) <= @Radius
		)

I got this code and it works 80% on the data. It doesn't work on some coordinates (even though they are correct). And if I increase the Radius to 100KM, it bombs out/or takes forever to load.

Is there a simpler formula that you think I can use and guarantee exact/correct result?

Unfortunately when it comes to distance between 2 geographic coordinates... there is no simple calculation as you need to take the earths curvature into account.

What distance formula is that? Looks weird. Google Haversine formula, and use that. And use the arithmetic mean of the earths radius... not that estimation you have there. That radius looks way to rounded for my liking.

I will assume that your coordinates are in EPSG 4326 (WGS84) projection.

Here's a javascript example: http://www.movable-type.co.uk/scripts/latlong.html

Don't use the radius in the javascript example... it's also too rounded. You want arithmetic mean radius in meters. Note that your radius is in km... if you pump in the arithmetic radius accurate to meters... your result will be in meters. Just something to think about.

 

[Fuma]

I will assume that your coordinates are in EPSG 4326 (WGS84) projection.

Nope
E.g.
LatitudeSouth = 32.12345
LongitudeEast = 26.12345

 

[shogun]

Nope
E.g.
LatitudeSouth = 32.12345
LongitudeEast = 26.12345

Those could be perfectly valid WGS84 projection coordinates. Are you perhaps confusing projection with format? Those are in decimal degrees format, which is exactly what you need for the calculations.


For example, if this map is showing that location correctly, then those coordinates are in WGS84 projection:

http://www.openstreetmap.org/?mlat=-32.12345&mlon=26.12345&zoom=15&layers=M


EDIT: you would obviously have to invert the sign for South values and West values (e.g. -32.12345)

 

[Fuma]

Those could be perfectly valid WGS84 projection coordinates. Are you perhaps confusing projection with format? Those are in decimal degrees format, which is exactly what you need for the calculations.


For example, if this map is showing that location correctly, then those coordinates are in WGS84 projection:

http://www.openstreetmap.org/?mlat=-32.12345&mlon=26.12345&zoom=15&layers=M


EDIT: you would obviously have to invert the sign for South values and West values (e.g. -32.12345)

I confused the format and projections. I will see how to do it simpler.

 

[shogun]

I confused the format and projections. I will see how to do it simpler.

Not sure what you mean by simpler... there really isn't a simpler way to do the distance calculations... unless you've had luck with STDistance

EDIT: Found link with STDistance that may help:

http://msdn.microsoft.com/en-us/magazine/dd434647.aspx

It would assume that you convert your coordinates to sqlgeography. Be warned... converting to sqlgeography or sqlgeometry on the fly is damn slow.

EDIT 2: I see you mention 2005. I'm not sure if 2005 has spatial extensions, so STDistance probably doesn't work.