Geospatial data in SQL

Question

I’ve been experimenting with a geographic data type lately and just love it. But I can’t decide if I should convert from my current scheme, which stores the latitude and longitude in two separate numeric (9.5) fields, to the type of geography. I calculated the size of both types, and the Lat / Long way of representing a point is 28 bytes for one point, while the geography type is 26. Not a big increase in space, but a huge improvement in performing geospatial operations (intersection, distance measurement, etc. ) that are currently being processed using inconvenient stored procedures and scalar functions. I'm interested in indexes. Will there be more geographic data type space for indexing data? I feel that this will happen, although the actual data stored in the columns is less, I believe that the work of geospatial indexes will ultimately lead to a greater spatial distribution for them.

PS as a side note, it seems that SQL Server 2008 (and not R2) does not automatically look for geospatial indexes unless you explicitly talk about using the WITH clause (INDEX ())

Answer 1

In my opinion, you should definitely use only spatial types. The spatial type is optimized for spatial queries, and if spatial queries are what you need, I think it's a simple choice.

As a side effect, you can get rid of your geographic functions and procedures, as they are (possibly) built into SQL Server 2008. However, you may need to spend some time optimizing spatial indexes, but it depends on your specific case.

Answer 2

I understand that you are trying to decide whether to save one of the two, but you might think about saving both. If you export your data to form files, it is common practice to use the lat lon field along with the geom field.

Answer 3

I would save both. It may be useful to easily request the source coordinates of a particular function without requiring spatial operations. You can know the starting points, as well as the ability to create new geometry from them, if you need it in another coordinate system (for example, if you have geometry in a certain projection that will lose a lot of accuracy to another).