Skip to main content
Question

Possible combinations for each ID

  • November 15, 2018
  • 2 replies
  • 14 views
pmarseil
Contributor
Forum|alt.badge.img+1

Hi,

I have a problem and I was wondering if you could help me.

I have a road intersection table like this:

 

OdonymeIDIDRueIDINTNameCountIndex730350012611Robert, Rue20730350013121Roxanne, Rue21730350013132Roxanne, Rue20730350015142Thérèse, Rue21730350012092Rue Raymond32730350012653Robert, Rue20730350012963Rosaire, Rue21730350012974Rosaire, Rue20730350015184Thérèse, Rue21

 

 

I need to create a table of intersection combinations (IDINT). In other words, for each intersection id I want to know the possible intersection 2 streets at a time.

Ex:

IDINTIDRue (1)IDRue(2)112121234243249294239293

 

Someone would have any idea?

thanks

 

 

    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.

    2 replies

    takashi
    Celebrity
    • takashiCelebrity
    • 7843 replies
    • November 15, 2018

    I don't know where the "13"s in your example result come from, but if it was just a typo ("9" was correct) and your requirement was to enumerate 2-combinations of IDRue for each group of IDINT, this workflow might help you.

    0684Q00000ArLdqQAF.png

    Alternatively, the InlineQuerier with this SQL statement does the trick.

    select
        a."IDINT",
        a."IDRue" as "IDRue (1)",
        b."IDRue" as "IDRue (2)"
    from "data" as a
    inner join "data" as b
    on b."IDINT" = a."IDINT" and b."IDRue" <> a."IDRue"
    order by a."IDINT", a."IDRue"

    0684Q00000ArLZpQAN.png

    Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
    pmarseil
    Contributor
    Forum|alt.badge.img+1
    • pmarseilContributor
    • Author
    • Contributor
    • 1 reply
    • November 15, 2018

    I may have expressed myself badly, but it's all the combinations I was looking for. I still find a similar solution to yours, using the inlinequerier like you suggest, but with this request:

    select a."IDINT", a."IDRue" as IDRue1, b."IDRue" as IDRue2
    from "InputTable"as a
    cross join "InputTable" as b
    where a."IDRue" != b."IDRue"
    and a."IDINT" = b."IDINT"

    ;

    Thanks for your time!

    Community Stats

    32,084
    Posts
    121,834
    Replies
    39,720
    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.