2

Im trying to calculate a batch of data with a pupil unique identifier, their home postcode and their adoption placement postcode.

I would like to find the distance between the home and the placement of the pupil. I do have RouteFinder but only a small road network so I'm happy to just get a straight line distance measurement.

I have tried creating two sperate tables with the geocoded postcodes, one for home postcodes with the unique pupil ID and one with their placement postcodes with the ID.

Has the distance calculator got a capability of just calculating the home postcode point to the placement postcode point where the identifiers match.

I know i can get every possible combination and then filter it out in Excel using a formula however i can only do this for small batches of info.

Can anyone let me know if there is a better way to do this with a large data set?

3

You can do this via a SQL:

Select Home.PupilID, Home.Postcode, Placement.Postcode
   , Distance(CentroidX(Home.OBJ), CentroidY(Home.OBJ), CentroidX(Placement.OBJ), CentroidY(Placement.OBJ), "m") "Distance_in_meters"
From Home, Placement
Where Home.PupilID = Placement.PupilID 

In the statement above I assume you have two tables: Home and Placement. Both have a column called PupilID and Postcode.

Using the PupilID I join the two tables. I then use the Distance() function to calculate the distance between the centroid of the two postcodes.

You can copy the individual elements from the above statement into the SQL Select dialog in MapInfo Pro. But it might be even easier to paste the entire statement into the MapBasic window, selecting the statement and hitting Enter (or Ctrl + Enter on MapInfo Pro 64 bit).

| improve this answer | |
  • Thank you Peter. I'm a beginner when it comes to mapinfo and i don't write SQL however, one of my colleagues writes SQL but doesn't use mapinfo. Please could you direct me to where the SQL can be entered. – Hayley Aug 24 '16 at 10:56
  • Sure, see my comment above in the answer – Peter Horsbøll Møller Aug 24 '16 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.