Support for spatial data with MySQL?

hanc2006 commented 6 years ago

Steps to reproduce

dotnet ef dbcontext scaffold "server=xxx;port=3306;database=test;user id=xxx;password=xxx;sslmode=none" "Pomelo.EntityFrameworkCore.MySql" -o Model -c "Db" --verbose

Target Framework

netcoreapp2.0

The issue

No mapping column for mysql geometry type.

Project configuration

``

netcoreapp2.0 Exe

``

Exceptions

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:01.13
dotnet exec --depsfile C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\bin\Debug\netcoreapp2.0\CatchMe.Telegram.deps.json --additionalprobingpath C:\Users\hanc\.nuget\packages --additionalprobingpath "C:\Program Files\dotnet\sdk\NuGetFallbackFolder" --runtimeconfig C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\bin\Debug\netcoreapp2.0\CatchMe.Telegram.runtimeconfig.json "C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.entityframeworkcore.tools.dotnet\2.0.1\tools\netcoreapp2.0\ef.dll" dbcontext scaffold "server=xxxx;port=3306;database=test;user id=xxx;password=xxx;sslmode=none" Pomelo.EntityFrameworkCore.MySql -o Model -c Db --assembly C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\bin\Debug\netcoreapp2.0\CatchMe.Telegram.dll --startup-assembly C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\bin\Debug\netcoreapp2.0\CatchMe.Telegram.dll --project-dir C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\ --verbose --root-namespace CatchMe.Telegram
Using assembly 'CatchMe.Telegram'.
Using startup assembly 'CatchMe.Telegram'.
Using application base 'C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\bin\Debug\netcoreapp2.0'.
Using working directory 'C:\Users\hanc\Documents\VSCodes\Catchme.Telegram'.
Using root namespace 'CatchMe.Telegram'.
Using project directory 'C:\Users\hanc\Documents\VSCodes\Catchme.Telegram\'.
Finding design-time services for provider 'Pomelo.EntityFrameworkCore.MySql'...
Using design-time services from provider 'Pomelo.EntityFrameworkCore.MySql'.
Finding IDesignTimeServices implementations in assembly 'CatchMe.Telegram'...
No design-time services were found.
column.

Could not find type mapping for column 'boundary.area' with data type 'geometry'. Skipping

Further technical details

MySQL version: 5.7.20 Operating system: Ubuntu Server 17.10 Pomelo.EntityFrameworkCore.MySql version: 2.0.1

Other details about my project setup:

caleblloyd commented 6 years ago

First, EF Core upstream needs to support it: https://github.com/aspnet/EntityFrameworkCore/issues/1100 Next, MySqlConnector needs to support it: https://github.com/mysql-net/MySqlConnector/issues/70 There's also conversations about adding support in CoreFX, which would hopefully make it's way into ADO.NET: https://github.com/dotnet/corefx/issues/12034

Once EF Core settles on a way to do it, we'll look into it. In the meantime, I recommend you add your requirements to the discussion at https://github.com/aspnet/EntityFrameworkCore/issues/1100

csboling commented 6 years ago

Just a heads up that MySqlConnector now supports mapping geometry types to byte[] to match the behavior of the first-party connector. Currently when attempting to query any geometry column, Pomelo.EntityFrameworkCore is throwing:

MySql.Data.MySqlClient.MySqlException (0x80004005): Failed to read the result set. ---> System.NotImplementedException: ConvertToMySqlDbType for Geometry is not implemented
   at MySqlConnector.Core.TypeMapper.ConvertToMySqlDbType(ColumnDefinitionPayload columnDefinition, Boolean treatTinyAsBoolean, Boolean oldGuids) in C:\projects\mysqlconnector\src\MySqlConnector\Core\TypeMapper.cs:line 268
   at MySqlConnector.Core.ResultSet.<ReadResultSetHeaderAsync>d__1.MoveNext()
   at MySql.Data.MySqlClient.MySqlDataReader.ActivateResultSet(ResultSet resultSet) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 92
   at MySql.Data.MySqlClient.MySqlDataReader.<ReadFirstResultSetAsync>d__88.MoveNext() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlDataReader.cs:line 324

Not sure if there are other obstacles to bumping the version of MySqlConnector as part of the 2.1.0 release.

jamsoft commented 6 years ago

It looks like EF Core support is about to drop in 2.2 preview 3.

Yrlec commented 5 years ago

What's the status of this issue? I'll gladly chip but I haven't worked with this before so I'd appreciate some pointers on where to start.

mguinness commented 5 years ago

@csboling Since you seem to have experience with GIS would you be able to give Yrlec any pointers? Part of the work I assume would be creating translators for the Geometry Format Conversion Functions in MySQL?

csboling commented 5 years ago

Yeah, now that EF officially supports using NetTopologySuite data types for this I think it would probably be straightforward to write a package for this by following e.g. Npgsql.EFCore's example. I think the ST_xxx geometry functions are a standard SQL extension so a lot of this code is probably almost copy-n'-paste.

vivet commented 5 years ago

Is somebody working on this issue, otherwise maybe I can do it tomorrow?

caleblloyd commented 5 years ago

