How do I find a point half way between 2 points?

OK, many thanks to @Ken Johnson and @Scot Hauder. Their suggestions have got me to the solution as:

DECLARE @Converter TABLE (
LatLong_t NVARCHAR(MAX),
LatLong_g GEOGRAPHY)

INSERT INTO @Converter 
SELECT  --@LatLong_t =
 'POINT('
       + CONVERT(NVARCHAR(MAX), ROUND(( [ssug].[location].Long
                            + [ssug1].[location].Long ) / 2, 6))
       + ' ' + CONVERT(NVARCHAR(MAX), ROUND(( [ssug].[location].Lat
                                + [ssug1].[location].Lat ) / 2,
                              6)) + ')', NULL 
FROM    @SQLServerUGs AS ssug
       CROSS JOIN @SQLServerUgs AS ssug1
WHERE   [ssug].[name] != [ssug1].[name]

UPDATE @Converter
SET LatLong_g = GEOGRAPHY::STGeomFromText(LatLong_t, 4326)

SELECT  [ssug].[name] ,
    [ssug].[location].STBuffer(5000) AS [UG]
FROM    @SQLServerUGs AS ssug
UNION ALL
SELECT 'Halfway' ,
    [c].[LatLong_g].STBuffer(1000) FROM  @Converter AS c

This is what I wanted to achieve but am not sure that it's the best way, if anyone has any other ideas please add your answers below.

Sorry, I have not worked hard to come up with my own answer, but carrying on with Kev Riley's answer, following looks much cleaner (hope so :))

SELECT  ssug.name
,       [ssug].[location].STBuffer(5000) UG
FROM    @SQLServerUGs AS ssug
UNION ALL
SELECT  [ssug].[name] + '->' + ssug2.name AS name
,       GEOGRAPHY::STGeomFromWKB(GEOMETRY::STGeomFromWKB(ssug.location.STUnion(ssug2.location).STAsBinary(),
                                                         4326).STEnvelope().STCentroid().STAsBinary(),
                                 4326).STBuffer(1000) HalfwaySTBuffer
FROM    @SQLServerUGs AS ssug
        JOIN @SQLServerUGs AS ssug2
        ON ssug.name <> ssug2.name

The difference is use of STAsBinary and STUnion functions. With their help the strings concatenation etc. are not needed.

/* === MORE SIMPLER APPROACH ==== */
    SELECT  ssug.name
    ,       [ssug].[location].STBuffer(5000) UG
    FROM    @SQLServerUGs AS ssug
    UNION ALL
    SELECT  [ssug].[name] + '->' + ssug2.name AS name
    ,       GEOGRAPHY::Point((ssug.location.Lat + ssug2.location.Lat) * 0.5,(ssug.location.Long + ssug2.location.Long) * 0.5,
                                                             4326).STBuffer(1000) HalfwaySTBuffer
    FROM    @SQLServerUGs AS ssug
            JOIN @SQLServerUGs AS ssug2
            ON ssug.name  ssug2.name

I'm sure there's a way to do it using great circles and trig, but I took the averaging the points approach which puts the midpoint between Bristol and Cardiff right here:

SELECT [ssug].[name] as startpoint,
       [ssug1].[name] as endpoint,
       round(([ssug].[location].Lat + [ssug1].[location].Lat)/2, 6) as midLat,
       round(([ssug].[location].Long + [ssug1].[location].Long)/2, 6) as midLong,
       cast('POINT(' + cast(round(([ssug].[location].Long + [ssug1].[location].Long)/2, 6) as varchar) + ' ' + cast(round(([ssug].[location].Lat + [ssug1].[location].Lat)/2, 6) as varchar) + ')' as geography) as MidPoint,
       [ssug].[location].STBuffer(5000) as data

 FROM @SQLServerUGs AS ssug
 CROSS JOIN @SQLServerUgs AS ssug1
 WHERE [ssug].[name] != [ssug1].[name]

Just for fun, here's the version that builds them as URLs...

select 'www.gorissen.info/Pierre/maps/googleMapLocation.php?lat=' + cast(midLat as varchar) + '&lon=' + cast(midLong as varchar) + '&setLatLon=Set'
from
(
SELECT [ssug].[name] as startpoint,
       [ssug1].[name] as endpoint,
       round(([ssug].[location].Lat + [ssug1].[location].Lat)/2, 6) as midLat,
       round(([ssug].[location].Long + [ssug1].[location].Long)/2, 6) as midLong,
       cast('POINT(' + cast(round(([ssug].[location].Long + [ssug1].[location].Long)/2, 6) as varchar) + ' ' + cast(round(([ssug].[location].Lat + [ssug1].[location].Lat)/2, 6) as varchar) + ')' as geography) as MidPoint,
       [ssug].[location].STBuffer(5000) as data

 FROM @SQLServerUGs AS ssug
 CROSS JOIN @SQLServerUgs AS ssug1
 where [ssug].[name] != [ssug1].[name]
 ) AS sources

In geometry I might do something like this:

SELECT @Linestring.STEnvelope().STCentroid()

Since there are no equivalent methods for geography, as an alternative, you could transpose the lat/lon values and take the intersection.

DECLARE @g geography;
DECLARE @g2 geography;
SET @g = geography::STGeomFromText('LINESTRING(-3.1770349 51.4752282, -2.597065 51.4496655 )', 4326);
SET @g2 = geography::STGeomFromText('LINESTRING(-3.1770349 51.4496655, -2.597065 51.4752282 )', 4326);
SELECT @g.STIntersection(@g2).ToString();

OK A bit of a combination of all the answers here.

This solution does everything in-line , so no need for updating temporary table/variables.

It builds a LINESTRING (geometry data type) between each location, then gets the halfway point by taking the STCentroid value of the STEnvelope - however this results in a geometry data type and cannot be combined with the geography data type to display on the spatial tab, so there is an extra step that converts the geometry back to geography.

select
    [ssug].[name] ,
    ssug.name,
    [ssug].[location].STBuffer(5000)
FROM @SQLServerUGs AS ssug
union all
SELECT  [ssug].[name] ,
       ssug2.name,
       geography::STGeomFromText(
         geometry::STGeomFromText(
            'LINESTRING(' +
            replace(replace(ssug.location.STAsText(),'POINT (', ''),')','')
            + ', '+
            replace(replace(ssug2.location.STAsText(),'POINT (', ''),')','')
            +')'
         ,4326).STEnvelope().STCentroid().STAsText()
       , 4326).STBuffer(1000)
FROM @SQLServerUGs AS ssug
join @SQLServerUGs as ssug2 on ssug.name <> ssug2.name

It's still not very clean, but at least avoids the conversion out of spatial data types.

Great question Jonathan, I'd never played with spatial data before and this presented a real-world problem to solve! Still amazed that something as simple as a halfway point is soooo difficult to calculate.