InterpolateBetweenGeog results in strange Latitude value

Aug 4, 2009 at 2:37 PM

Hi

I am running the following query: -

 

declare @p1 geography = GEOGRAPHY::STPointFromText('POINT(-120.5 60.5)', 4326);
declare @p2 geography = GEOGRAPHY::STPointFromText('POINT(-90.5 60.5)', 4326);
declare @d float = @p1.STDistance(@p2)/2;
declare @q geography = dbo.InterpolateBetweenGeog(@p1, @p2, @d);

select @p1, @p1.Long, @p1.Lat
union all
select @q, @q.Long, @q.Lat
union all
select @p2, @p2.Long, @p2.Lat

It gives the following results. The latitude of @q looks wrong to me.
Geog  Long      Lat
@p1   -120.5    60.5
@q     -105.5    61.3436365208052
@p2     -90.5    60.5

Perhaps I missing something?

Aug 4, 2009 at 5:01 PM

Interestingly, writing the query like this gives similar results. Perhaps I have overlooked the fact that line is being drawn in a great circle.

declare @px geography = GEOGRAPHY::STLineFromText('LINESTRING ('+convert(varchar(max),@p1.Long)+' '+convert(varchar(max),@p1.Lat)+', '+convert(varchar(max),@p2.Long)+' '+convert(varchar(max),@p2.Lat)+')', 4326);
declare @q2 geography = @p1.STBuffer(@d).STIntersection(@px).STStartPoint();
select @q2.Long, @q2.Lat

Long    Lat
-105.500829982056    61.3436363954543

 

Developer
Aug 4, 2009 at 6:18 PM

I just ran your first query and got different results from you:

GEOG   LONGITUDE            LATITUDE
-------   --------------            -----------
@p1     -120.5                     60.5
@q      -105.598473528541   60.5
@p2    -90.5                        60.5

Try installing the latest SQL Server Spatial Tools assembly.

Aug 4, 2009 at 6:42 PM

I think there might be something wrong with your assembly. I've just downloaded the latest and greatest from SVN and received the same results. Also your results contradict the 2nd method I posted that finds the intersection of a line and circle.

Developer
Aug 4, 2009 at 7:26 PM

Very possible - I have many different versions of the assembly installed.  Let me do a bit more investigation.

Developer
Aug 4, 2009 at 7:34 PM

I just replicated your results using the latest assembly.  I 'll figure out what is going on.  Stay tuned...

Developer
Aug 4, 2009 at 9:29 PM
If you run this query (simple exansion of the code that you provided), you will observe in Management Studio, Spatial results tab, 
that the interpolated point (@q) visually appears to lie on the line between @p1 and @p2 (I've placed a buffer around the point
so that you can easily see where the interpolated point is).
<font size="2" color="#0000ff"><font size="2" color="#0000ff">

declare

</font></font>
<font size="2" color="#0000ff">

 

</font>

@p1 geography = GEOGRAPHY::STPointFromText('POINT(-120.5 60.5)', 4326); <font size="2" color="#0000ff"><font size="2" color="#0000ff">

declare

</font></font><font size="2" color="#0000ff">

 

</font>

@p2 geography = GEOGRAPHY::STPointFromText('POINT(-90.5 60.5)', 4326); <font size="2" color="#0000ff"><font size="2" color="#0000ff">

declare

</font></font><font size="2" color="#0000ff">

 

</font>

@d float = @p1.STDistance(@p2)/2; <font size="2" color="#0000ff"><font size="2" color="#0000ff">

declare

</font></font><font size="2" color="#0000ff">

 

</font>

@q geography = dbo.InterpolateBetweenGeog(@p1, @p2, @d);

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

declare

</font></font><font size="2" color="#0000ff">

 

</font>

@px geography = GEOGRAPHY::STLineFromText('LINESTRING ('+convert(varchar(max),@p1.Long)+' '+convert(varchar(max),@p1.Lat)+',

+convert(varchar(max),@p2.Long)+' '+convert(varchar(max),@p2.Lat)+')', 4326); <font size="2" color="#0000ff"><font size="2" color="#0000ff">

