11

I have the following query:

select distance(GeomFromText('POINT(8 49)',4326),GeomFromText('LINESTRING(8.329969 49.919323,8.330181 49.919468)',4326))

this gives me 0.97 degrees. But I need it in meters and do not know which SRID to transform to.

Can somebody give me an example how to get the result in meters for spatialite?

The positions are all in Europe.

tmanthey
  • 4,547
  • 6
  • 35
  • 42

2 Answers2

22

Just multiply the value in degrees by 111195 - this value is (Earth mean radius)*PI/180 - that is 'mean length of one great circle degree in meters on Earth's surface'.

The result obtained using this method is within 1% of the geodesic distance for the WGS84 ellipsoid.

EDIT

OK, my answer above still stands for the question: "how to convert arcs in degrees into lengths in meters", however, it's not the question you asked (should have asked).

I haven't used Spatialite professionally, so I assumed that your sample query indeed returns the 'length in degrees'. That's not true.

Unfortunately, it appears that Spatialite fails to calculate the distance in 'geographic sense'. Despite your geometries are defined with SRID 4326, it treats them as if they were on a plane.

Here's a simple proof:

select Distance(GeomFromText('POINT(0 0)',4326),GeomFromText('POINT(3 4)',4326));

returns 5.0.

It's a shame ...

Lets have a look at your original query:

select Distance(
  GeomFromText('POINT(8 49)',4326),
  GeomFromText('LINESTRING(8.329969 49.919323,8.330181 49.919468)',4326)
)

An equivalent query in MS SQL Server:

SELECT (geography::STGeomFromText('POINT(8 49)', 4326)).STDistance(geography::STGeomFromText('LINESTRING(8.329969 49.919323,8.330181 49.919468)', 4326));

gets you the correct result immediately: 105006.59673084648, in meters, and without any extra brouhaha.

So what are your options with Spatialite?

Indeed, as you said in comments, one option is to project your geometries, and calculate on those. Using SRID 3035 for Europe makes sense, too (if your locations are mostly in Germany, I'd consider SRID 25832).

select Distance(
  Transform(GeomFromText('POINT(8 49)',4326),25832),
  Transform(GeomFromText('LINESTRING(8.329969 49.919323,8.330181 49.919468)',4326),25832)
)

returns 104969.401605453.

As to your other sample (in comments):

select distance(
  Transform(GeomFromText('POINT(8.328957 49.920900)',4326),3035),
  Transform(GeomFromText('POINT(8.339665 49.918000)',4326),3035)
)

There's a simpler way to do it (if you have two POINTs, not a POINT and a LINESTRING): create a LINESTRING with your POINTs and use GeodesicLength function, like this:

select GeodesicLength(GeomFromText('LINESTRING(8.328957 49.920900, 8.339665 49.918000)',4326))

It returns 833.910006698673, as expected.

Incidently
  • 4,249
  • 3
  • 23
  • 30
  • Hmm, this gets me for above positions 1218 meters. When using Google earth this gets me distance of 833 meters. After trying a while I used this query: select distance(transform(GeomFromText('POINT(8.328957 49.920900)',4326),3035),transform(GeomFromText('POINT(8.339665 49.918000)',4326),3035)) which gets me 834 meters – tmanthey Aug 31 '12 at 07:50
  • yes, it should work reasonably well (see my edited answer, too) – Incidently Aug 31 '12 at 22:43
  • Thanks a lot. Last question. You have an elaborate way to choose an SRID? – tmanthey Sep 01 '12 at 08:11
  • 1
    I'm sure there is a lot of science behind choosing the right SRID, but what I'd do is this: run SELECT * FROM spatial_ref_sys WHERE ref_sys_name like 'WGS 84 / UTM zone%' in your Spatialite database. You'll get SRIDs for 120 zones (using UTM coordinate system and the same ellipsoid as in SRID 4326). Read up on UTM zones at http://en.wikipedia.org/wiki/Universal_Transverse_Mercator_coordinate_system – Incidently Sep 03 '12 at 09:32
  • it is related to your location, you can not multiply degree in a constant number – ghanbari May 10 '16 at 21:53
  • a decimal degree is not a unit of length. At any given location the length corresponding to a decimal degree varies with the bearing, too – ghanbari Aug 07 '16 at 21:26
12

In SpatiaLite's functions reference guide, you can see there are two version of the Distance() function. One takes only two arguments and return the distance in CRS units, the other takes 3 arguments and return the distance in meters.

To get the distance in meters, simply pass a third argument to Distance:

sqlite> select Distance(MakePoint(0, 0), MakePoint(3, 4));
5.0
sqlite> select Distance(MakePoint(0, 0), MakePoint(3, 4), 1);
554058.923752633
Gu1
  • 121
  • 2
  • 2