Solved

Merging data

  • 3 October 2017
  • 5 replies
  • 5 views

Badge

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.

icon

Best answer by danilo_fme 3 October 2017, 16:24

View original

5 replies

Userlevel 4
Badge +30

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

Userlevel 3
Badge +13

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.

Badge

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

Userlevel 2
Badge +17

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.
Badge +2

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