Inserting large polygons

[jeroenbourgois]

We are migrating an existing PHP/MySQL web application to Elixir/Phoenix. A part of the application deals with geometry and polygons. After hitting some bumps we got most of it working. We could create and edit clusters (Polygon) in the app. However, once we imported the existing dataset we got errors parsing the Polygon data in mariaex. It seems like whenever the polygon gets too big, the pattern matching for the row parsers fails, and we fail to understand why 😄

So then I cloned the repo here and update the inserts polygon test, and I was able to replicate the error.

Executing this test:

polygon = %Mariaex.Geometry.Polygon{
  coordinates: [
    [ {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0}, {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0}, {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0}, {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0}, {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0}, {0.0, 0.0}, {10.0, 0.0}, {10.0, 10.0}, {0.0, 10.0}, {0.0, 0.0} ]
    ],
    srid: 0
}

Results in the following error:

 ** (FunctionClauseError) no function clause matching in Mariaex.RowParser.decode_geometry/6

     The following arguments were given to Mariaex.RowParser.decode_geometry/6:

         # 1
         <<252, 241, 1, 42, 0, 0, 0, 1, 3, 0, 0, 0, 1, 0, 0, 0, 30, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 36, 64, 0, 0, 0, 0, 0, 0, ...>>

         # 2
         []

         # 3
         0

         # 4
         []

         # 5
         :structs

         # 6
         Poison

Attempted function clauses (showing 3 out of 3):

defp decode_geometry(<<25::integer()-little()-size(8), srid::integer()-little()-size(32), 1::integer()-little()-size(8), 1::integer()-little()-size(32), x::float()-little()-size(64), y::float()-little()-size(64), rest::bitstring()>>, fields, null_bitfield, acc, datetime, json_library)
defp decode_geometry(<<_len::integer()-little()-size(8), srid::integer()-little()-size(32), 1::integer()-little()-size(8), 2::integer()-little()-size(32), num_points::integer()-little()-size(32), points::binary()-unit(128)-size(num_points), rest::bitstring()>>, fields, null_bitfield, acc, datetime, json_library)
defp decode_geometry(<<_len::integer()-little()-size(8), srid::integer()-little()-size(32), 1::integer()-little()-size(8), 3::integer()-little()-size(32), num_rings::integer()-little()-size(32), rest::bitstring()>>, fields, null_bitfield, acc, datetime, json_library)

Can you hint us in the right direction? It is not always the amount of points that seems to cause the issue, sometimes the precision makes a difference too. We checked the polygons to be valid using the MySQL validation functions but all seems fine.

Thank you in advance!

[pierot]

I have been testing this as well and have found a fix, but I don't understand why ..

The function definition that doesn't match, and should is:

defp decode_geometry(<<_len::integer()-little()-size(8), srid::integer()-little()-size(32), 1::integer()-little()-size(8), 3::integer()-little()-size(32), num_rings::integer()-little()-size(32), rest::bitstring()>>, fields, null_bitfield, acc, datetime, json_library)

I saw that more complex polygons generates a different bitstring:

Complex:

    polygon = %Mariaex.Geometry.Polygon{
      coordinates: [
        [
          {4.375889897346497, 51.20387196361823},
          {4.381211400032043, 51.19935444755919},
          {4.393399357795715, 51.19709552339094},
          {4.399235844612122, 51.19515921451808},
          {4.40902054309845, 51.19257734276737},
          {4.416401982307434, 51.19182426958834},
          {4.426015019416809, 51.19300766477249},
          {4.434083104133606, 51.19903175088785},
          {4.438546299934387, 51.20494749738101},
          {4.445756077766418, 51.21312073325066},
          {4.449189305305481, 51.21849707104884},
          {4.449532628059387, 51.22226013396642},
          {4.44197952747345, 51.22763540452407},
          {4.430821537971497, 51.2349447650688},
          {4.424641728401184, 51.23698687887106},
          {4.421551823616028, 51.2403185541701},
          {4.410393834114075, 51.24128576954669},
          {4.40352737903595, 51.24171563651943},
          {4.397862553596497, 51.23924384656603},
          {4.399750828742981, 51.23311753378148},
          {4.398205876350403, 51.2251628580361},
          {4.375889897346497, 51.20387196361823}
        ]
      ],
      srid: 0
    }

Generates:

<<252, 113, 1, 0, 0, 0, 0, 1, 3, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 0, 0, 0, 72,
  233, 128, 17, 64, 226, 39, 252, 121, 24, 154, 73, 64, 255, 255, 255, 71, 92,
  134, 17, 64, 24, 74, 80, 114, 132, 153, ...>>

A simpler one:

    polygon = %Mariaex.Geometry.Polygon{
      coordinates: [
        [
          {4.375889897346497, 51.20387196361823},
          {4.381211400032043, 51.19935444755919},
          {4.399750828742981, 51.23311753378148},
          {4.398205876350403, 51.2251628580361},
          {4.375889897346497, 51.20387196361823}
        ]
      ],
      srid: 0
    }

Generates:

<<97, 0, 0, 0, 0, 1, 3, 0, 0, 0, 1, 0, 0, 0, 5, 0, 0, 0, 0, 0, 0, 72, 233, 128,
  17, 64, 226, 39, 252, 121, 24, 154, 73, 64, 255, 255, 255, 71, 92, 134, 17,
  64, 24, 74, 80, 114, 132, 153, 73, 64, ...>>

In the complex one adds 2 extra parts a the start 113, 1

I created a different definition for decode_geometry that catches these parts and makes the extended tests pass.

Any ideas?