Binary format of Geometry data

Topics: User Forum
Nov 30, 2006 at 9:23 PM
Is there any point in using AsBinary()? Isn't the format that is stored in the DB already WKB? ...if not, is the format documented?

I know this might be something that would change at some point in the future.
Coordinator
Dec 1, 2006 at 2:33 AM
> Isn't the format that is stored in the DB already WKB?

Almost WKB. Today the geometry binary format is mostly WKB but extended with two bytes to store SRID. If those extra bytes are missing (ie. "real" WKB), SRID = -1 is assumed. AsBinary() returns WKB only, without the SRID value which is returned by... the SRID() function.

> I know this might be something that would change at some point in the future.

Exactly, some day that could change for a smaller and/or faster alternative (do you have some recommendation? :), so better we do not assume WKB is the internal storing format and do prefer using AsBinary() to dump WKB from database and the provided constructor functions *FromWKB() to load from WKB.

Best regards,
Ricardo Stuven.


Dec 1, 2006 at 4:33 AM
No I don't have any recommendations. I would think that before that happens, the next MSSQL200x is out, which will support geometries, so storing them in that native format would probably be sufficient.
At that time, (depending on the implementation MS comes up with) I would think that it would be sufficient to convert MsSqlSpatial to support all the great stuff the Microsoft might not do natively (which could likely be coordinate transformation, geoprocessing tools etc)

Btw. what happens if one geometry has one SRID, and its table has a different SRID? If this is enforced, I see no reason to store the SRID on the geometry.
Coordinator
Dec 1, 2006 at 11:32 AM
> Btw. what happens if one geometry has one SRID,
> and its table has a different SRID?
> If this is enforced, I see no reason to store
> the SRID on the geometry.

It's enforced by a constraint. But the reason to keep the SRID along with the geometry is to use it in a context out of the table or without reference it explicitly. For example:

SELECT ST.Transform(the_geom, 32718) FROM mytable
In this case, you just need to specify the target SRID, the source SRID is implicit, but not because "mytable" is referenced. Indeed there is no way for the Transform function to know what is the source table of the geometry, therefore there's not way to know what is the source SRID either. Unless we include it in the geometry.

Another example:
SELECT @geom1 = the_geom FROM mytable WHERE gid=100
 
...
 
SET @geom2 = ST.Transform(@geom1, 32718)
 
...
 
SET @srid2 = ST.SRID(@geom2)
The same as before, but even more clear. We need to keep the SRID as part of the geometry.

One last example:
SET @intersection = ST.Intersection(@geom1, @geom2)
Oops! "DifferentSridException... Operation on geometries with different SRIDs (32719 and 32718)".

Best regards,
Ricardo Stuven.
Dec 1, 2006 at 5:24 PM
Why is it I am seing either 6 or 10 extra bytes after the SRID in the returned byte array? Are there more useful info stored there?
Coordinator
Dec 4, 2006 at 4:44 PM
Sorry, where I said before "two bytes" should be "four bytes" (Int32). But no, it's no more than that: WKB (as serialized by NTS) + SRID (4 bytes integer). You can verify the details in the file EWKBWriter.cs
And could you provide here an hex dump of a geometry you are finding problematic, please?

Best regards,
Ricardo Stuven.