Best Practice for Storing Spatial Data in Multiple Projections? RRS feed

  • Question

  • From what I've been able to determine MS SQL can't do reprojections is that correct? Assuming it is, the state of Washington requires that our GIS data be managed in Washington Stat Plane South but for our web mapping sites need the data in Web Mercator.  Is it OK then to have two geometry columns in a spatial enabled table, say a county table, one in WSPS and the other in WM? 

    I’m coming at this from a 30 year background in GIS using ESRI software.  Usually we would store the shape / geometry in one projection and project it to other on the fly when needed.  That way there is only one definitive source.  I don’t see a way to do this in MS SQL.

    Tuesday, August 5, 2014 9:59 PM


  • Hi Scott.

    Storing two columns of spatial data is fine in SQL Server. And you are correct that there is no built-in reprojection in SQL Server, most folks do that as part of data loading. There was talk of porting parts of ogr2ogr to SQLCLR, but I don't think anyone did that.

    Cheers, Bob

    • Proposed as answer by Sofiya Li Wednesday, August 6, 2014 7:58 AM
    • Marked as answer by Sofiya Li Thursday, August 14, 2014 7:26 AM
    Wednesday, August 6, 2014 5:54 AM