It is not being actively worked on to my knowledge

On Fri, Jun 14, 2019, 11:23 Michael Vivet notifications@github.com wrote:

Is somebody working on this issue, otherwise maybe I can do it tomorrow?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/488?email_source=notifications&email_token=AASNR5PUUTIXXCLXOGF4KRDP2OZVVA5CNFSM4EQRJSX2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODXXDSQY#issuecomment-502151491, or mute the thread https://github.com/notifications/unsubscribe-auth/AASNR5OS32MPXUU5QZCRLX3P2OZVVANCNFSM4EQRJSXQ .

theezz commented 5 years ago

Is this in developing or shall we do it now?

vivet commented 5 years ago

I haven't started much yet. Real life came in the way. So feel free to start it yourself, cause it will be a few weeks before I get some time.

karanjilka commented 5 years ago

I am new to the .net core world, so is there any workaround for this for now? until it is being developed. I tried MySqlGeometry but it seems it is only storing POINT, I want to store POLYGON and LINESTRING

petersontubini commented 5 years ago

I can't believe that nobody needed that until today at Dotnet Core :(

bgrainger commented 5 years ago

By copy/pasting EFCore.SQLite.NTS I was able to get a simple provider up and running pretty quickly (it's not published to NuGet). There is a bug in this library that prevents plugins from loading properly; it's now fixed but not shipped: 760d9efdabf8e1667a0a4947c2d487be6fb9437b

I'm currently planning to wait and find out how many changes are coming in 3.0 before deciding whether to finish this for 2.2 or just wait and ship it as part of 3.0.

petersontubini commented 5 years ago

At my case I just need a simple way to calculate the distance between two points and then I went with raw sql for that using MySqlCommand. Not the best solution at all but it's working fine. But of course would be lovely see a NTS for Mysql. You're a life saver @bgrainger happy to know that you're working on that

rdecarreau commented 5 years ago

This is kind of a showstopper for me. @bgrainger do you have any more information regarding your 2.2 versus 3.0 release? Anything I can do to help (i.e. use your copy/paste version in my tests)?

yasir095 commented 4 years ago

Whats the status on this. No support for Point data type. Is there a solution that I can use.

lauxjpn commented 4 years ago

I am going to push the spatial support PR next week. I already have something working, just needs some minor refinements.

YYCMZERO commented 4 years ago

@lauxjpn ,are you push the spatial support?

lauxjpn commented 4 years ago

@YYCMZERO Yeah, over the next few days. Probably not before the weekend.

YYCMZERO commented 4 years ago

@lauxjpn ,how to use Geometry type data in mysql ?

VanSunDe commented 4 years ago

Sry I don’t know

发件人: noreply@github.com noreply@github.com 代表 MZERO 发送时间: 2020年6月8日 14:25 收件人: PomeloFoundation/Pomelo.EntityFrameworkCore.MySql Pomelo.EntityFrameworkCore.MySql@noreply.github.com 抄送: Subscribed subscribed@noreply.github.com 主题: Re: [PomeloFoundation/Pomelo.EntityFrameworkCore.MySql] Support for spatial data with MySQL? (#488)

@lauxjpn https://github.com/lauxjpn ,how to use Geometry type data in mysql ?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/488#issuecomment-640395207 , or unsubscribe https://github.com/notifications/unsubscribe-auth/AHZSBBFR7ECJQSNUNAIEH2LRVR733ANCNFSM4EQRJSXQ . https://github.com/notifications/beacon/AHZSBBAJIUKR32MTPBEFT5LRVR733A5CNFSM4EQRJSX2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEYV2PRY.gif

lauxjpn commented 4 years ago

@VanSunDe, @YYCMZERO, @yasir095: See #1097

mguinness commented 4 years ago

~~Pomelo.EntityFrameworkCore.MySql 3.2.0-preview.20311.1~~ (edited by @lauxjpn) is available to test using the nightly build.

lauxjpn commented 4 years ago

@mguinness I haven't tested this yet, but I believe we still need to create a dedicated package for the NTS project.

lauxjpn commented 4 years ago

Spatial support is now available (when enabling preview versions in your project/solution) using our nightly build feed.

The first nightly build release containing the Pomelo.EntityFrameworkCore.MySql.NetTopologySuite is version 3.2.0-preview.20313.3.

lauxjpn commented 4 years ago

To effectively use scaffolding and spatial types together, you have to make sure, that the spatial support related services from the NTS package have been registered before the scaffolder runs.

The simplest way to do this, is to add the following class to your project (the class name is arbitrary):

public class EFCoreDesignTimeService : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
        => serviceCollection.AddEntityFrameworkMySqlNetTopologySuite();
}

micahosborne commented 4 years ago

var factory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
var point = factory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(location.Lng,location.Lat));
location.Coordinates = point;

location is the model class for the database table. When i save it and try to run a query i get the following error. (But only when the column has data in it). If i set the values to null, the query executes. Is there another way to create these coordinates so it will work?

This creates the error. There's no spatial reference system with SRID 4294967295.

PomeloFoundation / Pomelo.EntityFrameworkCore.MySql