Custom projection

Question

Hi Everyone,

I have a custom projection which is Lambert Conformal Conic, ( Center latitude 45, Center Longitude -77, 1st standard latitude 44 and 2nd standard latitude 45). Its always keep changing the projection when I was trying to export geometry column from manifold to SQL server 2008. ( I think its happening because in SQL sys.spatial_reference_systems tablehas not any value for my customized projection)

Everytime I tried to link that SQL table into manifold I have to reset the projection according to my custom one which is fine but if I do any analysis It keep changing to SQL's projection.

I would appreciate your help in this regards. PLz urgent..

Thanks

Answer 1

Hi,

If you're using geometry data with a custom projected coordinate system then the problem is unrelated to sys.spatial_reference_systems - that table is only used for storing geodetic spatial reference systems used by the geography datatype.

When you say that it's "changing the projection", do you mean that it's assigning a different SRID than that which you assigned? (If so, what SRID are you using?), or is it actually reprojecting the data? (If so then it must be a problem with Manifold, since SQL Server does not provide any projection functions).

It's a bit hard to tell from your description exactly at what point the problem is occurring, and therefore whether it's likely a problem with Manifold or with SQL Server. Is the data already stored in SQL Server as a geometry column with the correct SRID, and then loading it in Manifold causes it to be updated? Or is the data already in Manifold (where is it stored?) and then exporting it to SQL Server gives it the incorrect SRID?

Every geometry data in SQL Server must have some SRID associated with it, so if it doesn't have the SRID you were expecting, what SRID does it have?!

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

The geometry column in SQL server looks like that.. (0xDF080000010C68000FE1AA28EAC0521CB8320487E8C0).. There is no SRID for my custom projection in sys.spatial_reference_systems table for sure.

The projection is chnaging every time i do  export and import from manifold! Data in SQL server is not saved with the correct SRID. Everytime i link or import that table into manifold i have to set the projection to my custom coordinate system.  plz ask if u have aymore qouestions.

Thanks.

 

Answer 3

The SRID of that instance is 2271, which is the Pennsylvania North projection (NAD83/ft). I have no idea how that SRID would have got assigned in SQL Server unless it had been set somehow in Manifold... have you raised a support request with Manifold?

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 4

Hi Tanoshimi,

 

I have no idea either how that SRID got assigened ! In manifold The projection is Lambert Conformal Conic, North American 1983(Canada), Center Latitude 45.00, Center Longitude -77.00, 1st Standard Latitude 44.00, and 2nd Standard Latitude is 46.00.

Its automatically taking this SRID as soon as i export the data into SQL server.

This projection doesn't have any EPSG related code.

Do u hvae any idea if ther is no any SRID for the above projection in SQL server which table should i update for that and how would I do that plz.

I do really appreciate your time and help.

 

Thanks.

 

 

 

Answer 5

Well, since your data is using a non-standard projection you can assign any SRID you want. For example:

UPDATE YourTable

SET YourGeomColumn.STSrid = 9999;

Note that SQL Server essentially ignores the SRID of projected geometry data - you don't need to (and can't) add your projection parameters into a system table in SQL Server, and it wouldn't make a difference if you did - calculations using geometry data are performed on a 2-dimensional plane in which the projection used is unimportant.

The problem is how to tell Manifold that, when it loads this data in from this table, it should interpret features with SRID "9999" using your custom projection parameters. Not being a Manifold user, I'm afraid I don't know how to do that.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 6

Its very simple to import or link any table into manifold. In my case when I was trying to import the table its getting the projection of Pennsylvia ...(Which SRID is 2271) as its saved in SQL on that way.  I just need to update that projection system into SQL system.

I don't think Manifold is the problem here, Manifold support a vast array of coordinate system and provides facility to create customized projections as well. After importing that table into manifold i could always change the projection from Pennsylvia (Wrong one) to Lambert Conformal Conic, North American 1983(Canada), ...(correct one). and its projecting fine. if I save that table in manifold and do analysis everything is working fine.

 

But i wanted to update the analytical data into my SQl table directly and its possible only with the link option with that SQL table but not by saving the table into Manifold it self.

So when i wanted to do analysis or make queries  with the other layers in the manifold and SQl server link table its not working cos their projections are different.

Perhaps that is where the problem lay, What would happen if a coordianate system, that is not standard (modified/ customized) is being uploaded to the SQL server, how is this handle by SQL server? Is the coordinate system or projection information should match with the exported geom column into sql sever?

I think this is the case here, Manifold is asking SQL server for this modified coordinate system which is not in the SQL server systmes' table.  

Thanks.

Answer 7

I think this is the case here, Manifold is asking SQL server for this modified coordinate system which is not in the SQL server systmes' table. 

We sort of agree... it's just that I think what's happening is that Manifold is getting data from SQL Server that is not in Manifold's table of known coordinate systems.

SQL Server doesn't store individual projection parameters of each geometry instance - it doesn't store the fact that your data uses a Lambert Conic Projection with latitude of center x, scale factor y, etc. etc., and it has no way of doing so. What it does instead is to store an integer value that represents all those parameters: 4326, 27700, or 32768, for example. You can set or retrieve this value using the geometry STSrid property. Generally speaking, these SRIDs correspond to the EPSG identifier for that system, which Manifold recognises and therefore loads the appropriate parameters associated with that spatial reference system. The problem comes when the SRID doesn't correspond to an EPSG-recognised system.

What I'm suggesting is that you need to add a custom spatial reference system to Manifold and associate it with the same SRID that you are storing that data in SQL Server (in my example above, SRID 9999). That way, when Manifold retrieves the SRID 9999 data from SQL Server, it knows what projection parameters they correspond to. I just don't know how to do that in Manifold.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 8

This page seems to describe the feature you're looking for - it shows how to match coordinate reference systems in Manifold with those in a spatial RDBMS (this example is for Oracle, but I guess it must exist for SQL Server too):

http://www.georeference.org/doc/database_object_projection.htm

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 9

Thank you for your time. I have been doing some research on how to add custom coordinate reference system to manifold and associated with SRID but seems there is no option of doing that. Manifold  supports to create custom projection xml file that would work on the fly during the export/ import process but no SRID could be related to that custom projection. I am really lost now and dont have any idea wht to do now!!

Anything that u could come up with would be highly appreciated.

Thanks again.