Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Geospatial functions in Vertica not returning expected results, as per Google Maps

Hi, I'm running into an issue with geospatial function being in-accurate. I'm using Stv_geometrypoint and St_distance. See example below:

--https://www.usna.edu/Users/oceano/pguth/md_help/html/approx_equivalents.htm says 1 degree ~= 111KM
--lat/long of empire state building new york: (40.74819191258412, -73.98573903191816)
--lat/long of Good Smaritan Hospital in LongIsland NYC: (40.69412102658633, -73.29518084490165)
--distance between empire state & Good Smaritan Hospital == 58.58KM from Google Maps

SELECT public.St_distance(public.Stv_geometrypoint(-73.98573903191816, 40.74819191258412), public.Stv_geometrypoint(-73.29518084490165, 40.69412102658633))
--Result: 0.692671834542238
-- 0.692671834542238 * 111KM = 76.88KM

76.88KM is not even close to 58.58KM.

What am I doing wrong?

Answers

  • moshegmosheg Administrator

    You did it right.
    Distance: 76.81 km (to 4 SF*)
    Initial bearing: 358° 42′ 38″
    Final bearing: 358° 45′ 45″
    Midpoint: 73° 38′ 26″ S, 040° 43′ 14″ E
    However, the mentioned points are in Antarctica.
    For online distance calculator see: https://www.movable-type.co.uk/scripts/latlong.html

    SELECT public.St_distance(public.Stv_geometrypoint(-73.98573903191816, 40.74819191258412), public.Stv_geometrypoint(-73.29518084490165, 40.69412102658633))*111;
    ------------------
     76.8865736341885
    (1 row)
    
  • Ariel_CaryAriel_Cary Employee

    You shouldn't approximate distances if you're interested in accuracy. That reference you found indicates the approximation factors are valid around the Equator. Your "mileage will change" elsewhere, getting worse as you travel farther away from the Equator.

    You can try geography data type instead.

    SELECT public.St_distance(public.Stv_geographypoint(-73.98573903191816, 40.74819191258412), 
    public.Stv_geographypoint(-73.29518084490165, 40.69412102658633));
       St_distance    
    ------------------
     58505.7199106322
    (1 row)
    

    That would be the as-the-crow-flies distance. You might be computing road network distance on Google.

  • zunzun
    edited May 12

    Hello,

    I used the distance function on google maps (right click and measure distance between points).
    link here: https://google.com/maps/place/Good+Samaritan+Hospital/@40.7073509,-73.9119435,10.62z/data=!3m1!5s0x89e82d4d357a2c57:0x921157c9e7378ac1!4m12!1m6!3m5!1s0x0:0x55194ec5a1ae072e!2sTimes+Square!8m2!3d40.7579747!4d-73.9855426!3m4!1s0x89e9d2b360413aa5:0x2b44376bff7fd3e4!8m2!3d40.6940832!4d-73.2943189

    Answer should be 58.5KM.
    Stv_geographypoint gives the correct distance, but Stv_geographypoint is much slower than Stv_geometrypoint.

    Is there anyway for Stv_geometrypoint to obtain the correct answer 58.5KM? I know you said it's an estimation based on Equator, but is there any parameters we can pass to improve accuracy? Being off by 20KM seems quite big

  • Ariel_CaryAriel_Cary Employee

    You can experimentally refine estimation parameters for certain lines of latitude you're interested in. For instance, distance between two meridians near the latitude lines of your reference points is:

    SELECT public.St_distance(public.Stv_geographypoint(0, 40.5), public.Stv_geographypoint(1, 40.5));
       St_distance    
    ------------------
     84552.9493174148
    (1 row)
    

    using that to approximate distance, you get:

    SELECT public.St_distance(public.Stv_geometrypoint(-73.98573903191816, 40.74819191258412), public.Stv_geometrypoint(-73.29518084490165, 40.69412102658633))*84552.9493174148/1000; 
         ?column?     
    ------------------
     58.5674465196506
    (1 row)
    
  • Oh wow that's awesome.

    Do you mind explaining how that algorithm works exactly?

    Specifically, where did you get the 0 and 1 from in
    SELECT public.St_distance(public.Stv_geographypoint(0, 40.5), public.Stv_geographypoint(1, 40.5));
    ?

    Is that always set to 0 and 1, and we can estimate the latitudes, like you did?

  • Ariel_CaryAriel_Cary Employee

    I chose 0 and 1 semi-arbitrarily to keep things simple. What matters is the latitude line. You're trying to get a sense of how 1-degree distance changes as you move up/down latitudes.

  • zunzun
    edited May 12

    Gotcha thank you.

    Tried out your algorithm, generally works well. But we're seeing the results being more in-accurate as you move south.

    Any idea why that's the case? and how we can improve the algorithm as we move away from equater near in south?

    Experiments I ran:

    -- Experiment 4: Egypt
    -- Eden Healthcare Hospital: (29.9562495398459, 30.919366376776104)
    -- Badr City Awwal Health Bureau: (30.137419683878445, 31.72385648052336)
    -- Distance from Google Maps: 79.96KM
    SELECT public.St_distance(public.Stv_geometrypoint(30.919366376776104, 29.9562495398459), public.Stv_geometrypoint(31.72385648052336, 30.137419683878445))
               * (public.St_distance(public.Stv_geographypoint(0, 29.9), public.Stv_geographypoint(1, 29.9)) / 1000)
    -- 79.4903916413761    vs From Google Maps 79.96KM 
    
    
    -- Experiement 5: Tokyo
    -- Tama Kyuryo Hospital: (35.60441204922332, 139.42068129894284)
    -- Matsudo City General Hospital: (35.80255649949862, 139.9324877166963)
    -- Distance from Google Maps: 51.13KM
    SELECT public.St_distance(public.Stv_geometrypoint(139.42068129894284, 35.60441204922332), public.Stv_geometrypoint(139.9324877166963, 35.80255649949862))
               * (public.St_distance(public.Stv_geographypoint(0, 35.6), public.Stv_geographypoint(1, 35.6)) / 1000)
    --49.6204414723404    vs From Google Maps 51.13KM 
    
    
    -- Experiment 6: Large Distance, LA
    -- Los Robles Regional Medical Center: (34.2068024324121, -118.88231817672143)
    -- Redlands Community Hospital: (34.0362877080048, -117.20556277656054)
    -- Distance from Google Maps: 155KM
    SELECT public.St_distance(public.Stv_geometrypoint(-118.88231817672143, 34.2068024324121), public.Stv_geometrypoint(-117.20556277656054, 34.0362877080048))
               * (public.St_distance(public.Stv_geographypoint(0, 34.2), public.Stv_geographypoint(1, 34.2)) / 1000)
    -- 155.001343878586    vs From Google Maps 155KM 
    
    
    -- Experiment 7: New Zealand
    -- University of Canterbury: (-43.523046727195094, 172.57969398858603)
    -- Lyttelton Farmers Market: (-43.60287038304825, 172.72210672762935)
    -- Distance From Google Maps: 15KM
    SELECT public.St_distance(public.Stv_geometrypoint(172.57969398858603, -43.523046727195094), public.Stv_geometrypoint(172.72210672762935, -43.60287038304825))
               * (public.St_distance(public.Stv_geographypoint(0, -43.5), public.Stv_geographypoint(1, -43.5)) / 1000)
    --13.1680018711177    vs From Google Maps 15KM 
    
    
    
    -- Experiment 8: New Zealand, but larger
    -- Kaiapoi North School: (-43.37636491596186, 172.6635217251082)
    -- Lyttelton Farmers Market: (-43.60287038304825, 172.72210672762935)
    -- Distance From Google Maps: 25.60KM
    SELECT public.St_distance(public.Stv_geometrypoint(172.6635217251082, -43.37636491596186), public.Stv_geometrypoint(172.72210672762935, -43.60287038304825))
               * (public.St_distance(public.Stv_geographypoint(0, -43.6), public.Stv_geographypoint(1, -43.6)) / 1000)
    --18.9018118842852    vs From Google Maps 25.60KM 
    
    
  • Also, if we have two widely different latitudes, what which lat do you suggest we use in this case?

    For example, if we have 172.57969398858603, -43.523046727195094 and 172.57969398858603, -23.523046727195094, should it still be fixed on the 43.5 degree lat, as shown below?

    public.St_distance(public.Stv_geometrypoint(172.57969398858603, -43.523046727195094), public.Stv_geometrypoint(172.72210672762935, -23.523046727195094))
               * (public.St_distance(public.Stv_geographypoint(0, -43.5), public.Stv_geographypoint(1, -43.5)) / 1000)
    
  • Following up on my last comment, any idea @Ariel_Cary ? Would def appreciate your help!

  • Ariel_CaryAriel_Cary Employee

    The approximation generally works in small regions, like cities. For larger areas, I would not recommend using the approximation. It will tend to have higher variance as you move to the north/south poles. You have to use spherical distances to have proper accuracy.

    You also need to be careful about crossing the international date line (long=180) when mapping lat/long coordinates into a plane. Two points on both sides of IDL can appear farther away than they are on a spherical model.

  • edited July 5

    Is there anyway for Stv_geometrypoint to obtain the correct answer 58.5KM? get-vidmateapp.com
    mobdro download

  • Ariel_CaryAriel_Cary Employee

    Not a reliable way with planar geometry. You should use GEOGRAPHY data type as I mentioned in an earlier post. I recommend using Vertica 12.0+, where we have made performance improvements to the distance function.

    SELECT public.St_distance(public.Stv_geographypoint(-73.98573903191816, 40.74819191258412), 
    public.Stv_geographypoint(-73.29518084490165, 40.69412102658633));
       St_distance    
    ------------------
     58505.7199106322
    (1 row)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.