Know about spatial database concept in SQL Server

Question

I am just starting with Spatial database. I have a table with Latitude and Longitude columns with data type double.

Now I want to add a column of datatype geometry to my table so that I can use this table for spatial queries. I want to insert data to this column which contains the point (from Latitude and Longitude column.).

How I can do this?

Try this for more info: http://stackoverflow.com/questions/11245645/sql-server-from-longitude-and-latitude-to-geometry-data-type — Eric Yeoman, May 19 '16 at 09:40
Ah, though your question did state Geometry, there is both Geometry and Geography datatypes in SQL Server :) — Eric Yeoman, May 19 '16 at 10:01

score 2 · Answer 1 · answered May 19 '16 at 12:55

2

If it were me, I'd use a persisted computed column.

create table G(
   [ID] int identity primary key clustered,
   [longitude] float, 
   [latitude] float, 
   [point] as geography::Point(latitude, longitude, 4326) persisted
)

When done this way, you need only maintain the longitude and latitude columns and the point column will be automatically correct.

answered May 19 '16 at 12:55

Ben Thul

31,080
4
45
68

Thanks .. But I have already exists the table just want to add new column. – Sandeep Kumar May 20 '16 at 04:51
This was only an example. You can add the persisted column as I've defined it above just like you would add any other column. – Ben Thul May 20 '16 at 05:10

score 1 · Accepted Answer · answered May 19 '16 at 09:50

You should probably store these points as sql server geography instead of sql server geometry. SQL Server geography will give you more accurate calculations of distances and areas because it takes into account the spherical shape of the earth. SQL geometry is planar geometry and is particularly bad for latitude and longitude because the units for distance etc. would be degrees!

Create a column called (say) geog in your table of data type geography. Because all the data will be points, it's very easy to update the table with:

Update MyTable set geog = geography::Point(latitude, longitude, 4326)

MSDN

The 4326 here is the spatial reference system that you are using. 4326 is the code for the WGS84 spatial reference system, which is used a lot for global lat/long coordinates.

Thanks for your quick help. Now if i want to find out point between 200 meters. How I can find this? — Sandeep Kumar, May 19 '16 at 09:59
If you mean you want to find the points in your table that are within 200m of another point variable (called testpoint here) then use STDistance. For example: declare @testpoint geography = geography::Point(..... select * from mytable where geog.STDistance(@testpoint) < 200 — andyb, May 19 '16 at 10:11

Know about spatial database concept in SQL Server

2 Answers2