How to store additional information about each point of a SQL Server geography line string?

I am using SQL Server 2016 and I have a Trajectory table and a TrajectoryPoint table. A trajectory consists of many trajectory points, and a trajectory point is related to only one trajectory. Each trajectory point has many values such as location, timestamp, speed, SSR code and more.

This is the current trajectory table (simplified):

CREATE TABLE [dbo].[Trajectory](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TypeId] [tinyint] NOT NULL,
    [TimeOverFir] [time](7) NULL,
    [DistanceOverFir] [float] NULL,
    [FlightRules] [nvarchar](1) NULL,
    [LinkInfoId] [int] NULL,
    [StateId] [int] NOT NULL,
    [CO2Emission] [float] NULL,
    [FuelConsumption] [float] NULL,
    [Flight_Id] [int] NULL,
 CONSTRAINT [PK_dbo.Trajectory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the current trajectory point table (simplified):

CREATE TABLE [dbo].[TrajectoryPoint](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Location] [geography] NOT NULL, --*
    [Time] [datetime] NULL,
    [Speed] [real] NULL,
    [TrajectoryId] [int] NULL,
    [SsrCode] [char](4) NOT NULL,
    [TypeId] [tinyint] NOT NULL,
    [SsrModeId] [tinyint] NOT NULL,
 CONSTRAINT [PK_dbo.TrajectoryPoint] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[TrajectoryPoint]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.TrajectoryPoint_dbo.Trajectory_TrajectoryId] FOREIGN KEY([TrajectoryId])
REFERENCES [dbo].[Trajectory] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[TrajectoryPoint] CHECK CONSTRAINT [FK_dbo.TrajectoryPoint_dbo.Trajectory_TrajectoryId]
GO

Currently, the trajectory point table has a geography column (marked with * in above code) containing the latitude, longitude

All the points in your Geography have one common TrajectoryId, but each one of them has a relative position (first point, second point, ..., nth point).

I would consider that the natural key for this table is just a composite-key (TrajectoryId, PointNr), where PointNr is just 1 for the first point of the trajectory (LINESTRING) 2 for the second, and so on.

That is, the idea is to not use a TrajectoryPointId, but create the table like:

CREATE TABLE [dbo].[TrajectoryPoint]
(
    /* This is the natural key to a certain point */
    [TrajectoryId] [int] NOT NULL REFERENCES [dbo].[Trajectory] ([Id]),
    [PointNr] [int] NOT NULL,

    /* Add here all the attributes of every trajectory point */
    [Time] [datetime] NULL,
    [Speed] [real] NULL,
    [SsrCode] [char](4) NOT NULL,
    [TypeId] [tinyint] NOT NULL,
    [SsrModeId] [tinyint] NOT NULL,

    /* And add the 2-column PRIMARY KEY */
    PRIMARY KEY ([TrajectoryId], [PointNr])
) ;

There is no need to have an identity column in every table. They tend to be convenient, but they're not always the best choice.

This assumes that your Geography is just a list of points, obviously.

This is a practical case:

We insert some values into the Trajectory and TrajectoryPoint tables:

INSERT INTO Trajectory
    (Geography, TypeId, StateId)
VALUES 
  (Geography::STGeomFromText('LINESTRING (
     -71.8807  43.1500, 
     -71.8805  43.1497,
     -71.8803  43.1493)', 4269), 1, 1) ;

DECLARE @t AS Integer = @@IDENTITY ;

INSERT INTO TrajectoryPoint 
  (TrajectoryId, PointNr, Time, SSrModeId, SsrCode, TypeId)
VALUES 
  (@t, 1,  '2017-01-07 19:00:05', 1, 'ABCD', 0),
  (@t, 2,  '2017-01-07 19:00:15', 1, 'ABCD', 0),
  (@t, 3,  '2017-01-07 19:00:25', 1, 'ABCD', 0) ;

... and we would query the two tables using a somehow complicated procedure to get the points out of the LINESTRING:

-- We select here the trajectory we are interested in
WITH OneTrajectory AS
(
    SELECT 
        Id AS TrajectoryId, Geography AS G, G.STNumPoints() AS NrOfPoints
    FROM 
        Trajectory 
    WHERE 
        Id = @t
)

-- We get the list of Geometry Points out of the LineString in G
, GeometryPoints (TrajectoryId, PointNr, Point) AS  
( 
   SELECT T.TrajectoryId, 1, T.G.STPointN(1) 
     FROM OneTrajectory T
UNION ALL
   SELECT T.TrajectoryId, PointNr + 1, T.G.STPointN(PointNr + 1) 
     FROM OneTrajectory T, GeometryPoints GP
    WHERE PointNr < T.NrOfPoints
)

-- And we do the JOIN and retrieve whatever needed
SELECT 
    TP.TrajectoryId, TP.PointNr, GP.Point.STAsText() AS PointAsText, TP.time
FROM 
    GeometryPoints GP
    INNER JOIN TrajectoryPoint TP 
        ON TP.TrajectoryId = GP.TrajectoryId AND TP.PointNr = GP.PointNr ;

What you get is (slightly edited for readability)...

+--------------+---------+--------------------------+---------------------+
| TrajectoryId | PointNr |     PointAsText          |          time       |
+--------------+---------+--------------------------+---------------------+
|            3 |       1 | POINT (-71.8807 43.1500) | 2017-07-01 19:00:05 |
|            3 |       2 | POINT (-71.8805 43.1497) | 2017-07-01 19:00:15 |
|            3 |       3 | POINT (-71.8803 43.1493) | 2017-07-01 19:00:25 |
+--------------+---------+--------------------------+---------------------+

The part of the SQL for taking out the points out of the geometry was borrowed from Tom Halladay's answer to Linestring to Points.

NOTE: Tested with SQL Server 2016, SP1 (13.0.4001.0)

Performance with millions of trajectories would most probably be not very good. Just the fact that you have to call one function STPoinN for every single point... makes it really hard. The question is: Are there (published) alternatives?

If I had to really use the information of many points at a time, I'd consider unnormalizing the information (via INSERT/UPDATE/DELETE triggers) and have the coordinates from points also in the TrajectoryPoint table. If it is done in a really automatic fashion, the risk of inconsistency is rather low. If the use-case doesn't require it, I'd leave it as proposed.

StackExchange Database Administrators Q#159934, answer score: 5