x

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?
more ▼

asked Mar 20 '12 at 04:34 PM in Default

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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 '12 at 12:01 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

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 '12 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 '12 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 '12 at 03:11 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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 '12 at 11:06 AM

Usman Butt gravatar image

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 '12 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 '12 at 12:39 PM Usman Butt
much better without the strings
Mar 22 '12 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 '12 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 '12 at 11:51 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

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 '12 at 05:58 PM

KenJ gravatar image

KenJ
19.1k 1 3 11

Hmmm, I can see in the future a question about how to clip the data so we dont get any drowned DBAs!!
Mar 21 '12 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 '12 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 '12 at 03:20 PM KenJ
(comments are locked)
10|1200 characters needed characters left

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 '12 at 08:10 PM

Scot Hauder gravatar image

Scot Hauder
5.9k 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 '12 at 11:01 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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 '12 at 03:03 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 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 '12 at 03:13 PM Fatherjack ♦♦
yes, that's where all this mapping activity started!
Mar 21 '12 at 03:57 PM Fatherjack ♦♦
Of course, these halfway points land in the sea sometimes....
Mar 21 '12 at 03:57 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x140
x5
x1
x1

asked: Mar 20 '12 at 04:34 PM

Seen: 2456 times

Last Updated: Mar 26 '12 at 06:09 PM