# question

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

Given this information DECLARE @SQLServerUGs TABLE ( name VARCHAR(255) , location GEOGRAPHY ); INSERT INTO @SQLServerUGs VALUES ('Cardiff User Group', 'POINT(-3.1770349 51.4752282)'), ('Bristol User Group', 'POINT(-2.597065 51.4496655)'), ('SQL South West', 'POINT(-3.467336 50.729626)') SELECT [ssug].[name] , [ssug].[location] FROM @SQLServerUGs AS ssug SELECT [ssug].[name] , [ssug].[location].STBuffer(5000) FROM @SQLServerUGs AS ssug How can I plot the point that is half way between each of the user groups? I can use `"ssug.Location.STDistance"` to find the distance between the locations but how would I plot a point that is halfway please?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Its not horrible, it's simply a guy who doesnt know what he's doing making something work. "If it doesnt work hit it with a hammer. If it still doesnt work hit it harder"
1 Like 1 ·
Wow! That's horrible! You had to convert data out of spatial to text, to back to spatial to get a point in space between 2 other points. I'm amazed there's not better Spatial 'operators'.
0 Likes 0 ·
Why can't you do that calculation 'in-line'? Do you have to do it in 2 steps : build up the string, then convert it to geography?
0 Likes 0 ·
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 : http://www.gorissen.info/Pierre/maps/googleMapLocation.php?lat=51.4624&lon=-2.88705&setLatLon=Set

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hmmm, I can see in the future a question about how to clip the data so we dont get any drowned DBAs!!
0 Likes 0 ·
OK, I got the fact that I can divide the Lat and Long by 2 before I posed the question, the trouble I ran in to is trying to then turn those values into a geography type again and get it onto a map ... Its close but not a final solution.
0 Likes 0 ·
I was thinking you were just after the lat/long. Here is the midpoint as geography (updated in the answer, too) 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,
0 Likes 0 ·
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();
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the answer Scott. If I have dozens of groups how would I transpose the values 'programatically' (ie in TSQL). I want eventually to find half way points between each pair of UGs that are closest together.
0 Likes 0 ·
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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I take it you've seen this
2 Likes 2 ·
the halfway point is actually just that, its halfway through the problem I start out to solve - I wanted to draw a line between two points, at 90deg to the shortest line between them. Thus representing the border between them. Check out http://en.wikipedia.org/wiki/Voronoi_diagram
0 Likes 0 ·
yes, that's where all this mapping activity started!
0 Likes 0 ·
Of course, these halfway points land in the sea sometimes....
0 Likes 0 ·
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
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for keeping going on this @Usman, it is indeed easier to read and more elegant that other solutions.
0 Likes 0 ·
@Fatherjack You are always welcome :) May be I am not good at understanding your question, but it seems some detail like what is the full purpose etc. is missing in the question (May be hidden in the hyperlinks provided but could be elevated to the question)?? That addition could be helpful for the future reference. Thanks.
0 Likes 0 ·
much better without the strings
0 Likes 0 ·
Interesting approach, I don't I've ever mixed geography and geometry. I will definitely have to explore this further--this is why I love this site!
0 Likes 0 ·
I do not remember the exact, but such conversion from geography to geometry or vice versa may not behave well for edge cases. So this made me think of a solution without the conversion. Then re-grabbing from the start yields that it is soooo much simpler :) I have edited my response and added the second solution.
0 Likes 0 · 