Can anyone give real example of geometry and geography datatypes in SQL server and explain me the purpose of them? I haven't seen use of these two datatypes anywhere. We have generic datatypes in sql server like varchar which can also be used to store such data. What is the benefit of using these two datatypes?
4 Answers
Can you calculate the area of a polygon on the earth's surface using just varchars?
If you can I imagine that it's much more complex and less clear than Select @geog.STArea()
The use is for storing data in the appropriate type, just like the XML type is better than a varchar for XML data, etc.
1 Comment
Rzassar
In addition to all people comments, They're internally same (both use binary DataType internally). So if you want to use data to represent it on the map or just represent it as a text or as a feed for a third party application (like GIS once), Then use Geometry and gain the performance. But if you need some calculation over saved data (Like sorting - get distances - get area - etc...) You should use Geography.
geometry and geography are datatypes used for storing spatial information - describing the shape and position of objects in space (usually on the surface of the earth).
Why do you need a dedicated datatype for this sort of information? Think about how you would ORDER BY spatial information - it doesn't have a natural collation such as alphabetical for varchar, or chronological for date/time. And how would you write a query to identify features close to a given location - you can't SELECT * WHERE location BETWEEN 'Bristol' AND 'London'. So, ever since SQL Server 2008, there's been a whole range of methods (and indexes) specifically designed for doing these sorts of queries.
If you want practical examples of when they are used: an insurance company might use a geography field in a Customer table to record the location of every policyholder, and determine how many of them were likely to be affected by rising water levels by joining to a Rivers table which modelled river flood plains also in a geography field. Or, you might want to determine the optimal location for a new store by analysing the geographic coverage of your (and your competitors) existing outlets. Or, plan the optimal logistics route for a delivery vehicle, etc. etc.
Comments
SQL Server supports two spatial data types: the geometry data type and the geography data type.
The geometry type represents data in a Euclidean (flat) coordinate system. The geography type represents data in a round-earth coordinate system.
We can find it easily in sql server 2008 and higher.
create TABLE tab_spatial ( id int IDENTITY (1,1) primary key, zip nvarchar(20), city nvarchar(500), geo geography );
Comments
Ok i have an example for you. You have a table with 4 columns X1, Y1, X2, Y2. In this columns you have coordinates
x1=22.4109883
y1=44.740203
x2=22.4112528
y2=44.7404422
Now in this table you have also 3 columns declared as geometry: GEOSTART,GEOEND,GEOSEGMENT
SET @sqlCommand = 'USE [' + @databaseName + '];' +
' UPDATE [dbo].[StreetsTable]' +
' SET [GEOSTART] = geometry::STGeomFromText(''POINT ('' + [X1] + '' '' + [Y1] + '')'', 4326) WHERE X1<>'''' and Y1<>'''' ';
EXEC (@sqlCommand)
SET @sqlCommand = 'USE [' + @databaseName + '];' +
' UPDATE [dbo].[StreetsTable]' +
' SET [GEOEND] = geometry::STGeomFromText(''POINT ('' + [X2] + '' '' + [Y2] + '')'', 4326) WHERE X2<>'''' and Y2<>'''' ';
EXEC (@sqlCommand)
SET @sqlCommand = 'USE [' + @databaseName + '];' +
' UPDATE [dbo].[StreetsTable]' +
' SET [GEOSEGMENT] = geometry::STGeomFromText(''LINESTRING('' + [X1] + '' '' + [Y1] + '', '' + [X2] + '' '' + [Y2] + '')'', 4326) WHERE X1<>'''' and Y1<>'''' and X2<>'''' and Y2<>'''' ';
EXEC (@sqlCommand)
After the update go and do a select on that table. Go on spatial results an select the spatial column. You will see the use of the geometry.
Hope it helps you
Comments
int,datetimeordecimal?