Mixed Spatial References

Question

Thanks for the obvious hard work implementing spatial data support in SQL Server. I have a couple of questions/observations about spatial references:

 

It is currently possible to store spatial data with differing spatial references in the same table. This is compliant with OGC specs but is not necessarily a best practice. PostGIS gets around this by placing a constraint on the spatial column to limit it to a single user-specified SRID. This is done (among other things) in the AddGeometryColumn function. I was curious if you would consider adding a similar function to create the column and constrain the SRID? This might be very helpful for those who are less experienced with spatial data.

 

I have discussed it here: http://geobabble.wordpress.com/2007/11/26/spatial-references-in-sql-server-2008-part-2/

 

Also, do you plan to expand spatial reference support beyond geographic coordinate systems to include projected?

 

Overall, I've been highly impressed with the spatial support. Keep up the good work!

 

Bill Dollins

 

Answer 1

Hi Bill,

 

Thanks for the kind words---they are very much appreciated.

 

The uniform SRID issue is an interesting one.  We aren't planning on enforcing such a restriction at the system level, although I think it is generally a good best practice.  That best practice can be enforced through a constraint, as you observe, but could also be enforced at the application level.  We're planning some more technical white papers, and I would expect to see a discussion of this there.

 

Perhaps there is a difference in perspective here between GIS and database people.  We definitely have a database perspective in this work, and as such we want to be the platform for as diverse a set of data and operations possible.  While it is generally a best practice, we have seen scenarios where users want to store heterogeneous data in a column, and we want to support them.  A GIS built on our platform would likely enforce SRID homogeneity, but this is their prerogative.

 

Another way of putting this is that our goal is not to build a GIS, it is to build a spatially-enabled database.  As such, we want an architecture that supplies the functionality needed to store, query, and retrieve data, while putting the minimum number of constraints on those who built upon us.

 

Regarding planar SRIDs: Right now, we don't seen any value in exposing a set of standard SRIDs for planar coordinate systems, since they wouldn't be used by the system.  We need to know about the geodetic SRID, since we need to know what the shape of space is.  For the moment, our best practice would be to use the standard EPSG numbering system.

 

Cheers,

-Isaac

Answer 2

Isaac,

 

Thanks for the quick reply. I understand the pull between database requirements and GIS requirements. I will probably continue to build the constraint into the tables that I design.

 

With regard to SRIDs, I am more interested in projected reference systems (I know I said planar, my mistake). These would include the various forms of state plane, UTM and the like. These all have standard EPSG numbers but don't currently appear in sys.spatial_reference_systems. Books online indicates that you have to use "supported" SRIDs, or those that appear in sys.spatial_reference_systems. Many applications, such as parcel mapping and other planimetric functions, don't typically store data in geographic (lat/lon) coordinates. I guess I'm curious if there are any plans to expand the list of supported SRIDs to include EPSG-defined projects CRSs. I think it will enhance the utility of the platform. The option at the moment is to store such geometry with an undefined SRS, which has problems of its own.

 

Or will there be the ability add new SRS definitions to the view? Just trying to sort out different potential modes of operation.

 

Thanks much,

 

Bill

 

 

Answer 3

Bill,

 

I think we're suffering from a slight confusion in terms: by planar I mean coodinates on the plane, including systems like state plane and UTM that are projections to the plane.

 

The crux of the issue from our perspecitve is that we don't want to dictate information that we don't have to.  For geography, we need to know about the SRID since it carries information about the shape of space, and so we build it in.  If a user wants to create a table to store similar data about their planar systems they are free to do so.  We would recommed EPSG numbering.

One thing we will probably do in conjunction with best practice documentation is provide a set of stored procedures that may make managing these a bit easier.

 

Cheers,

-Isaac

Answer 4

Isaac,

 

Thanks. We were indeed talking about the same thing. I think that clears up my understanding of where you're going. I appreciate your help in sorting that out.

 

Again, keep up the good work and I'll be watching with great interest.

 

Bill

Answer 5

Isaac: What's the point of the unit_of_measure and unit_conversion_factor columns in sys.spatial_reference_systems if it only lists geographic coordinate systems? There are a few rows in there that uses a value different than meters/1.0, but I don't really see the point in that. The only length related thing is the datum size in the WKT, and that is always specified in meters.

Answer 6

The unit of measure is useful because it specifies the unit that is output by scalar methods such as Length and Distance, and unit_of_measure^2 for Area.

 

For example, 4326 will return meters while 4241 will return Clarke's Feet:

select geography::STGeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)', 4326).STLength() --318.062188147241 m

select geography::STGeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)', 4241).STLength() --1043.52285405615 Clarke's feet

 

Answer 7

Wow I must say that's really weird. This is a spherical coordinate system that doesn't have anything to do with linear units, and yet it assumes a length unit based on which geographic spatial reference you are using???

 

The problem with the previous is that you can't rely on the result you are getting from STLength because it will depend on the spatial reference which doesn't make any sense in a non-projected SR anyway.

 

In my mind you would have to specify the output unit in the select, for instance:

select geography::STGeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)').STLength(9001)

(9001 is the EPSG ID for metre)

 

Of course we now get the whole discussion on whether STLength is following OGC standards or not, but MS already made it clear that they are going their own way when it comes to the geography type. Besides the STLength on geography is already not according to OGC either. The spec says "The length of the curve in its associated spatial reference", and 'feet' is never part of a geographic spatial reference. Of course the euclidean distance in a spheric coordinate system doesn't make any sense, but I don't think it should assume a unit based on the SR.

It might be wiser not to prefix many of the geography methods with ST to make it clear that this is methods that are behaving different than OGC standards.

 

Instead in my mind it should be one of these (I also changed STGeomFromText, since this is also behaving different than OGC by swapping X and Y):

select geography::GeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)').Length(9001)

select geography::GeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)').Length(1.0)

select geography::GeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)').Length('Metre')

select geography::GeomFromText('LINESTRING (40.759017 -73.979573, 40.761256 -73.977224)').Length(geography::metre)