wgs84 lat/longs to web mercator projection

Jan 25, 2010 at 1:42 PM

Hi spatial experts,

I was wondering, does the SqlProjection.TranverseMercator(longitude0) function allow to project from wgs84 to web mercator (I think the function only allows for "Mercator" as it is with the 60 zones of 6degrees each)? 

I'm trying to put my SQL server spatial data in GMaps - if anyone has any suggestions on how i can get my data into web mercator (required fro GMaps display) , I would appreciate any pointers.

Thanks,

Mike

Jan 25, 2010 at 2:20 PM
Edited Jan 25, 2010 at 2:38 PM

 

So, i've run a little test to check things out...my test point, lat,long (52,5), is in Netherlands:

SqlGeography hollandPoint =SqlGeography.Point(52,5,4326);

SqlProjection proj = new SqlProjection();
proj = SqlProjection.TranverseMercator(0);  //(or Mercator)
SqlGeometry geometry = proj.Project(hollandPoint);

geometry.toString() returns this: POINT (0.053710020589542724 0.90942000965256509)

i was expecting something more like (x: 555904, y: 6797162) which are the x,y coordinates in web mercator

 

Developer
Jan 25, 2010 at 9:02 PM

Our projection algorithm is based on a unit sphere (sphere of a radius = 1).  You will need to use the affine transform to scale these values to WGS84 Authalic Sphere (6,371,007.2 meters).  Lastly, you will need to again use the affine transform to shift the resulting coordinates into those expected by the GMap coordinate system (spherical Mercator).

Developer
Jan 26, 2010 at 4:20 AM

A correction to my last post: You need to use a value of 6378137 for the WGS84 sphere.

The value of your test point lat = 52, long = 5 in Spherical Mercator coordinates is:

  556597.4539664, 6800125.4543973 – from Safe Software’s FME

  556597.45396636787, 6800125.4543973068 – from SQL Spatial Tools (CodePlex)

NOTE: in your code fragment, above, you indicate that you are using a TransverseMercator projection.  You need to use a Mercator projection for this exercise.

Here is the T-SQL to accomplish what you need (you can translate into the corresponding spatial library methods):

--CREATE A GEOGRAPHIC POINT IN WGS84 LONGITUDE, LATITUDE COORDINATES
DECLARE @point GEOGRAPHY;
SET @point = GEOGRAPHY::STGeomFromText('POINT(5 52)',4326);

--PROJECT THE GEOGRAPHIC POINT TO A UNIT SPHERE MERCATOR PROJECTION
DECLARE @point_mercator GEOMETRY;
DECLARE @mercator Projection;
SET @mercator = Projection::Mercator(0) -- Initialize projection (0 = Longitude of Central Meridian)
SET @point_mercator = @mercator.Project(@point) -- Perform the projection
SELECT @point_mercator.STAsText() UNIT_SPHERE_MERCATOR
--UNIT_SPHERE_MERCATOR
--POINT (0.087266462599716474 1.0661617106056684)

--SCALE THE PROJECTED COORDINATES FROM THE UNIT SPHERE TO THE WGS84 SPHERE
DECLARE @point_mercator_wgs84 GEOMETRY;
DECLARE @a FLOAT;
SET @a = 6378137 -- Spherical Mercator Radius in meters 
SET @point_mercator_wgs84 = AffineTransform::Scale(@a, @a).Apply(@point_mercator) -- perform the scaling operation
SELECT @point_mercator_wgs84.STAsText() AS WGS84_MERCATOR
--WGS84_MERCATOR
--POINT (556597.45396636787 6800125.4543973068)

Sep 1, 2011 at 7:28 PM

Is the Mercator projection that you are refering in this post the same that it's been use by Google Maps and Bing Maps? Web Mercator Auxiliary Sphear with SRID 3857?

Also the code is reprojecting the coordinates but it does not set the apropiate SRID to it. How could you alter this line to set the apropiate SRID?

SET @point_mercator_wgs84 = AffineTransform::Scale(@a, @a).Apply(@point_mercator) -- perform the scaling operation
Sep 2, 2011 at 6:49 PM
Edited Sep 5, 2011 at 10:43 AM

Hi Mike

I also want to convert lat/lon co-ordinates in wgs84 to mercator projection.

Can you please let me know, how you have done this.

Developer
Sep 2, 2011 at 7:57 PM

@hmelendez

Yes, this is the same coordinate system used by Google Maps, Bing Maps and others for web browser-based presentation of spatial data (SRID 3857).

>Also the code is reprojecting the coordinates but it does not set the apropiate SRID to it. How could you alter this line to set the apropiate SRID?

SET @point_mercator_wgs84.STSrid = 3857
You can test that the SRID was set to the desired value by:
SELECT @point_mercator_wgs84.STSrid

Please let me know if you have any questions.
-Ed
Jan 8, 2012 at 8:09 AM
Edited Jan 8, 2012 at 8:10 AM

Hi,

I am storing data as Geometry. However, the Project method seems to accept only Geography type. So, I changed the above T-SQL a bit to suit to my needs. Below is a sample script performing re-projection to one Polygon.

 

DECLARE @geom Geometry;
SET @geom = Geometry::STGeomFromText('POLYGON ((77.9672499974569 13.3208888689677, 77.968444442749 13.320666662852, 77.9683610916138 13.3203333218892, 77.9671388626099 13.3205555597941, 77.9672499974569 13.3208888689677))', 4326)

-- Correct the ring orientation if it is incorrect
-- SqlGeography, unlike SqlGeometry data type is very unforgiving
-- This will force a validation of the Spatial object and correct the ring orientation internally
-- For more info see: http://blogs.msdn.com/b/edkatibah/archive/2008/08/19/working-with-invalid-data-and-the-sql-server-2008-geography-data-type-part-1b.aspx
SET @geom = @geom.STUnion(@geom.STStartPoint());

DECLARE @geography Geography
SET @geography = Geography::STGeomFromText(@geom.STAsText(),@geom.STSrid)

DECLARE @mercator Projection;
SET @mercator = Projection::Mercator(0)

SELECT 
	@geom.STAsText() AS WGS84,
	@mercator.Project(@geography).STAsText() AS WebMercator

 

However, the values I get are totally off! Here is what I get:

Original Geometry: POLYGON ((77.968361091625411 13.320333321884391, 77.968444442754844 13.32066666285391, 77.967249997454928 13.320888868969632, 77.967138862615684 13.320555559796048, 77.968361091625411 13.320333321884391))

Re-projected Geometry: POLYGON ((1.3608046134327036 0.23460666206021522, 1.3608060681843475 0.23461264080687308, 1.3607852211811289 0.2346166262623624, 1.3607832815122705 0.23461064808050117, 1.3608046134327036 0.23460666206021522))

 

I am not able to figure out what I am doing wrong. Any help will be great.

Thanks in advance.

Regards,
Raghu