MapInfo Pro

How can I calculate distance from origin for a series of points along a road centreline? I have a record ID (starting at 1) & a unique road ID. I have approx.1400 roads to do and some of them have up to 3600 points\road.

Thanks, Angelene

Hello Angelene,

Are you able to share a small subset of your tables in a packaged zip file on here so that we can try to take a closer look at your requirements on our end?

Thanks,

I want to make sure "I" understand. I just want to make sure, I understand the request/question.

Calculate: "Do you want to assign/issue a distance to each segment?", or you have already (assigned the distance in feet or miles) done that, and you want to calculate a distance using a route or specific segments, to get a total distance?

 

Hi Alfonso I would like to calculate the length of each segment so that I can then calculate the distance from origin. I have approx. 1500 roads to do. A friend has helped me out in the meantime with a calculation (using excel) based on Pythagoras theorem, which calculates the distance between nodes, however I would like to know how to do this using MapBasic please. Thanks Angelene Angelene Wright Asset/GIS Officer South Burnett Regional Council PO Box 336 Kingaroy QLD 4610 ' 07 4189 9537 • awright@southburnett.qld.gov.au : www.southburnett.qld.gov.au [Description: Description: sbrcBar] DISCLAIMER: This electronic mail message is intended only for the addressee and may contain confidential information. If you are not the addressee, you are notified that any transmission, distribution or photocopying of this email is strictly prohibited. The confidentiality attached to this email is not waived, lost or destroyed by reasons of a mistaken delivery to you. The information contained in this email transmission may also be subject to Right to Information and Information Privacy legislation.

If you basically are looking for the total length of the roads grouped by unique Road ID, you can do it with a grouped query like this:

Select Vejkode, Sum(CartesianObjectLen(OBJ, "m") "RoadLength"

From ROADS

Group By Vejkode

Vejkode is in case my column with the unique Road ID. I could also have use a column with the road name if that was unique.

Hi Peter No I’m not just after total length of road. I want to know the individual lengths of all the segments that make up the total length. Angelene Wright Asset/GIS Officer South Burnett Regional Council PO Box 336 Kingaroy QLD 4610 ' 07 4189 9537 • awright@southburnett.qld.gov.au : www.southburnett.qld.gov.au [Description: Description: sbrcBar] DISCLAIMER: This electronic mail message is intended only for the addressee and may contain confidential information. If you are not the addressee, you are notified that any transmission, distribution or photocopying of this email is strictly prohibited. The confidentiality attached to this email is not waived, lost or destroyed by reasons of a mistaken delivery to you. The information contained in this email transmission may also be subject to Right to Information and Information Privacy legislation.

You can do that by removing the group by consition and the Sum aggregation:

Select Vejkode, CartesianObjectLen(OBJ, "m" "RoadLength"

From ROADS

This will give you a list of all the records in your road table with their ID and the individual lengths in meters

It seems I'm not explaining this very well.

Thanks for your input everyone!

Maybe you can include a picture that shows what you are looking to do.

I would use RouteFinder in batch mode - if I am reading this correctly.

I think we're all missing something here. Sample data would help...but here's my guess....

Each road is a single polyline object made up of multiple segments. You would like to calculate the length of each segment (to begin with), whilst also retaining the order of segments.

1. Ensure your table has a RoadID and a SegmentID. RoadID needs to be populated but SegmentID should be blank at this stage.
2. For each polyline (i.e do this for one polyline object at a time):

• Select the polyline and disaggregate to single line objects (Spatial > Disaggregate to Lines)
  • Select the single line objects just created (e.g. Select * From RoadTable Where RoadID=1 Into RoadSel)
  • Update the SegmentID of the selected objects with an ID (e.g. Update RoadSel Set SegmentID=RowID). Note that this will update according to the order of the segments.

3. Calculate the length of each segment: Update RoadTable Set SegmentLength=CartesianObjectLen(OBJ, "m")

4. If you like, you can then proceed to calculate the cumulative length of each segment (i.e. segment 1 in each road would be the length of that segment, segment 2 would be the length of segment 2 plus segment 1) and so on... I'll leave this step for now though.

As you can see this will take a while if it's to be done manually for 1,400 roads, but at least you can try it for one road to make sure this is what you want. I can think of another method using buffer objects that might be better, but what you will really need in the end is an mbx, or perhaps RouteFinder as mentioned.

 

 

I think, this is it.

 

I did a test with a road East & West directional, Have the Fields: "Street", "Length MI" and Etc....

Assigned the distance to the segments using, Update column.....Update field "Length MI".......

Result....

 

Then I used SQL select, while in the browser window!

 

The result, is the count of street segments...... and total distance of all the segments....

 

Yes, I'll figure out, how to delete the use-less column of "Seg_Len_Mi", but the last column does have the Total!

Here is a screen shot of my segments (road/street), nodes indicate segments

(7) East and (4) West.

I am uncertain if the images I posted on the other two answers I gave posted, so I will upload images. I am new to this group.

 

?

 

I think, this is it.

 

I did a test with a road East & West directional, Have the Fields: "Street", "Length MI" and Etc....

?

Assigned the distance to the segments using, Update column.....Update field "Length MI".......

Result....

 

Then I used SQL select, while in the browser window!

 

The result, is the count of street segments...... and total distance of all the segments....

 

Yes, I'll figure out, how to delete the use-less column of "Seg_Len_Mi", but the last column does have the Total!

A total/Sum can also be obtained the with SUM statistics feature.

 

 

do a selection and results in the statistics dialog box.

Hi Everyone. Gee I didn't expect all these responses! Thank you so much!!!!

James Nolet and Alfonso are spot on. Thanks to both of you! This is exactly what I am after however as you pointed out, undertaking this as a manual process ?for 1500 roads is not really feasible so yes an mbx would be great. I'll have to learn how to do this!

I undertook the buffer approach in the first instance but because it wasn't able to return an exact distance measurement I wasn't happy with the result. Additionally at road intersections the buffer method returned erroneous results as it had had multiple numbers to choose from, as well as giving me cumulative errors on very long roads.

A friend\colleague gave me the following calculation to work this out using the coordinates of the nodes in MS Excel, by firstly converting Lat\Long to a local projection and then importing the csv and then using column update. I thought that there must be a way to do the same thing within MapInfo? Someone would be able to write a script\mbx for this?

=IF(E3-E2=1,ROUND(SQRT((A3-A2)^2+(B3-B2)^2),1),"")

where E3=Record ID2 and E2=Record ID1 and

A2\B2 and A3\B3 are X\Y coordinates of the nodes that belong to the Record ID's.

Thank you also John Levers for the suggestion of Route Finder. I'm not familiar with this but will look into it for future reference.

Thank you everyone for your time. I should have posted some screen shots and referred to the sample data I posted in my previous post regarding buffers.

Thanks again for looking at this for me everyone! I'll get working on learning to write mapbasic scripts!

Kind regards, Angelene

MapInfo RouteFinder is available through your supplier. More info here http://support.pitneybowes.com/SearchArticles/VFP05_KnowledgeWithSidebarHowTo?id=kA180000000CtWdCAK&popup=false&lang=en_US

I couldn't find a product page on the PB website though??