calculate distance between two latitude longitude points

Hey,

I need to calculate the distance between two latitude longitude that I calculate using MapQuestGeocoder :

Adresse1 latitude1 longitude1 Adresse2 latitude2 longitude2
1 RUE DES SARCELLES 50,350142 1,573162 70 ROUTE DE DOULLENS 50,107496 1,850869
1 RUE DES SARCELLES 50,350142 1,573162 14 PLACE DU PILORI 50,107662 1,835815

What I used to add to my workflow to calculate the distance before extract the data in excel file ?


the first MapquestGeocoder is to calculate latitude1 and longitude1 for the adresse 1 and the second MapquestGeocoder is to calculate latitude2 and longitude2 for the adresse2

Thank You !

Hi @TIZIZ -

I believe you can do this using the Geo distances node available from the Palladian extensions.

If you are conversant in R, you could also use an R Snippet node with the geosphere package to do this - see https://stackoverflow.com/questions/32363998/function-to-calculate-geospatial-distance-between-two-points-lat-long-using-r?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa

2 Likes

Hi @TIZIZ,

You are on the right way. As Scott already mentioned, the Palladian nodes are what you are searching for – you already have used them to generate the coordinates. Have a look at the Geo distances in combination with the ColumnDistanceNode.

30

Use two Geo distances nodes to calculate distance matrices for each column and feed your data plus the matrices into the ColumnDistanceNode to calculate the distances in km. The output for your first example is 33,44 km:

Best regards,
Daniel

11 Likes

@ScottF
Thank you so much for you help and I am so grateful to you guys :slight_smile:
I have One more question about the significant shift between the latitude longitude that give mapquestgeocoder and GoogleAdressesGeocoder so great diffirence between the distances.
For example for the first example by mapquest gives 33.44 km but by googleGeocoder it gives 1.1 km ?
What I can do for correct this in what gives MapQuest ?!

Thank you so much again !

This is either caused by different databases used by Google vs MapQuest, or probably by different parsing algorithms to parse the input address, or just by different resolution strategies (an input string ‘1 RUE DES SARCELLES’ is quite ambiguous likely exists in more than one city)

MapQuest as well as Google Maps would theoretically allow to narrow down the search to a given bounding box or region, but we currently do not support this in the nodes.

2 Likes

Hi Daniel @danielesser

hoping you can help me too…I am absolutely new to using KNIME and I want to the get a distance in kilometres or miles between two given latitude and longitude values.
The LatitudeLongitudeToCoordinate nodes have an error saying
“No suitable column with any of the name(s) lat,latitude found”
What does this mean please?
I have manually added a few rows of data so I can run and see the results (Ive manually created the rows and named the rows latitude respective longitude)
Can you please help me understand?
Many thanks
Alex

Hi @alexAI,

Sure. You are almost there! The problem is your input table. Row0 is the first row of your table but it contains your column headers. You need to properly define your column headers, column types and contents in your Table Creator node.

As seen in the screen, open the configuration of your Table Creator and double-click on the table header. This opens a dialog that let you define the column name and its type. Give it a proper name and select Number (double) as type.

Please have a look at my workflow on how this is done: lat-lon-geo-distance.knwf (14.4 KB) Let me know, if you still struggle with it.

– Daniel

5 Likes

Dear Daniel,

Absolutely brilliant !
I have so much to learn :slight_smile::smiling_face_with_three_hearts:
thank you a million for the help
best regards
alex

2 Likes

Hi Daniel. Can you help me too?

I tried to use something similar to your WorkFlow but I have a different situation. I don’t want to calculate the distance beetween every point I have in a table because I have 46.590 geolocations, so it would give me more than 2 billion rows. I created a file with 46.590 GeoLocations and I need to find the distance from these points to other 70 geolocations. I will have something with around 3 million rows.

Can anybody help me, please?

Hi @rodrigogrola,

Has that already solved your problem or should I have a look?

Best regards,
Daniel

Hi Daniel,

I’m trying to solve that n x m combinations… some idea? The challenge is to select the points from n-vector closest to the ones on m-vector.

Thank you in advance,

You might want to check the links in this thread. Most of them are links to this thread, they’re found below each post. There should still be something useful to be found in some of them. Especially this one:

and also the Haversine formula and for some use cases more general the Great-circle distance.

You haven’t given us much information about your problem, but a general outline could look like this:

  1. Cross Join your input tables/vectors (beware, table size blows up dramatically)
  2. calculate distance between points in each row (Haversine formula)
  3. for each target point, select shortest distance (Duplicate Row Filter on target point, using Min-distance as tiebreaker)

Let us know if you need any more help! :slight_smile:

1 Like

FYI, a couple of weeks ago, I built a component that calculates distance between 2 sets of latitudes and longitudes that uses the Haversine formula:

Sharing just in case the component can be of any help.

EDIT: Some additional information about the component:
A simple use of my component is like this:
image

And for this input:
image

I get this result:
image

The component allows you to select which columns are the latitude 1 and 2, and longitude 1 and 2, so your column names do not matter.

I basically use the Haversine Formula of:

and based on the Earth radius of 6,371 kilometres.

4 Likes

Hi Thyme,

Thank you for your reply :slight_smile: the idea is to find the nearest village of a set to the GPS points of a track by calculating the minimum distance.

As you mention, it’s a dimension problem n x m:

  • n [100,2.000]: they are GPS points of a track

  • m [40, 300]: they are GPS locations of near villages.

Since the calculation of the distance using the Havesine method (thank you @bruno29a) is very fast, I am solving it by making a loop to check the shortest distance to the GPS points of each village.

This is my last implementation:

And these are the results:

  • n = 2040
  • m = 1850 (there a lot of small villages on that area)
  • final villages with d<200m = 175

Well… just trying to optimize or discover new “simplier” ways.

Thank you for your help guys!

1 Like

Great to see you were able to solve your problem! :slight_smile:

If computational resources are of concern, looping over one input vector definitely is the way to go. It’s also hella slow, so if you want execution speed, and your machine can handle it, you might want to try out the Cross Joiner after all. 4 million rows is a lot, but not a crazy amount. Maybe save your workflow before trying :wink:

1 Like

I’m on it… :smiley: let’s explore the limits of my new M1.
Thank you @Thyme

1 Like