asked
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?
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.
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"
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'.
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?
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
Hmmm, I can see in the future a question about how to clip the data so we dont get any drowned DBAs!!
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.
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,
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();
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.
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.
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
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
Thanks for keeping going on this @Usman, it is indeed easier to read and more elegant that other solutions.
@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.
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!
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.
6 People are following this question.
