document more explicitly which projections are used and how distances are calcualted

[dberardo-com]

hi there, based on the online tutorial i am trying to figure out how to calculate distances using meters.

in particular i am using this expression to calculate distances, but i am not getting results close to the ones from this tool: https://www.calculator.net/distance-calculator.html?la1=42.451392%09&lo1=%0914.183854%09&la2=42.4515965000&lo2=14.1841619000&lad1=38&lam1=53&las1=51.36&lau1=n&lod1=77&lom1=2&los1=11.76&lou1=w&lad2=39&lam2=56&las2=58.56&lau2=n&lod2=75&lom2=9&los2=1.08&lou2=w&type=3&ctype=dec&x=Calculate#latlog

the tool above states a distance of 34 m for the 2 points. if i use this expression to calculate distance in duckdb:

SELECT latitude, longitude, 30 as value, ST_Distance( ST_Transform( ST_Point(space.latitude,space.longitude) , 'EPSG:4326', 'EPSG:3857'), ST_Transform( ST_Point(42.4515965,14.1841619) , 'EPSG:4326', 'EPSG:3857')) as distance, [42.4515965, 14.1841619] as cc,

then i get a value of 46.097854

the results from the tool above are in line with the ones from google earth so i wonder why am i getting different results ? is it a matter of projections?

should i also include the altitude to get better results ?

[Maxxen]

Hi! Thanks for reporting this issue. Documentation is still very much in progress, but it's a good idea to maybe add a special section to elaborate more on projections and distances.

EPSG:3857 is a planar projection that spans almost the entire world, and while it does measure in meters it does have a lot of distortion as you get further away from the equator, thus you will get less accurate results because ST_Distance only calculates the cartesian distance (it is not aware of the altitude or curvature of the earth). Perhaps there is a more suitable projection you can use that has minimum distortion in your area of interest?

Alternatively if you want the most precise measurement in meters you could instead use the st_distance_spheroid function. This uses a very precise algorithm using a ellipsoidal approximation of the earths surface (which is more precise than most typical web distance measurement tools that assume the earth is a perfect sphere) and gives you back the result in meters. But this function is not projection aware and will only produce correct results if you give it coordinates in WGS84 (e.g. EPSG:4326, "degrees") and latitude/longitude order.

[dberardo-com]

alright so summing up your last suggestion i should go for:

st_distance_spheroid( ST_Transform( ST_Point(space.latitude,space.longitude) , 'EPSG:4326', 'WGS84'), ST_Transform( ST_Point(42.4515965,14.1841619) , 'EPSG:4326', 'WGS84 '))

this would indeed return about 34 m distance. so it seems to be doing the job.
in my altitude it is not so relevant so i am assuming this formula to always work well, although it is somewhat slower to execute

[Maxxen]

Yes, but note that EPSG:4326 is the same as WGS84, so you don't need the extra projection.
I don't know about any GIS tools that take altitude into account when calculating distances (unless maybe you have a raster height map or something...?). But I guess the _spheroid functions kind of does since they approximate the earth as an ellipsoid, but its more like an average, the earths radius is considered ~6356.7523km at the North Pole, and ~6378.1370km at the equator.

[dberardo-com]

alright, so you are suggesting this, right ? :

st_distance_spheroid( ST_Point(space.latitude,space.longitude) , ST_Point(42.4515965,14.1841619) )

and as for the altitude, thanks for pointing that out. i dont actually need this, but good to know the it is not generally found in conventional GIS libraries

[Maxxen]

Looks good!

[dberardo-com]

thats also way faster :)

well i believe this issue can be closed when the proper piece of documentation is added to the main doc.

also: please make all of us aware in the doc that EPSG:4326 and WGS84 are the same thing ... i could have never guessed it :D

[dberardo-com]

here i am again, i was wondering if what i am facing here might be a projection problem, or is it some issue i might be having in duckdb. i am planning to plot an heatmap on a map that uses leaflet and open street map as underlying tiling system.

the heatmap is composed by points that are constrained within a region, expressed as a polygon. To draw the polygon i am using google earth, then i get the coordinates and use them in duckdb. The result is a colored region that must fit within that polygon (although i might have gaps of uncolored parts of course:

as you can see in the picture, some of the colored dots are falling on top of buildings, while they should be all within streets. in deed you can see the bounding region as green shaded region in google earth cover only streets and no buildings:

can this be a problem of projection system?