Elixir / phoenix / sqlite / spatialite error: tracks.geom violates geometry constraint [geom-type or srid not allowed]

Phoenix Forum Questions / Help
, ,
1

Hi,

I’m not sure if this is the appropriate forum for my question. It’s a hybrid question involving Elixir, Phoenix, SpatiaLite, and SQLite. Perhaps someone can point me in the right direction.

Firstly, here’s the error I’m encountering:

error: tracks.geom violates geometry constraint [geom-type or srid not allowed]

Basically trying to get this

%Geo.MultiLineStringZ{
  coordinates: [
    [
      {18.68056, 54.695338, 15.6},
      {18.68061, 54.695331, 15.6},
      {18.68069, 54.695339, 15.6},
      {18.680783, 54.695356, 15.8},
      {18.680868, 54.695368, 15.8},
      {18.680947, 54.695379, 15.8},
      {18.681009, 54.695388, 15.8},
      {18.675521, 54.692955, 22.4}
    ]
  ],
  srid: 3857,
  properties: %{}
}

Into a spatialite GIS table. Created a SpatiaLite SQLite DB a la

  def change do
    create table(:tracks) do
      add :name, :string

      timestamps(type: :utc_datetime)
    end

    execute("SELECT AddGeometryColumn('tracks', 'geom', 3857, 'GEOMETRYZ', 'XYZ');")
  end

I have also tried using MULTILINESTRINGZ, LINESTRING, and other combinations (e.g., SRID 4326). At this point, I feel like I’m just guessing.

I’ve experimented with Ecto.type, but I always get the same error:

error: tracks.geom violates geometry constraint [geom-type or srid not allowed]

I haven’t come across anything like Ecto.Adapters.Spatialite. Postfix/PostGIS seems more straightforward, but for some reason, I’m fixated on SpatiaLite/SQLite.

I even contemplated using:

field(:geom, Geo.PostGIS.Geometry)

But that led to other issues. It seems I might not be creating the column correctly? A direct insert in SpatiaLite gives the same error.

When trying:

spatialite>  INSERT INTO tracks (geom, inserted_at, updated_at) VALUES (X'00A000000500000F11000000010080000002000000084032AE392E1EF73C404B5900D5E8D541402F3333333333334032AE3C74FB54A0404B59009B30728F402F3333333333334032AE41B328B6D8404B5900DE4C5111402F3333333333334032AE47CB70AC3B404B59016CE789E7402F99999999999A4032AE4D5D80E497404B5901D19157AC402F99999999999A4032AE528AE74F2F404B59022DD7A9A0402F99999999999A4032AE569B17481B404B5902795703F3402F99999999999A4032ACEEF1BAC2DF404B58B2BFDB4CC24036666666666666', '2023-10-17', '2023-10-17');

I receive the error:

Error: tracks.geom violates Geometry constraint [geom-type or SRID not allowed]

And when I do manage to insert something, like:

spatialite> INSERT INTO tracks (geom, updated_at, inserted_at) VALUES (st_geomfromtext('multilinestring((10.02 20.01, 10.32 23.98, 11.92 25.64), (9.55 23.75, 15.36 30.11))', 3857), DATETIME('now'), DATETIME('now'));

spatialite> INSERT INTO tracks (geom, inserted_at, updated_at) VALUES (GeomFromText('MULTILINESTRINGZ((18.68056 54.695338 15.6,18.68061 54.695331 15.6,18.68069 54.695339 15.6,18.680783 54.695356 15.8,18.680868 54.695368 15.8,18.680947 54.695379 15.8,18.681009 54.695388 15.8,18.675521 54.692955 22.4))', 3857), DATETIME('now'), DATETIME('now')

( I have encoded WKB and WKT in there too just not listed )

The data doesn’t seem to be retrievable correctly:

iex(90)> t = GPX.get_track!(2)
[debug] QUERY OK source="tracks" db=0.4ms idle=1333.8ms
SELECT t0."id", t0."name", t0."geom", t0."inserted_at", t0."updated_at" FROM "tracks" AS t0 WHERE (t0."id" = ?) [2]
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:376
%GPX.Track{
  __meta__: #Ecto.Schema.Metadata<:loaded, "tracks">,
  id: 2,
  name: nil,
  geom: <<0, 1, 17, 15, 0, 0, 154, 153, 153, 153, 153, 25, 35, 64, 195, 245, 40,
    92, 143, 2, 52, 64, 184, 30, 133, 235, 81, 184, 46, 64, 92, 143, 194, 245,
    40, 28, 62, 64, 124, 5, 0, 0, 0, 2, 0, 0, ...>>,
  inserted_at: ~U[2023-10-18 02:18:56Z],
  updated_at: ~U[2023-10-18 02:18:56Z]
}

iex(91)> t.geom |> Geo.WKT.decode
{:error,
 %FunctionClauseError{
   module: Geo.WKT.Decoder,
   function: :do_decode,
   arity: 2,
   kind: nil,
   args: nil,
   clauses: nil
 }}

iex(92)> t.geom |> Geo.WKB.decode
{:error,
 %FunctionClauseError{
   module: Geo.WKB.Decoder,
   function: :do_decode,
   arity: 4,
   kind: nil,
   args: nil,
   clauses: nil
 }}

I’m at a bit of a loss. I’m neither a SpatiaLite/GIS expert nor proficient in Elixir/Phoenix, so any assistance would be greatly appreciated.

Please let me know if I haven’t provided sufficient information or if any part of my query isn’t clear.

Thanks!

2

Sorry that nobody chimed in, have you worked something out?

If all else fails I’d just opt for raw SQL statements and add a parse / reformatting stage in my own code.

3

Thanks for the reply. I meant to reply myself but I guess I forgot

I did manage to at least work something out and you are totally right, had to resort to basically raw SQL for insert

 def build_sql() do
    """
    INSERT INTO tracks(geom, name, updated_at, inserted_at) VALUES (SetSRID(GeomFromText(?), 3857), ?, DATETIME('now'), DATETIME('now'));
    """
  end

You can still use fragments for selects so thats cool

  def get_geom_as_geojson!(id) do
    id = String.to_integer(id)

    from(t in Track,
      where: t.id == ^id,
      select: fragment("AsGeoJSON(?)", t.geom)
    )
  end

Not the most elegant solution but gets the job done for what it is.

SQLiteGIS maybe one day

1 Like