SQL Server Spatial – Line Constructor or Version of ST_MakeLine(pt1,pt2)

Maybe this will help start you out but here is how I am thinking through this.

I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road. I am assuming that your roads are modelled as line segments along great circles.

So I don't think that this is a complete solution but, what I would be looking at doing is:

1. Do these lines intersect? If you assume that all intersections are listed as such in your db, you can assume no.
2. If so that's the closest point.
3. If not, then grab the vertices on both roads, draw any lines perpendicular to another line segment on the other, and calculate distance to intersection. Your shortest point will be the minimum of these lines.

That seems the best answer. You have to calculate points. I don;t know of any magic way to do this in PostGIS but it is something where spherical trig may come to the rescue. If the distances are sufficiently small, plane trig may be sufficient as an approximation, of course.

Below is a simplistic, single-query way to do it as a list of small 2 point line segments in one query, without using cursors. There's a lot more that can be done if you build and store your lines in another table with a cursor and the .STUnion method and your point spatialdata field.

Assumptions and changes: 1. includes city and state. 2. to be more realistic I stored lat and long as decimal(10,7), so, unfortunately this example converts lat and long back to varchar to build the line of Well Known Text (WKT). 3. I renamed order to ptorder since order is a reserved word. 4. Only one path or line exists in the source data table and cities not on the path have a NULL ptorder. 5. ptorder is a sequence without gaps, or else the query gets far more complicated. 6. I'm avoiding cursors. 7. I'm assuming your data looks like this:

5,    42.37, -71.03,  'Boston', 'MA' 
3,    39.75, -104.87, 'Denver', 'CO' 
2,    33.93, -118.40, 'Los Angeles', 'CA'
4,    25.82, -80.28,  'Miami Intl', 'FL' 
NULL, 40.77, -73.98,  'New York', 'NY'
1,    33.43, -112.02, 'Phoenix', 'AZ'

Load some test data like this:

CREATE TABLE pointdatatable (
    ptorder             INT, 
    lat                 DECIMAL(10,7), 
    long                DECIMAL(10,7), 
    pointspatialdata    GEOGRAPHY,    
    city                VARCHAR(20),
    state               CHAR(2) )

INSERT INTO pointdatatable (ptorder, lat, long, pointspatialdata, city, state) VALUES
    (5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA'), 
    (3, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'), 
    (2, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'), 
    (4, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'), 
    (NULL, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'), 
    (1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ')

Then this query produces the segments using WKT:

SELECT 
    a.ptorder,
    a.lat,
    a.long,
    a.city,
    b.ptorder,
    b.lat,
    b.long,
    b.city AS nextcity,
    CAST('LINESTRING(' + CAST(a.long AS VARCHAR) +' '+ CAST(a.lat AS VARCHAR) +', '+ 
        CAST(b.long AS VARCHAR) +' '+ CAST(b.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
FROM 
    pointdatatable a
LEFT OUTER JOIN 
    pointdatatable b
ON     
    b.ptorder = a.ptorder + 1 
WHERE 
    b.ptorder IS NOT NULL   
ORDER BY a.ptorder