select

</font></font><font size="2" color="#0000ff">

 

</font>

@px <font size="2" color="#0000ff"><font size="2" color="#0000ff">

union

</font></font><font size="2" color="#0000ff">

 

</font>

all <font size="2" color="#0000ff"><font size="2" color="#0000ff">

select

</font></font><font size="2" color="#0000ff">

 

</font>

@q <font size="2" color="#0000ff"><font size="2" color="#0000ff">

union

</font></font><font size="2" color="#0000ff">

 

</font>

all <font size="2" color="#0000ff"><font size="2" color="#0000ff">

select

</font></font><font size="2" color="#0000ff">

 

</font>

@q.STBuffer(20000)

The correctness of the interpolated point, @q, is further validated with the following query which tests to see if the interpolated point
actually falls on the line between @p1 and @p2. I'm actually a bit suprised that I did not have to add an "intersection tolerance" in the
test since floating point number comparisions often fails without this.
<font size="2" color="#0000ff"><font size="2" color="#0000ff">

select

</font></font>
<font size="2" color="#0000ff">

 

</font>

@q.STIntersects(@px)

--Results: 1 (true)

 

If this had failed, I would have tried the same query with a tiny buffer region around the interpolated point:

 

<font size="2" color="#0000ff"><font size="2" color="#0000ff">

select

</font></font>
<font size="2" color="#0000ff">

 

</font>

@q.STBuffer(.001).STIntersects(@px)

--Results: 1 (true)

 

Let me know if this answers your question(s).

 

 

Developer
Aug 4, 2009 at 9:40 PM

So much for cut-and-paste - the html code did not show up in the window until after it posted ;-(

Here is my previous response, hopefully with the extra baggage...

If you run this query (simple exansion of the code that you provided), you will observe in Management Studio, Spatial results tab,
that the interpolated point (@q) visually appears to lie on the line between @p1 and @p2 (I've placed a buffer around the point
so that you can easily see where the interpolated point is).

declare @p1 geography = GEOGRAPHY::STPointFromText('POINT(-120.5 60.5)', 4326);
declare @p2 geography = GEOGRAPHY::STPointFromText('POINT(-90.5 60.5)', 4326);
declare @d float = @p1.STDistance(@p2)/2;
declare @q geography = dbo.InterpolateBetweenGeog(@p1, @p2, @d);
declare @px geography = GEOGRAPHY::STLineFromText('LINESTRING ('+convert(varchar(max),@p1.Long)+' '+convert(varchar(max),@p1.Lat)+',
                                                                                                        '+convert(varchar(max),@p2.Long)+' '+convert(varchar(max),@p2.Lat)+')', 4326);

select @px
union all
select @q
union all
select @q.STBuffer(20000)

The correctness of the interpolated point, @q, is further validated with the following query which tests to see if the interpolated point
actually falls on the line between @p1 and @p2. I'm actually a bit suprised that I did not have to add an "intersection tolerance" in the
test since floating point number comparisions often fails without this.

select @q.STIntersects(@px)
--Results: 1 (true)

If this had failed, I would have tried the same query with a tiny buffer region around the interpolated point:

 select (@q.STBuffer(.001)).STIntersects(@px)
--Results: 1 (true)

Let me know if this answers your question(s).

Coordinator
Aug 5, 2009 at 5:16 AM
Edited Aug 5, 2009 at 10:13 PM

Hi Folks,

If I understand the confusion here, it's because the interpolated point is north of the two input points. 
As bsdz suggests, this is indeed because the result lies on the great circle between the two input points,
and the great circle between the two points curves north from these two points.

One way to visualize this is to remember that the great circle is the intersection of the sphere with a
plane defined by the two endpoints and the center of the sphere.  For any two points in the northern
hemisphere, this curve arcs north between them.  Wikipedia has a good picture:

great circle

Another way to think about this is to consider a flight path between, say, Seattle and
Amsterdam: it goes very far north toward the pole.

Hope this helps,
-Isaac