convert gml:Polygon to SQL geometry hex string with c#

Question

I am trying to convert gml:Polygon to SQL Server Geometry datatype using Microsoft.SqlServer.Types in C# only, so no SQL server is available for this task, in the following example I was using MSSQL server only for testing purposes. What I did already is this:

string str = "<gml:Polygon xmlns:gml=\"http://www.opengis.net/gml\"><gml:exterior><gml:LinearRing><gml:posList>1 2 3 4 5 6 1 2</gml:posList></gml:LinearRing></gml:exterior></gml:Polygon>";

SqlXml str1 = new SqlXml(new MemoryStream(Encoding.UTF8.GetBytes(str)));

SqlGeometry a = SqlGeometry.GeomFromGml(str1, 4326);

writer.WriteString(a.STAsText().ToSqlString().ToString());

This will output:

POLYGON ((1 2, 3 4, 5 6, 1 2))

Now I need to convert it like this: As I execute this command in SSMS

select convert(geometry, 'POLYGON ((1 2, 3 4, 5 6, 1 2))')

And get the following result in the result pane so I can take it as a string:

0x00000000010004000000000000000000F03F00000000000000400000000000000840000000000000104000000000000014400000000000001840000000000000F03F000000000000004001000000020000000001000000FFFFFFFF0000000003

How I can achieve this?

Update

With SqlGeometry.GeomFromGml(str1, 4326) I am already having geometry datatype and there is another method: STAsBinary() which will give me a binary representation of the same geometry (WKB), but I don't know how to get SQL hex string from it.

I've tried this way:

var m = BitConverter.ToString(ba).Replace("-", "");

But the result is WKB representation.

@marc_s I've updated my question, need the output of select convert(geometry, 'POLYGON ((1 2, 3 4, 5 6, 1 2))') as a string — Dejan Dozet, Apr 09 '21 at 14:06
You probably have to "wrap" this first `Convert(geometry, ...)` into another `Convert(VARCHAR(n)`, Convert(Geometry, ....))` call — marc_s, Apr 09 '21 at 14:12
@marc_s thanks, I've updated my question even more. There is a STAsBinary method to get a binary representation of the geometry datatype, but I don't know how to get it as a string that I need. — Dejan Dozet, Apr 09 '21 at 14:27
Could you possibly use the `STAsText` method on the `Geometry` object? https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stastext-geography-data-type?view=sql-server-ver15 — marc_s, Apr 09 '21 at 14:28
already did it, but that will give me POLYGON ((1 2, 3 4, 5 6, 1 2)), look in the question — Dejan Dozet, Apr 09 '21 at 14:29

convert gml:Polygon to SQL geometry hex string with c#

0 Answers0