Which is the correct spatial data type?

Question

Which is the correct spatial data type?

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Ozzmodiar Ten Centuries Points: 1345 More actions · Answer 1

An excellent and interesting questions!

Thanks so much. I've not had an opportunity to actually work with either data type in my career so I did quite a bit of reading on both before I made my choice. Thankfully I got it right!

And @TomThompson; don't feel too bad, I'm sure you'll get it next time. I don't think the timing on it was right this time around. It's a good idea with some good reasoning, unlike those d*mn Quebecors we have here in Canada! 😛

They have tried a few times to no avail.

Kick6Tiger SSCrazy Points: 2380 More actions · Answer 2

Thank goodness for Top Gear teaching me what a lorry is, or I never would have gotten this right. 😎

Aigle de Guerre!

okbangas SSChampion Points: 11773 More actions · Answer 3

I've searched a bit, and from what I can see, both Geometry and Gography stores the coordinates as IEEE 754 64-bit float internally, hence there is no difference in precision... Please review the following code:

DECLARE @geo geography;

SET @geo = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)

SELECT @geo.ToString();

go

DECLARE @geo geometry;

SET @geo = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)

SELECT @geo.ToString();

go

Both select statements return the same:

POINT (1.2345678901234567 1.2345678901234567)

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 4

okbangas (9/20/2014)
I've searched a bit, and from what I can see, both Geometry and Gography stores the coordinates as IEEE 754 64-bit float internally, hence there is no difference in precision... Please review the following code:
DECLARE @geo geography;
SET @geo = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)
SELECT @geo.ToString();
go
DECLARE @geo geometry;
SET @geo = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)
SELECT @geo.ToString();
go
Both select statements return the same:
POINT (1.2345678901234567 1.2345678901234567)

The two data types are identical in structure and numerical accuracy, the difference is the actual meaning of the data stored within each type, one can think of geometry as a position and geography as a location. Inspecting the hexadecimal representation of the two:

DECLARE @geo1 geography;

DECLARE @geo2 geometry;

SET @geo1 = geography::Point(1.234567890123456789, 1.234567890123456789, 4326)

SET @geo2 = geometry::Point(1.234567890123456789, 1.234567890123456789, 4326)

SELECT CONVERT(VARBINARY(64),@geo1,3) AS GEO_BIN UNION ALL

SELECT CONVERT(VARBINARY(64),@geo2,3);

shows that the two are identical

GEO_BIN

-----------------------------------------------

0xE6100000010CFB598C42CAC0F33FFB598C42CAC0F33F

Although the SRID is set in both instances, the geometry data type will ignore it which renders it useless for geographical coordinates. Consider the following example:

DECLARE @GEOGR01 GEOGRAPHY = GEOGRAPHY::Point(50,0,4326);

DECLARE @GEOGR02 GEOGRAPHY = GEOGRAPHY::Point(51,0,4326);

DECLARE @GEOM01 GEOMETRY = GEOMETRY::Point(50,0,4326);

DECLARE @GEOM02 GEOMETRY = GEOMETRY::Point(51,0,4326);

SELECT @GEOGR01.STDistance(@GEOGR02) AS P_DISTANCE UNION ALL

SELECT @GEOM01.STDistance(@GEOM02);

Results

P_DISTANCE

----------------------

111238.680801466

1

The geography returns the distance but the geometry returns the difference between the two x values of the coordinates.

😎

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

TomThomson (9/19/2014)
Good question.
I was feeling too depressed by my nation's decision to stay under England's domination :crying: to do any research this morning, so I took a guess and got it wrong. 🙁

Hard luck 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 6

Like some here said, I never used spatial data types before, so definitly learnt somthing new today, thanx for the question.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

mickyT SSChampion Points: 10360 More actions · Answer 7

mickyT

SSChampion

Points: 10360

September 21, 2014 at 1:13 pm

#1746922

Good question thanks.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Hany Helmy (9/21/2014)
Like some here said, I never used spatial data types before, so definitly learnt somthing new today, thanx for the question.

Same here. Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 9

Thank you Don for the question. Nice one.

Thank you to Okbangas and Eirikur for putting together the sample.

Thumb up!

Cheers,

Iulian