Manifold Community Site: MS SQL Coordinate System problem - Projection Problem

RE: MS SQL Coordinate System problem - Projection Problem

Domagoj — Sat, 30 May 2009 07:44:40 GMT

I have problem when i export data from Manifold to MS SQL Spatial database. I have using my coordinate system definition.

Picture1: Coordinate system which i am using

Picture2: Projection which i have when i try to export to MS SQL Spatial

Picture 3: Version of Manifold i am using

Right projection definition is:

- Spatial Reference Information:

- Horizontal Coordinate System Definition:

- Planar:

- Map Projection:

Map Projection Name: Transverse Mercator

- Transverse Mercator:

Scale Factor at Central Meridian: 0.9999

Longitude of Central Meridian: 18

Latitude of Projection Origin: 0

False Easting: 6500000

False Northing: 0

- Planar Coordinate Information:

Planar Coordinate Encoding Method: coordinate pair

Planar Distance Units: meters

- Geodetic Model:

Horizontal Datum Name: Militar-Geographische Institut modified

Ellipsoid Name: Bessel, 1841

Semi-major Axis: 6377397.155

Denominator of Flattening Ratio: 299.152815

Go to Top

- Metadata Reference Information:

Metadata Date: 20090530

Metadata Standard Name: FGDC Content Standards for Digital Geospatial Metadata

Metadata Standard Version: FGDC-STD-001-1998

Metadata Time Convention: local time

How to have this definition in Manifold and for export to MS SQL Spatial?

Attachments:
AssignProjection.jpg
ManifoldVersion.jpg
WrongProjection.jpg

RE: MS SQL Coordinate System problem - Projection Problem

jkelly — Sun, 31 May 2009 19:47:14 GMT

I have noticed that Sql Server does have a limited set of spatial reference systems that it supports. If you want to check to see that SQL Server supports the system you are interested in, check the view sys.spatial_reference_systems in SQL Server. If the correct definition is there, then we can deal with this. If the definition isn't there, then you will have to reproject the data and store in a different coordinate system.

If the definition is there, but manifold doesn't regonise it, then this is a manifold issue of not correlating it's spatial reference system to srids. I have noticed that Manifold does not support the entire set of sql server spatial reference systems. This is a pain, but it can be sorted. When you export, make sure you don't reproject, and go with whatever projection manifold chooses. It will be wrong, but that doesn't matter. Once exported, run an update statement in sql server, updating all srids of the geometries to the srid that you want. I think the statement is something like

--SQL

update dbo.[your table] SET geom.StSrid = 2567

where 2567 is the desired srid of your data and geom is the geometry column. Then when you link this in manifold, make sure you set the assign projection to the one you want, same as you have shown above.

When you add new shapes or update existing shapes, then manifold may get set the geometry srid wrong, but this should be able to be corrected by running the update statement I have given above. You could do this automatically by rolling it in a trigger in the database.

I recall Adam W. commenting that the srid projection stuff was in the pipeline for changing in the (hopefully) upcoming version 9 release.

Another option is to reproject into a standard projection, say maybe lat / long and export this to the database. The when you link the data back, use projected maps in your desired projection to display the data.

RE: MS SQL Coordinate System problem - Projection Problem

Kenneth — Tue, 28 Jul 2009 13:47:26 GMT

Hi Jkelly,

The sys.spatial_reference_systems in SQL Server is ONLY limited for GEOGRAPHY data type.

When you export data from Manifold to SQL Server, Manifold will only export as GEOMETRY data type in SQL Server. And Geometry data type can accept any coordinate systems.

What the problem you are experiecing is:

Manifold considers ALL UTM projections with datum WGS1984. If you coordinate system datum is not WGS 1984, it will have problem. If you projection datum is WGS 1984, it will be fine.

You problem has been discussed on the forum many times. you can search out the posts.