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

 0 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? more ▼ asked Mar 20, 2012 at 04:34 PM in Default Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 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. more ▼ answered Mar 21, 2012 at 12:01 PM Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 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'. Mar 21, 2012 at 01:18 PM Kev Riley ♦♦ 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? Mar 21, 2012 at 01:27 PM Kev Riley ♦♦ 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" Mar 21, 2012 at 03:11 PM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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 `` more ▼ answered Mar 22, 2012 at 11:06 AM Usman Butt 13.9k ● 6 ● 8 ● 14 Thanks for keeping going on this @Usman, it is indeed easier to read and more elegant that other solutions. Mar 22, 2012 at 11:34 AM Fatherjack ♦♦ @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. Mar 22, 2012 at 12:39 PM Usman Butt much better without the strings Mar 22, 2012 at 01:57 PM KenJ 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! Mar 22, 2012 at 06:13 PM Scot Hauder 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. Mar 26, 2012 at 11:51 AM Usman Butt add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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][1]: ``````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 `````` [1]: http://www.gorissen.info/Pierre/maps/googleMapLocation.php?lat=51.4624&lon=-2.88705&setLatLon=Set more ▼ answered Mar 20, 2012 at 05:58 PM KenJ 20.3k ● 1 ● 4 ● 12 Hmmm, I can see in the future a question about how to clip the data so we dont get any drowned DBAs!! Mar 21, 2012 at 10:27 AM Fatherjack ♦♦ 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. Mar 21, 2012 at 11:04 AM Fatherjack ♦♦ 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, `````` Mar 21, 2012 at 03:20 PM KenJ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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(); more ▼ answered Mar 20, 2012 at 08:10 PM Scot Hauder 6.1k ● 13 ● 15 ● 18 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. Mar 21, 2012 at 11:01 AM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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. more ▼ answered Mar 21, 2012 at 03:03 PM Kev Riley ♦♦ 53.9k ● 47 ● 49 ● 76 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 Mar 21, 2012 at 03:13 PM Fatherjack ♦♦ Mar 21, 2012 at 03:19 PM Kev Riley ♦♦ yes, that's where all this mapping activity started! Mar 21, 2012 at 03:57 PM Fatherjack ♦♦ Of course, these halfway points land in the sea sometimes.... Mar 21, 2012 at 03:57 PM Fatherjack ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1850
x168
x7
x1
x1

asked: Mar 20, 2012 at 04:34 PM

Seen: 2899 times

Last Updated: Mar 26, 2012 at 06:09 PM