Project X Y UTM projection to Lat/Long

Aug 19, 2009 at 6:26 PM

Hello everyone,

I´m just trying SQL Server Spatial Tools. I have the following situation: i have a table inside a foo database, which records X Y and SRID values. I need to visualize all of this data inside a GIS Framework (ArcGIS) with ArcSDE. The catch is: all of my data is is SAD-69 Lat/Long projection.

I´ve tried a thousand ways to convert my XYs points to Lat/Long, but without success. Is there a chance that you guys might help me with a simple example? Most of the time i get a message: Argument out of range;

I tried a simple hardcoded conversion, but or it returns me the same value (i´ve tried X: 700000 and Y: 7000000 - which returns Lat 0 and Long 39) or it fails with the message described above.

Another question i have, what is the meaning of the parameter Longitute0, when i'm creating a new SqlProjection:TransverseMercator? Is the Prime Meridian of that UTM Zone?

Thanks for your patiente and help.

 

George

Developer
Aug 19, 2009 at 7:16 PM

@georgesilva

Before we go too far, you are aware that ArcGIS can provide the necessary projection/datum transformation support, right?  SQL Server 2008, itself, does not provide any projection/datum transformation support.  SQL Server Spatial Tools does provide some "sample" support for projection transformation but these are not ellipsoidal transformations (support is for a spherical Earth-model only).

If, for some reason, you can't use ArcGIS projection/datum transformation features, my suggestion is to consider the use of a 3rd-party tool like Safe Software's Feature Manipulation Engine (FME).  FME can read/write SQL Server Spatial data and supports the necessary transforms to meet your needs. 

Let me know if this answers your questions.  Thanks,

Ed Katibah

Aug 19, 2009 at 8:12 PM

Hello edwink,

First of all, thank you for your answer.

I'm having some difficulties, because i have a PostgreSQL/PostGIS background. PostGIS itself implements coordinate transformatio using a 3rd library (Proj4), and today is very simple to convert between SRs, it´s just a simple ST_Transform(geometry,SRID) statement.

When will SQL Server 2008 have a similar feature? Full conversions are needed all the time, such as this one i need. I'm using UTM surveyed points, which cannot be collected in Lat/Long, and other data which HAS to be in Lat/Long.

Any tips on solving this? Anyhow, can anyone help me with the following questions?

 - What is the longitute0 (parameter in constructor for TranverseMercator)?

 - Is there any example for converting X Y data (even if it's full ellipsoidal transformations) to Lat/Long? There are precision limits that i'm willing to accept. I just need them to be fairly in place :P, and of course, i will test this extensively.

Thank you guys very much. The library is great and add great support of functions to SQL Server. I will keep up with this project.

Thanks again

Developer
Aug 19, 2009 at 8:33 PM

@georgersilva

Hi George -

In the interest of time, here an example from which you may be able to gleen the necessary information to be successful.  Note that this is for an inverse projection - you need a forward projection, which is supported.  Let me know if these helps (or hurts ;-).

Thanks,

-Ed

<font size="2" color="#008000"><font size="2" color="#008000">

---------------------------------------------------------------------
--Project Geometry column in Highways table into Albers Equal Area coordinates
--
-- Parameters:
-- 1: Central Meridian (-96)
-- 2: Latitude of the Origin (20)
-- 3: First Standard Parallel (30)
-- 4: Second Standard Parallel (40)
---------------------------------------------------------------------

</font></font><font size="2" color="#008000">

 

</font>

DECLARE @albers Projection
SET
@albers = Projection::AlbersEqualArea(-96, 20, 30, 40)
  UPDATE
Highways SET geom = @albers.Project(geog)
GO

---------------------------------------------------------------------
--Transform Geometry coordinates into more reasonable units
-- i.e. we want units in meters and for all values to be in quadrant 1
-- of the Cartesian coordinate system.
--
--We will do this in 2 steps
-- 1. Convert units to meters, based on the Authalic Sphere for the WGS84
-- reference ellipsoid.
-- 2. Add a false Easting to the x-coordinate values to adjust all x,y
-- coordinates into quadrant 1 of the Cartesian coordinate sytem
--
--Notes on the Authalic Sphere. Authalic means "equal area" or "area
--preserving". In this case we will be using a sphere with a
--radius 6,371,007.2 meters. This will produce an Authalic Sphere for the
--WGS84 ellipsoid (i.e. the surface area of the resultant sphere will be
--the same as the surface area for the WGS84 ellipsoid.
---------------------------------------------------------------------
-- Step 1. Scale X and Y-coordinates up meters by applying the radius value for
-- the WGS84 Authalic Sphere<font size="2" color="#008000"><font size="2" color="#008000">

 

</font></font><font size="2" color="#008000">

 

</font>

DECLARE @a FLOAT
SET
@a = 6371007.2 -- Authalic Sphere radius in meters
  UPDATE
Highways SET GEOM = AffineTransform::Scale(@a, @a).Apply(GEOM)
GO

-- Step 2. Adjust coordinates to 1st quadrant (move X-coordinates up by 10,000,000)
-- Technically, the resulting coordinates are now referred to as:
-- Eastings (x-coordinate) and Northings (y-coordinate)<font size="2" color="#008000"><font size="2" color="#008000">

 

</font></font><font size="2" color="#008000">

 

</font>

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

UPDATE

</font></font><font size="2" color="#0000ff">

 

</font>

Highways
 
SET GEOM = AffineTransform::Translate(10000000, 0).Apply(GEOM)
GO

 

 

Aug 19, 2009 at 9:07 PM

Hey Ed, thanks for your time.

I´ve tried your example and kinda hurts :P

I could not follow your trail of tought.

I'm having all sorts of questions because, after all, TransverseMercator only has 1 parameter: longitude0;

Argh! It's driving me insane :P

Thanks again for your time and sorry for the brain hickups.

 

Developer
Aug 19, 2009 at 9:14 PM

Sorry for the formatting hiccups.  I don't have time today or tomorrow but let me look at your problem on Friday.  Any chance you could send me a tiny bit of your data (shapefile, SQL Server backup file, other?)?   Here is my email address ed.katibah at microsoft.com.

Thanks,

-Ed

Aug 19, 2009 at 9:18 PM

Yea sure Ed. I will talk to my IT manager and he generate the data that you need. I'm sorry to take up all of your time :)

But i do appreciate the attention. I'll send you the data first thing in the morning.

Thanks a lot!

George