Best Practice for Storing Spatial Data in Multiple Projections?

Question

Best Practice for Storing Spatial Data in Multiple Projections?

Randy SK

SSC Veteran

Points: 255
More actions
August 6, 2014 at 9:24 am

#309731

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

From what I've been able to determine MS SQL can't do reprojections is that correct?

SQL Server will not natively reporject, but the is an option to create or use a CLR function to do this. SQL Server Spatial Tools has a reprojector in it.

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?

It is quite OK to do this. I have some tables where I have a geometry for both the boundary and centroid location. Personally in your situation I would have another database/schema that has only the set of tables required for the web mapping. Then refresh those table from you main set as required.

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.

Is you Web Mapping engine able to do the reprojections for you?

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 2

I think that if you use the ArcGIS JavaScript API, you can change the projection on the fly.

Hope that this helps.

Thanks...Chris

Randy SK SSC Veteran Points: 255 More actions · Answer 3

CGSJohnson (9/17/2014)
I think that if you use the ArcGIS JavaScript API, you can change the projection on the fly.

That's true that ESRI clients can reproject on the fly but Microsoft can't. It doesn't have reproject capabilities so for SQL only applications I was thinging about having two spatial columns one in Washington State Plain South, our state standard, and WGS84 to handle point in polygon operations for data entered in Lat Long.