October 23, 2019 at 3:36 pm
Hello everyone,
I have a table with a field "shape" which is geometry. I need to get the lat/long from this point. I have the following query and it is returning my point but I'm having trouble getting lat/long.
SELECT [SHAPE].STAsText() XY
, .shape.STX as X -- geometry
, .shape.STY as Y -- geometry
, .[SHAPE].[STSrid] AS [STSrid]
, .[SHAPE]
FROM [dbo].[shapetable] AS
The results I'm getting for the XY column are like these, that seems to be the wrong lat/log point information. Can you please point me in the right direction to get this figured out?
XY X Y STSrid SHAPE
POINT (1609293.5045530796 10774628.189474732) 1609293.50455308 10774628.1894747 2277 0xE5080000010C00642A814D8E3841502D10860C8D6441
POINT (1659862.1521032453 10860417.1465054) 1659862.15210325 10860417.1465054 2277 0xE5080000010C003DF026D6533941182CB024F0B66441
POINT (1563449.9321984202 10828316.118844062) 1563449.93219842 10828316.1188441 2277 0xE5080000010C408EA4EE39DB37411092CD8343A76441
POINT (1560553.0134515762 10828942.284586474) 1560553.01345158 10828942.2845865 2277 0xE5080000010C00907103E9CF374118551BC991A76441
October 23, 2019 at 4:39 pm
You're using the wrong spatial type. Geometry uses a flat Euclidean coordinate system, but latitude and longitude are properties of a round-Earth coordinate system which is used in Geography.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 23, 2019 at 5:11 pm
Oh I see, I knew my numbers were not correct but I wasn't sure why since I have limited knowledge on this topic. Is there a way to convert the Geometry flat euclidean coordinate system to latitude and longitude used by Geography?
October 24, 2019 at 2:05 pm
If the information of the Geometry flat euclidean coordinate system is of a limited area this information can be converted fairly accurate. (The curvature of the earth does not play a (large) role in the conversion).
For example a shopping mall, you can convert the information within the accuracy of the 'source' information, this often is not more accurate than a few centimeters.
For a town with the size of a few kilometers across this can be accurate to within a few meters.
For a small country the curvature off the earth makes it more difficult to make a 'correct' conversion. The curvature has te be accounted for. For a large country the accounting of the curvature is essential.
See the site :
https://en.wikipedia.org/wiki/Geographic_coordinate_system
For the actual dimensions at a geographic location.
For the actual conversion you need for at least one point in the Geometry flat euclidean coordinate where it is in the Geographic coordinate system. If that is perfectly North aligned you only need to know the distances to this point. If it is not north aligned you have to convert the coordinate system to a north aligned coordinate system.
Then calculate the x and the y factor using the site and your knowledge of the euclidean coordinate system and then it's a fairly strait convert.
Depending on the coordinate system there are conversion programs which can automatically convert between different coordinate systems.
The practicality of this method depends on what you actually want to achieve with the conversion,
Greetings,
Ben
From the site:
On the WGS84 spheroid, the length in meters of a degree of latitude at latitude f (that is, the distance along a north–south line from latitude (f - 0.5) degrees to (f + 0.5) degrees) is about
=111132.92-559.82*COS(2*PHI)+1.175*COS(4*PHI)-0.0023*COS(6*PHI)
Similarly, the length in meters of a degree of longitude can be calculated as
=111412.84*COS(phib)-93.5*COS(3*phib)+0.118*COS(5*phib)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply