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

zun

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?

mosheg

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_Cary

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.

zun

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_Cary

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)

zun

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_Cary

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.

zun

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 

zun

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)

zun

Following up on my last comment, any idea @Ariel_Cary ? Would def appreciate your help!

Ariel_Cary

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.

rudesingh56

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

Ariel_Cary

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)