**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?

## Leave a Comment

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

## Answers

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

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.

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

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

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:

using that to approximate distance, you get:

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?

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.

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:

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?Following up on my last comment, any idea @Ariel_Cary ? Would def appreciate your help!

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.

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

mobdro download

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.