
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 SAD69 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 6: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 Earthmodel only).
If, for some reason, you can't use ArcGIS projection/datum transformation features, my suggestion is to consider the use of a 3rdparty 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



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 7: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 xcoordinate 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 Ycoordinates 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 Xcoordinates up by 10,000,000)
 Technically, the resulting coordinates are now referred to as:
 Eastings (xcoordinate) and Northings (ycoordinate)<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



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 8: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



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

