Skip to main content
Solved

Merging data

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

View original
Did this help you find an answer to your question?

5 replies

danilo_fme
Evangelist
Forum|alt.badge.img+45
  • danilo_fmeEvangelist
  • Evangelist
  • 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

fmelizard
Safer
Forum|alt.badge.img+19
  • fmelizardSafer
  • Safer
  • 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

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
Evangelist
  • takashiEvangelist
  • 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.
M
Forum|alt.badge.img+2
  • markatsafe
  • October 6, 2017
shanetorrens3 wrote:

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

 

Reply


Helpful Members This Week

Community Stats

31,501
Posts
119,410
Replies
39,117
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.