Skip to main content
Solved

Merging data

  • October 3, 2017
  • 5 replies
  • 12 views
S
Forum|alt.badge.img

I have a excel sheet that includes a column of road numbers and a column of chainages (eg road distances) I have another spatial database called Road Segments which are lines. They contain attributes of AssetId, Road numbers and an attribute of 'StartChainage' and one of 'EndChainage'

What I want to achieve is to be able to identify what Road Segment AssetId the chainage from my excel sheet falls on.

Best answer by danilo_fme

Hi @shanetorrens3,

To Merge Data Excel with Lines ( Spatial Database ) i suggest you to use the transformer FeatureMerger. Into this transformer you configurate the attributes that are merged.

Thanks,

Danilo

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

danilo_fme
Celebrity
Forum|alt.badge.img+51
  • danilo_fmeCelebrity
  • Celebrity
  • 2077 replies
  • Best Answer
  • October 3, 2017

Hi @shanetorrens3,

To Merge Data Excel with Lines ( Spatial Database ) i suggest you to use the transformer FeatureMerger. Into this transformer you configurate the attributes that are merged.

Thanks,

Danilo

Partner Solutial Brazil - www.solutial.com.br
fmelizard
Safer
Forum|alt.badge.img+20
  • fmelizardSafer
  • Safer
  • 3719 replies
  • October 4, 2017

Hi @shanetorrens3, like @danilo_inovacao suggested the FeatureMerger can be your friend here provided you have an attribute common to both datasets to merge on.

S
Forum|alt.badge.img
  • shanetorrens3
  • Author
  • 6 replies
  • October 4, 2017

Thanks for your response, I have used FeatureMerger to join the ExcelSheet and GIS-Roadsegments.

However what I need to do is find out which GIS-RoadSegment (AssetId) the chainage in the Excel Sheet falls on.

Excel Sheet GIS - RoadSegments Common Attribute Road_Id Road_Id Attributes Chainage eg:1053 AssetId eg: 1 StartChainage eg: 425 EndChainage eg:2051

takashi
Celebrity
  • takashiCelebrity
  • 7843 replies
  • October 4, 2017

Hi @shanetorrens3, the InlineQuerier with this SQL Query might help you.

select
    a."Road_Id",
    a."Chainage",
    b."AssetId"
from "ExcelSheet" as a
left outer join "GIS_RoadSegments" as b
on a."Road_Id" = b."Road_Id"
and a."Chainage" between b."StartChainage" and b."EndChainage"

0684Q00000ArLApQAN.png

Alternatively, a geometric operation could also be a solution.

  1. Create points having coordinates ("Chainage", 0) from the ExcelSheet.
  2. Replace each GIS-RoadSegment with a line connecting between ("StartChainage", 0) and ("EndChainage", 0).
  3. Use a transformer such as SpatialFilter (Group By: Road_Id) to transfer attributes of a road segment to spatially related points. the SpatialRelater or PointOnLineOverlayer could also be used alternatively.
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
M
Forum|alt.badge.img+2
  • markatsafe
  • 1891 replies
  • October 6, 2017

Thanks for your response, I have used FeatureMerger to join the ExcelSheet and GIS-Roadsegments.

However what I need to do is find out which GIS-RoadSegment (AssetId) the chainage in the Excel Sheet falls on.

Excel Sheet GIS - RoadSegments Common Attribute Road_Id Road_Id Attributes Chainage eg:1053 AssetId eg: 1 StartChainage eg: 425 EndChainage eg:2051

@shanetorrens3 is there more than one RoadSegment record with the same Road_Id? I think you need to give us a little more detail. Then we should be able to give you a solution

 

Community Stats

32,068
Posts
121,762
Replies
39,693
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.