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.

  • Your scheme is a graph (non-oriented it seems). So use any pattern structure for this type of data. The most simple scheme consists from 2 tables: nodes (id, name, etc.) and connections (id, name, node1, node2, etc.), where the last table have 2 references to the first one. So the outlet is some 'etc.' property of a node and the city is its name or another 'etc.', and a route is a connections chain. – Akina Dec 4 at 6:03
  • The two edge tables have confusingly similar names. To give yourself the best chance I 'd suggest you fix that, and make them and the diagram consistent. – Michael Green Dec 4 at 12:22
  • You also need to clarify what sort of route calculations you need to do, and for what purposes. Route calculation and optimization are always done considering cost: distance or drive time. For that, the algorithms need a complete road graph where the road network is abstracted into nodes and links (or vertices and edges in graph parlance), where each node is an intersection and each link is a road segment connecting those intersections. Links have an associated cost (length or the typical time it takes to drive that link). – Albert Godfrind Dec 8 at 11:41
  • Routes between two locations are then computed using well-known algorithms - Dijkstra, A-Star ... and determine the best route (the one with the lowest cost - distance or drive time). Real-life route calculation engines will also consider traffic restrictions, via attributes associated with road segments: truck access, tunnel height, bridge maximum weight, legal access restrictions ... to compute routes based on the mode of transport (car, truck, bicycle, pedestrian ...). They also include variable costs (time) based on traffic statistics and time of day. Some can include real-time trafic. – Albert Godfrind Dec 8 at 11:45
  • Professional outing engines provide more capabilities: finding locations within a drive time, N closest neighbors, traveling-salesman problem (optimizing multi-stop routes), drive time polygons etc. Unfortunately, SQL Server provides none of that. Neither does Neo4J or any other property graph engines. Postgres (PostGIS) has a routing module (PGRouting). Also Oracle Spatial and Graph has a generic network engine ("Network Data Model") as well as a web service ("Oracle Router") that returns routes between destinations, including step-by-step directions. – Albert Godfrind Dec 8 at 11:52

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.