6

PostGIS provides a function called ST_MakeLine(pt1,pt2). It's great for constructing lines. MySQL does the same with LineString(pt1,pt2). What do you call the SQL Server analog that takes two Points and constructs a LineString?

I looked up LineString, but didn't see it mentioned.

Here is a test case on dbfiddle.

Sample data,

CREATE TABLE #tmp (
  pt1 geometry,
  pt2 geometry,
);

INSERT INTO #tmp(pt1,pt2) VALUES
  (geometry::Point(1,1,4326), geometry::Point(2,2,4326)),
  (geometry::Point(2,2,4326), geometry::Point(5,5,4326)),
  (geometry::Point(3,3,4326), geometry::Point(4,4,4326)),
  (geometry::Point(4,4,4326), geometry::Point(3,3,4326)),
  (geometry::Point(5,5,4326), geometry::Point(4,4,4326));

Query

SELECT pt1, pt2
  'My Line' AS line -- what goes here
FROM #tmp;

And I'm wanting "My Line" to be the LineString.

pt1         pt2         line
POINT (1 1) POINT (2 2) My Line
POINT (2 2) POINT (5 5) My Line
POINT (3 3) POINT (4 4) My Line
POINT (4 4) POINT (3 3) My Line
POINT (5 5) POINT (4 4) My Line
8

Try ShortestLineTo():

SELECT
  pt1.ToString(),
  pt2.ToString(),
  pt1.ShortestLineTo(pt2).ToString() AS line -- what goes here
FROM #tmp;

Returns

POINT (1 1)   POINT (2 2)   LINESTRING (1 1, 2 2)
POINT (2 2)   POINT (5 5)   LINESTRING (2 2, 5 5)
POINT (3 3)   POINT (4 4)   LINESTRING (3 3, 4 4)
POINT (4 4)   POINT (3 3)   LINESTRING (4 4, 3 3)
POINT (5 5)   POINT (4 4)   LINESTRING (5 5, 4 4)
  • great answer, that's exactly what I'm looking for. I just was being lazy and didn't want to write the literals, I took your example though and moved it up into the question to keep it simpler.. ShortestLineTo() is exactly what I wanted. – Evan Carroll Oct 11 '17 at 20:10
  • It is unlikely to happen, but I struck a "got ya" the other day with the Geography. If you start from 0 -180 and go to 0 -179 then it will change the start point to 0 180. Generally not a problem until you try to map it with ESRI Mapservice – MickyT Oct 12 '17 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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