EF Core : How can I Create Geometry by Parsing Spatial Data

Question

I had a requirement of storing static spatial data with some polygons, I imported GeoJson into my SQL Server to massage secondary data, once I had the correct data I exported my data into a Json (using FOR JSON PATH).

Now since geometry is a CLR type, I needed to convert it to string to allow export to JSON.

SELECT 
    Id, Bounds.ToString() as Bounds 
FROM
    ...
FOR JSON PATH

Now this produced a JSON file to the effect of

  [{
    "Id": 1,
    "Bounds": "POLYGON ((93.044663537224707 23.410524583651519, ...
  },
  {
    "Id": 2,
    "Bounds": "MULTIPOLYGON (((93.046192725017278 23.666228900665988...
  },

I need to use this for seeding initial static data, which will be loaded from but I do not know how to convert string Geometry to NetTopology Geometry.

Where did I go or and what am I missing? Is there a better way for me to seed this data? Or is there a way to parse the text I have in EF Core to populate the geometry?

My lengthy answer (below) became irrelevant with WKTReader. – Imran Shaik Aug 27 '20 at 19:08 — Imran Shaik, Aug 27 '20 at 19:08

score 1 · Answer 1 · answered Aug 27 '20 at 18:55

I have found an answer by researching further and I am also done with testing so thought I would share with anyone in similar boat.

The way to parse string to Geometry is by using GMLReader

But for that we to Generate Gml instead of string

SELECT 
Id, Bounds.AsGml() as Bounds 
FROM
    ...

This produces Xml on SQL Server

  <Bounds>
    <Polygon xmlns="http://www.opengis.net/gml">
      <exterior>
        <LinearRing>
          <posList>93.044663537224707 23.410524583651519 92.9467955185...

But this only solves half of my problem, I needed to generate JSON and use that JSON in seeding, but if I were flexible in using XML instead I could have exported by seed data with XML

SELECT 
  [Id],
  [Bounds].AsGml() as Bounds
  ...
FROM ...
FOR XML PATH

Would have produced an XML with the data like..

<row>
  <Id>1</Id>
  <Bounds>
    <Polygon xmlns="http://www.opengis.net/gml">
      <exterior>
        <LinearRing>
          <posList>93.044663537224707 23.410524583651519 92.9467955185...

So I decided to encode the generated GML to Base64 to generate JSON, this Base64 would be decoded while parsing the geometry for seeding.

    CREATE FUNCTION [dbo].[Geometry_Encode_Gml_Base64]
    (
        @value geometry
    )
    RETURNS varchar(max)
    AS
    BEGIN
        DECLARE @source varbinary(max) = convert(varbinary(max), @value.AsGml())
        RETURN cast(N'' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
    END

And generated JSON by executing the query

SELECT 
  [Id],
  dbo.Geometry_Encode_Gml_Base64([Bounds]) as Bounds
  ...
FROM ...
FOR JSON PATH

Which produces JSON like,

[
  {
    "Id": 1,
    "Bounds": "\/\/48AFAAbwBsAHkAZwBvAG4AIAB4AG0AbABuAHMAPQAiAGgAdAB0AH..."
}, ...

Now while reading and seeding I can simply, decode the Base64 string and read it with GMLReader

GMLReader gmlReader = new GMLReader(NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326));

Geometry bounds = gmlReader.Read(DecodeBase64(jsonRow.Bounds))

NOTE : DecodeBase64 should use the encoding (Unicode/Utf8 etc) that your database uses to decode

WARNING : I encountered problem with loading the extracted GML with GMLReader after decoding because there was a problem with some illegal bytes that were added (SQL Server?) if you find the same problem see how to resolve if you have XmlException: Data at the root level is invalid. Line 1, position 1.

EF Core : How can I Create Geometry by Parsing Spatial Data

1 Answers1