Solved

Joining housenumbers ot number intervals

  • 19 December 2017
  • 5 replies
  • 1 view
O
Badge

Hi all

I have an interesting task of joining 2 datasets:

One dataset is a table with house numbers, the table has roadids, housenumbers (including letters) and an attribute that shows if the house number is an even number og uneven.

The second datasat contains roadids that matches the first dataset but instead of specific house numbers the second dataset contains 2 attributes: house_number_from and house_number_to, the second dataset also has an attribute telling if the intervals are even or uneven numbers.The second dataset also has a district_id attribute.

I have to join these datasets to get the district_id joined on the first dataset. I somehow have to determine if the adresses in the first datasets fall between the intervals in the second grouped by the roadid

I have attached both datasets here:

does anyone have an idae how to go about this?

icon

Best answer by takashi 19 December 2017, 14:49

View original
1 Attachment

5 replies

takashi
Rank
Userlevel 2
Badge +17
  1. It seems that some house numbers contain alphabetic characters. How you can determine large and small between two house numbers if one of them or both contains an alphabetic character?
  2. Are '001' and '1' an identical number?

 

O
Badge
  1. It seems that some house numbers contain alphabetic characters. How you can determine large and small between two house numbers if one of them or both contains an alphabetic character?
  2. Are '001' and '1' an identical number?

 

Hi

 

 

Yes, the letters are a problem but I think the end result will be ok if we remove the letters so i have added 2 new datasets to this post with the letters and numbers ind seperate columns. I have also trimmed down the leading zeroes (which i should have done in the first place.)

 

 

the columns containing house numbers without letters have _int in the column names.

 

 

district-intervals.xlsxhouse-numbers.xlsx

 

takashi
Rank
Userlevel 2
Badge +17

Hi @oaralb, if I understood the requirement correctly, the InlineQuerier with this SQL statement could generate desired result. merge-district-to-houses.fmwt (FME 2017.0.1.1)

select    a.*,    b.house_number_from,    b.house_number_to,    b.district_idfrom house_numbers as aleft outer join district_intervals as bon b.roadid = a.roadid and b.evenuneven = a.evenunevenand (a.house_number_int between b.house_number_from_int and b.house_number_to_int)

0684Q00000ArKMfQAN.png

O
Badge

Hi @oaralb, if I understood the requirement correctly, the InlineQuerier with this SQL statement could generate desired result. merge-district-to-houses.fmwt (FME 2017.0.1.1)

select    a.*,    b.house_number_from,    b.house_number_to,    b.district_idfrom house_numbers as aleft outer join district_intervals as bon b.roadid = a.roadid and b.evenuneven = a.evenunevenand (a.house_number_int between b.house_number_from_int and b.house_number_to_int)

0684Q00000ArKMfQAN.png

 

Awesome, that did the trick :)
takashi
Rank
Userlevel 2
Badge +17

Hi @oaralb, if I understood the requirement correctly, the InlineQuerier with this SQL statement could generate desired result. merge-district-to-houses.fmwt (FME 2017.0.1.1)

select    a.*,    b.house_number_from,    b.house_number_to,    b.district_idfrom house_numbers as aleft outer join district_intervals as bon b.roadid = a.roadid and b.evenuneven = a.evenunevenand (a.house_number_int between b.house_number_from_int and b.house_number_to_int)

0684Q00000ArKMfQAN.png

Good to hear that. In addition, some string operations might be helpful to extract integer part from the number attribute. e.g.

 

0684Q00000ArMG0QAN.png

Hope this helps. Happy holidays!

 

 

Reply


Community Stats

28,631
Posts
109,154
Replies
36,510
Members
Terms & ConditionsCookie 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.