Sql-server – Storing Routes and Locations in Graph using SQL Server 2017

graphgraph-dbmssql serversql-server-2017

I'm very new to Graph database, so please ignore my mistakes. My scenario described in following picture:

enter image description here

I have different locations connected to each other with a route between them. Then I have many outlets linked to routes.

I created a RDBMS schema like this:

enter image description here

My goal is to give possible routes and outlets on them between two queried locations.

Recently I heard of Graph database support in SQL Server 2017 and read this article, but can't connect with my current problem.

Please help me to create Graph and node tables in SQL Server 2017.

Thanks!

Update

Now I create a data model:

Data Model

and some Node and Edge tables as follows:

CREATE TABLE [dbo].[mRoute](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mCity](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[mOutlet](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](500) NOT NULL,
    [Latitude] [decimal](11, 9) NULL,
    [Longitude] [decimal](11, 9) NULL,
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)ON [PRIMARY]
)
AS NODE ON [PRIMARY]
GO

CREATE TABLE [dbo].[LinkedTo]
AS EDGE ON [PRIMARY]
GO

CREATE TABLE [dbo].[On]
AS EDGE ON [PRIMARY]
GO

Now I am stuck on Between Edge Table, which contains route between two cities.

Best Answer

SQL Server's graph capabilities are -- how can I put this nicely -- limited. At the moment it is really just a little syntactic sugar in the new MATCH clause. There is no support for any serious graph-oriented algorithm. To achieve your goals you will end up using recursive CTEs, which do not play nicely with MATCH. While a solution is possible it will not be elegant. Sorry to be the bearer of bad news.

To learn about graph design & algorithms I would suggest starting with a specific graph DBMS. Neo4j springs to mind. Otherwise you will be spending all your time in work-arounds and never see the big picture.

Related Question