What would be the best datatype to store Longitude/Latitude co-ordinates such as below? Is it decimal? I was hoping there was a better way, just making sure as I really want to keep this code as optimized as possible. I'm storing it as a double in the VB app code if that means anything.

If you are planning on using them it great circle distance calculations using the Haversine distance formula or the circular law of cosines, you will have to convert the values to radians first, so it would make sense to convert them to float using the RADIANS() function, and store them that way.

I would probably store them both ways, as numeric latitude and longitude that are easy to read, and as float radians to eliminate the overhead of converting them on the fly when doing distance calculations.

how did you define your numeric column? it works this way: numeric(x,y) where x is the total number of digits, and y is the number of digits after the decimal point. if your y=0, that would explain your rounding:

You'd have to play with it and do some testing, but I've read people using CLR UDT's for geospacial data. I have yet to find a good use for CLR UDT's, though.

Jay White

It would be interesting to compare performance of a CLR UDT against a regular SQL UDT for computing the great circle distance with the Haversine distance formula.

I was doing some testing recently with a Zip code table that had latitude and longitude to see how long it took to find all locations within a certain distance. It seemed to perform OK with for an occasional lookup, but I would want to optimize it as much as possible if there were a lot of lookups of this type.