How to operate with a MSSQL geography field

Questions / Help
, ,
1

Hi,

I’m really new to the elixir / phoenix world, but I’m trying to migrate an old backend from C#/MSSQl to Elixir/Phoenix. I will try to do it step by step, first migrating all the functionalities using the current production MSSQL database, and then, as a second step migrating the database engine to PostgreSQL. I know it will be very difficult due to my little knowledge and experience with Elixir, but I will give it a try and I’m really excited. Unfortunately, now I am on the first stone of the road and I am quite blocked.

In my database, I have a table with a ‘geography’ field (populated with the Google Maps API, and this is how it looks on the table: 0xE6100000010C010000201D0D4640FEFFFF1FEEA52340). Following the topic Can't load Postgrex custom types with Ecto 3.0, I was reviewing the geo_postgis project (https://github.com/bryanjos/geo_postgis) but if I’m not wrong, it’s only for PostgreSQL. Anyway I tried to use it and set the field as ‘Geo.PostGIS.Geometry’ in my schema, but when I try to load the data from my current production database I get this error:

[error] Task #PID <0.1059.0> started from #PID <0.1064.0> ending
** (FunctionClauseError) no matching function clause in Tds.Types.decode_info / 1
(tds 2.1.3) lib / tds / types.ex: 162: Tds.Types.decode_info (<< 0, 9, 103, 0, 101, 0, 111, 0, 103, 0, 114, 0, 97, 0, 112, 0, 104, 0, 121, 0, 133, 0, 77, 0, 105, 0, 99, 0, 114, 0, 111, 0, 115, 0, 111, 0, 102, 0, 116, 0, 46, 0, 83, 0, 113, 0, 108, 0, 83, 0, … >>)
(tds 2.1.3) lib / tds / tokens.ex: 516: Tds.Tokens.decode_column / 1
(tds 2.1.3) lib / tds / tokens.ex: 511: Tds.Tokens.decode_columns / 3
(tds 2.1.3) lib / tds / tokens.ex: 108: Tds.Tokens.decode_colmetadata / 2
(tds 2.1.3) lib / tds / tokens.ex: 47: Tds.Tokens.decode_tokens / 2
(tds 2.1.3) lib / tds / messages.ex: 96: Tds.Messages.parse / 3
(tds 2.1.3) lib / tds / protocol.ex: 507: Tds.Protocol.decode / 2
(stdlib 3.12) timer.erl: 166 :: timer.tc/1
(tds 2.1.3) lib / tds / protocol.ex: 876: Tds.Protocol.msg_send / 2
(tds 2.1.3) lib / tds / protocol.ex: 600: Tds.Protocol.send_prepare / 3
(db_connection 2.3.1) lib / db_connection / holder.ex: 316: DBConnection.Holder.holder_apply / 4
(db_connection 2.3.1) lib / db_connection.ex: 1257: DBConnection.prepare / 4
(db_connection 2.3.1) lib / db_connection.ex: 1250: DBConnection.run_prepare / 4
(db_connection 2.3.1) lib / db_connection.ex: 1262: DBConnection.run_prepare_execute / 5
(db_connection 2.3.1) lib / db_connection.ex: 1359: DBConnection.run/6
(db_connection 2.3.1) lib / db_connection.ex: 557: DBConnection.parsed_prepare_execute / 5
(db_connection 2.3.1) lib / db_connection.ex: 550: DBConnection.prepare_execute / 4
(ecto_sql 3.5.3) lib / ecto / adapters / sql.ex: 692: Ecto.Adapters.SQL.execute! / 4
(ecto_sql 3.5.3) lib / ecto / adapters / sql.ex: 684: Ecto.Adapters.SQL.execute / 5
(ecto 3.5.5) lib / ecto / repo / queryable.ex: 229: Ecto.Repo.Queryable.execute / 4
Function: &: erlang.apply / 2
Args: [#Function <8.81890471 / 1 in Ecto.Repo.Preloader.maybe_pmap / 3>, [#Function <19.81890471 / 1 in Ecto.Repo.Preloader.prepare_queries / 6>]]
[info] Tds.Protocol (#PID <0.1027.0>) disconnected: ** (DBConnection.ConnectionError) client #PID <0.1063.0> exited

Right now, I’m in a dead end due my few knowlgendment about Elixir.
Could anyone help me and add some light? I’ll really apreciate it.

Thanks to read me!

2

I have a similar error trying to interact with the Image type:

** (exit) an exception was raised:
    ** (FunctionClauseError) no function clause matching in anonymous fn/2 in Tds.Types.decode_info/1
        (tds 2.3.0) lib/tds/types.ex:368: anonymous fn(1, <<11, 0, 116, 0, 98, 0, 108, 0, 83, 0, 116, 0, 117, 0, 100, 0, 101, 0, 110, 0, 116, 0, 115, 0, 7, 80, 0, 105, 0, 99, 0, 116, 0, 117, 0, 114, 0, 101, 0, 0, 0, 0, 0, 9, 0, 167, 20, 0, 9, 4, ...>>) in Tds.Types.decode_info/1
        (elixir 1.13.4) lib/enum.ex:4136: Enum.reduce_range/5
        (tds 2.3.0) lib/tds/types.ex:365: Tds.Types.decode_info/1
        (tds 2.3.0) lib/tds/tokens.ex:514: Tds.Tokens.decode_column/1
        (tds 2.3.0) lib/tds/tokens.ex:509: Tds.Tokens.decode_columns/3
        (tds 2.3.0) lib/tds/tokens.ex:108: Tds.Tokens.decode_colmetadata/2
        (tds 2.3.0) lib/tds/tokens.ex:47: Tds.Tokens.decode_tokens/2
        (tds 2.3.0) lib/tds/messages.ex:102: Tds.Messages.parse/3
        (tds 2.3.0) lib/tds/protocol.ex:481: Tds.Protocol.decode/2
        (tds 2.3.0) lib/tds/protocol.ex:568: Tds.Protocol.send_prepare/3
        (db_connection 2.4.2) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
        (db_connection 2.4.2) lib/db_connection.ex:1349: DBConnection.prepare/4
        (db_connection 2.4.2) lib/db_connection.ex:1342: DBConnection.run_prepare/4
        (db_connection 2.4.2) lib/db_connection.ex:1354: DBConnection.run_prepare_execute/5
        (db_connection 2.4.2) lib/db_connection.ex:1459: DBConnection.run/6
        (db_connection 2.4.2) lib/db_connection.ex:595: DBConnection.parsed_prepare_execute/5
        (db_connection 2.4.2) lib/db_connection.ex:587: DBConnection.prepare_execute/4
        (ecto_sql 3.8.1) lib/ecto/adapters/sql.ex:855: Ecto.Adapters.SQL.execute!/5
        (ecto_sql 3.8.1) lib/ecto/adapters/sql.ex:847: Ecto.Adapters.SQL.execute/6
        (ecto 3.8.3) lib/ecto/repo/queryable.ex:221: Ecto.Repo.Queryable.execute/4

@ernestfolch did you happen to ever figure this out?

3

Hi @markquezada, I’m so sorry but I didn’t find a way to deal with it.

Good Luck!
Ernest

4

Beside using Geo.PostGIS.Geometry in your schema, you also need to register the datatypes. However, the Geo.PostGIS library is built to work with Postgrex, and to encode and decode geography/geometry data for a PostGIS enabled Postgres database, and expose PostGIS functions. I wouldn’t expect inter-op with MSSQL. For one thing, I think the data formats are different. From the MSSQL docs:

The geography spatial data type, geography , is implemented as a .NET common language runtime (CLR) data type in SQL Server.

whereas in PostGIS:

[…] PostGIS defined extended EWKB and EWKT formats. They provide 3D (XYZ and XYM) and 4D (XYZM) coordinate support and include SRID information. Including all geometry information allows PostGIS to use EWKB as the format of record (e.g. in DUMP files).

Under the hood Geo.PostGIS uses Geo.WKB.decode!/1 to decode binary data from geometry/geography columns in PostGIS. With valid EWKB data that looks like this

iex> Geo.WKB.decode!("0101000000000000000000F03F000000000000F03F")
%Geo.Point{coordinates: {1.0, 1.0}, properties: %{}, srid: nil}

With the binary data from your MSSQL geography column it raises an error.

iex> Geo.WKB.decode!("0xE6100000010C010000201D0D4640FEFFFF1FEEA52340")
** (FunctionClauseError) no function clause matching in Geo.WKB.Decoder.decode/1

@markquezada, can you talk a little more about the context, for instance the database, extensions, and libraries you’re using, and some code samples? Might be better to open that in a new topic with a title that’s descriptive of your specific issue, since the domain seems different than @ernestfolch 's issues with geospatial data.