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

harleyqmc

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 comments

/phoenix #ecto #sqlite3

0 645 2

2023-11-21 19:44:50 UTC

Showing Posts 1 to 2

Show Best Posts
Show All Posts (oldest first)
Show All Posts (newest first)

dimitarvp

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.

Post #1

harleyqmc

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

Post #2

— All posts loaded —