Example #1
0
def moran_local_rate(subquery, numerator, denominator, w_type, num_ngbrs,
                     permutations, geom_col, id_col):
    """
        Moran's I Local Rate
        Andy Eschbacher
    """
    # geometries with values that are null are ignored
    # resulting in a collection of not as near neighbors

    query = pu.construct_neighbor_query(
        w_type, {
            "id_col": id_col,
            "numerator": numerator,
            "denominator": denominator,
            "geom_col": geom_col,
            "subquery": subquery,
            "num_ngbrs": num_ngbrs
        })

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(5)
    except plpy.SPIError:
        plpy.error(
            'Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(5)

    ## collect attributes
    numer = pu.get_attributes(result, 1)
    denom = pu.get_attributes(result, 2)

    weight = pu.get_weight(result, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_Rate(numer,
                                          denom,
                                          weight,
                                          permutations=permutations)

    # find units of significance
    quads = quad_position(lisa.q)

    return zip(lisa.Is, quads, lisa.p_sim, weight.id_order, lisa.y)
Example #2
0
def moran_local(t, attr, significance, num_ngbrs, permutations, geom_column,
                id_col, w_type):
    """
    Moran's I implementation for PL/Python
    Andy Eschbacher
    """
    # TODO: ensure that the significance output can be smaller that 1e-3 (0.001)
    # TODO: make a wishlist of output features (zscores, pvalues, raw local lisa, what else?)

    plpy.notice('** Constructing query')

    # geometries with attributes that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = {
        "id_col": id_col,
        "attr1": attr,
        "geom_col": geom_column,
        "table": t,
        "num_ngbrs": num_ngbrs
    }

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

    y = get_attributes(r, 1)
    w = get_weight(r, w_type)

    # calculate LISA values
    lisa = ps.Moran_Local(y, w)

    # find units of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order)
Example #3
0
def moran_local(subquery, attr, w_type, num_ngbrs, permutations, geom_col,
                id_col):
    """
    Moran's I implementation for PL/Python
    Andy Eschbacher
    """

    # geometries with attributes that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = {
        "id_col": id_col,
        "attr1": attr,
        "geom_col": geom_col,
        "subquery": subquery,
        "num_ngbrs": num_ngbrs
    }

    query = pu.construct_neighbor_query(w_type, qvals)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(5)
    except plpy.SPIError:
        plpy.error(
            'Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        return pu.empty_zipped_array(5)

    attr_vals = pu.get_attributes(result)
    weight = pu.get_weight(result, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local(attr_vals,
                                     weight,
                                     permutations=permutations)

    # find quadrants for each geometry
    quads = quad_position(lisa.q)

    return zip(lisa.Is, quads, lisa.p_sim, weight.id_order, lisa.y)
Example #4
0
def moran_local_rate(subquery, numerator, denominator,
                     w_type, num_ngbrs, permutations, geom_col, id_col):
    """
        Moran's I Local Rate
        Andy Eschbacher
    """
    # geometries with values that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = OrderedDict([("id_col", id_col),
                         ("numerator", numerator),
                         ("denominator", denominator),
                         ("geom_col", geom_col),
                         ("subquery", subquery),
                         ("num_ngbrs", num_ngbrs)])

    query = pu.construct_neighbor_query(w_type, qvals)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(5)
    except plpy.SPIError:
        plpy.error('Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(5)

    ## collect attributes
    numer = pu.get_attributes(result, 1)
    denom = pu.get_attributes(result, 2)

    weight = pu.get_weight(result, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_Rate(numer, denom, weight,
                                          permutations=permutations)

    # find quadrants for each geometry
    quads = quad_position(lisa.q)

    return zip(lisa.Is, quads, lisa.p_sim, weight.id_order, lisa.y)
Example #5
0
def moran_local(t, attr, significance, num_ngbrs, permutations, geom_column, id_col, w_type):
    """
    Moran's I implementation for PL/Python
    Andy Eschbacher
    """
    # TODO: ensure that the significance output can be smaller that 1e-3 (0.001)
    # TODO: make a wishlist of output features (zscores, pvalues, raw local lisa, what else?)

    plpy.notice('** Constructing query')

    # geometries with attributes that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = {"id_col": id_col,
            "attr1": attr,
            "geom_col": geom_column,
             "table": t,
             "num_ngbrs": num_ngbrs}

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

    y = get_attributes(r, 1)
    w = get_weight(r, w_type)

    # calculate LISA values
    lisa = ps.Moran_Local(y, w)

    # find units of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order)
Example #6
0
def moran_local_bv(subquery, attr1, attr2, permutations, geom_col, id_col,
                   w_type, num_ngbrs):
    """
        Moran's I (local) Bivariate (untested)
    """
    plpy.notice('** Constructing query')

    qvals = {
        "num_ngbrs": num_ngbrs,
        "attr1": attr1,
        "attr2": attr2,
        "subquery": subquery,
        "geom_col": geom_col,
        "id_col": id_col
    }

    query = pu.construct_neighbor_query(w_type, qvals)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(4)
    except plpy.SPIError:
        plpy.error("Error: areas of interest query failed, " \
                   "check input parameters")
        plpy.notice('** Query failed: "%s"' % query)
        return pu.empty_zipped_array(4)

    ## collect attributes
    attr1_vals = pu.get_attributes(result, 1)
    attr2_vals = pu.get_attributes(result, 2)

    # create weights
    weight = pu.get_weight(result, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_BV(attr1_vals,
                                        attr2_vals,
                                        weight,
                                        permutations=permutations)

    plpy.notice("len of Is: %d" % len(lisa.Is))

    # find clustering of significance
    lisa_sig = quad_position(lisa.q)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, weight.id_order)
def perform_1_ransac_segmentation(
    p
    , _ksearch
    , _search_radius
    , sac_model
    , _distance_weight
    , _max_iterations
    , _distance_threshold):
    """given a pointcloud, perform ransac segmetnation on it 
    :param p:  the point cloud
    :param _ksearch: number of neighboor considered for normal computation
    :param sac_model: the type of feature we are looking for. Can be pcl.SACMODEL_NORMAL_PLANE
    :param _distance_weight: between 0 and 1 . 0 make the filtering selective, 1 not selective
    :param _max_iterations: how many ransac iterations?
    :param _distance_threshold: how far can be a point from the feature to be considered in it?
    :return indices: the indices of the point in p that belongs to the feature
    :return model: the model of the feature
    """
    import plpy;
    import numpy as np ;
    import pcl ;
    reload(pcl) ;
    #prepare segmentation
    seg = p.make_segmenter_normals(ksearch=_ksearch, searchRadius = _search_radius) ; 
    
    toto = p.calc_normals(10,10) ;
    plpy.notice(toto);
    
    seg.set_optimize_coefficients (True);
    seg.set_model_type (sac_model)
    seg.set_normal_distance_weight (_distance_weight) #Note : playing with this make the result more (0.5) or less(0.1) selective
    seg.set_method_type (pcl.SAC_RANSAC)
    seg.set_max_iterations (_max_iterations)
    seg.set_distance_threshold (_distance_threshold)
    #segment
    indices, model = seg.segment()  ; 
    
    return indices, model;
Example #8
0
def moran_local_bv(subquery, attr1, attr2,
                   permutations, geom_col, id_col, w_type, num_ngbrs):
    """
        Moran's I (local) Bivariate (untested)
    """
    plpy.notice('** Constructing query')

    qvals = OrderedDict([("id_col", id_col),
                         ("attr1", attr1),
                         ("attr2", attr2),
                         ("geom_col", geom_col),
                         ("subquery", subquery),
                         ("num_ngbrs", num_ngbrs)])

    query = pu.construct_neighbor_query(w_type, qvals)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(4)
    except plpy.SPIError:
        plpy.error("Error: areas of interest query failed, " \
                   "check input parameters")
        plpy.notice('** Query failed: "%s"' % query)
        return pu.empty_zipped_array(4)

    ## collect attributes
    attr1_vals = pu.get_attributes(result, 1)
    attr2_vals = pu.get_attributes(result, 2)

    # create weights
    weight = pu.get_weight(result, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_BV(attr1_vals, attr2_vals, weight,
                                        permutations=permutations)

    plpy.notice("len of Is: %d" % len(lisa.Is))

    # find clustering of significance
    lisa_sig = quad_position(lisa.q)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, weight.id_order)
Example #9
0
def perform_1_ransac_segmentation(p, _ksearch, _search_radius, sac_model,
                                  _distance_weight, _max_iterations,
                                  _distance_threshold):
    """given a pointcloud, perform ransac segmetnation on it 
    :param p:  the point cloud
    :param _ksearch: number of neighboor considered for normal computation
    :param sac_model: the type of feature we are looking for. Can be pcl.SACMODEL_NORMAL_PLANE
    :param _distance_weight: between 0 and 1 . 0 make the filtering selective, 1 not selective
    :param _max_iterations: how many ransac iterations?
    :param _distance_threshold: how far can be a point from the feature to be considered in it?
    :return indices: the indices of the point in p that belongs to the feature
    :return model: the model of the feature
    """
    import plpy
    import numpy as np
    import pcl
    reload(pcl)
    #prepare segmentation
    seg = p.make_segmenter_normals(ksearch=_ksearch,
                                   searchRadius=_search_radius)

    toto = p.calc_normals(10, 10)
    plpy.notice(toto)

    seg.set_optimize_coefficients(True)
    seg.set_model_type(sac_model)
    seg.set_normal_distance_weight(
        _distance_weight
    )  #Note : playing with this make the result more (0.5) or less(0.1) selective
    seg.set_method_type(pcl.SAC_RANSAC)
    seg.set_max_iterations(_max_iterations)
    seg.set_distance_threshold(_distance_threshold)
    #segment
    indices, model = seg.segment()

    return indices, model
Example #10
0
def moran_rate(subquery, numerator, denominator, w_type, num_ngbrs,
               permutations, geom_col, id_col):
    """
    Moran's I Rate (global)
    Andy Eschbacher
    """
    qvals = {
        "id_col": id_col,
        "attr1": numerator,
        "attr2": denominator,
        "geom_col": geom_col,
        "subquery": subquery,
        "num_ngbrs": num_ngbrs
    }

    query = pu.construct_neighbor_query(w_type, qvals)

    plpy.notice('** Query: %s' % query)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(2)
        plpy.notice('** Query returned with %d rows' % len(result))
    except plpy.SPIError:
        plpy.error(
            'Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(2)

    ## collect attributes
    numer = pu.get_attributes(result, 1)
    denom = pu.get_attributes(result, 2)

    weight = pu.get_weight(result, w_type, num_ngbrs)

    ## calculate moran global rate
    lisa_rate = ps.esda.moran.Moran_Rate(numer,
                                         denom,
                                         weight,
                                         permutations=permutations)

    return zip([lisa_rate.I], [lisa_rate.EI])
Example #11
0
def moran(subquery, attr_name, w_type, num_ngbrs, permutations, geom_col,
          id_col):
    """
    Moran's I (global)
    Implementation building neighbors with a PostGIS database and Moran's I
     core clusters with PySAL.
    Andy Eschbacher
    """
    qvals = {
        "id_col": id_col,
        "attr1": attr_name,
        "geom_col": geom_col,
        "subquery": subquery,
        "num_ngbrs": num_ngbrs
    }

    query = pu.construct_neighbor_query(w_type, qvals)

    plpy.notice('** Query: %s' % query)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(2)
        plpy.notice('** Query returned with %d rows' % len(result))
    except plpy.SPIError:
        plpy.error(
            'Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(2)

    ## collect attributes
    attr_vals = pu.get_attributes(result)

    ## calculate weights
    weight = pu.get_weight(result, w_type, num_ngbrs)

    ## calculate moran global
    moran_global = ps.esda.moran.Moran(attr_vals,
                                       weight,
                                       permutations=permutations)

    return zip([moran_global.I], [moran_global.EI])
Example #12
0
def moran(subquery, attr_name,
          w_type, num_ngbrs, permutations, geom_col, id_col):
    """
    Moran's I (global)
    Implementation building neighbors with a PostGIS database and Moran's I
     core clusters with PySAL.
    Andy Eschbacher
    """
    qvals = OrderedDict([("id_col", id_col),
                         ("attr1", attr_name),
                         ("geom_col", geom_col),
                         ("subquery", subquery),
                         ("num_ngbrs", num_ngbrs)])

    query = pu.construct_neighbor_query(w_type, qvals)

    plpy.notice('** Query: %s' % query)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(2)
        plpy.notice('** Query returned with %d rows' % len(result))
    except plpy.SPIError:
        plpy.error('Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(2)

    ## collect attributes
    attr_vals = pu.get_attributes(result)

    ## calculate weights
    weight = pu.get_weight(result, w_type, num_ngbrs)

    ## calculate moran global
    moran_global = ps.esda.moran.Moran(attr_vals, weight,
                                       permutations=permutations)

    return zip([moran_global.I], [moran_global.EI])
Example #13
0
def moran_rate(subquery, numerator, denominator,
               w_type, num_ngbrs, permutations, geom_col, id_col):
    """
    Moran's I Rate (global)
    Andy Eschbacher
    """
    qvals = OrderedDict([("id_col", id_col),
                         ("attr1", numerator),
                         ("attr2", denominator)
                         ("geom_col", geom_col),
                         ("subquery", subquery),
                         ("num_ngbrs", num_ngbrs)])

    query = pu.construct_neighbor_query(w_type, qvals)

    plpy.notice('** Query: %s' % query)

    try:
        result = plpy.execute(query)
        # if there are no neighbors, exit
        if len(result) == 0:
            return pu.empty_zipped_array(2)
        plpy.notice('** Query returned with %d rows' % len(result))
    except plpy.SPIError:
        plpy.error('Error: areas of interest query failed, check input parameters')
        plpy.notice('** Query failed: "%s"' % query)
        plpy.notice('** Error: %s' % plpy.SPIError)
        return pu.empty_zipped_array(2)

    ## collect attributes
    numer = pu.get_attributes(result, 1)
    denom = pu.get_attributes(result, 2)

    weight = pu.get_weight(result, w_type, num_ngbrs)

    ## calculate moran global rate
    lisa_rate = ps.esda.moran.Moran_Rate(numer, denom, weight,
                                         permutations=permutations)

    return zip([lisa_rate.I], [lisa_rate.EI])
Example #14
0
def moran_local_rate(t, numerator, denominator, significance, num_ngbrs, permutations, geom_column, id_col, w_type):
    """
    Moran's I Local Rate
    Andy Eschbacher
    """

    plpy.notice('** Constructing query')

    # geometries with attributes that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = {"id_col": id_col,
             "numerator": numerator,
             "denominator": denominator,
             "geom_col": geom_column,
             "table": t,
             "num_ngbrs": num_ngbrs}

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Error: %s' % plpy.SPIError)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

        plpy.notice('r.nrows() = %d' % r.nrows())

    ## collect attributes
    numer = get_attributes(r, 1)
    denom = get_attributes(r, 2)

    w = get_weight(r, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_Rate(numer, denom, w, permutations=permutations)

    # find units of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    ## TODO: Decide on which return values here
    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order, lisa.y)
Example #15
0
def moran_local_bv(t, attr1, attr2, significance, num_ngbrs, permutations, geom_column, id_col, w_type):
    plpy.notice('** Constructing query')

    qvals = {"num_ngbrs": num_ngbrs,
             "attr1": attr1,
             "attr2": attr2,
             "table": t,
             "geom_col": geom_column,
             "id_col": id_col}

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Error: %s' % plpy.SPIError)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

    ## collect attributes
    attr1_vals = get_attributes(r, 1)
    attr2_vals = get_attributes(r, 2)

    # create weights
    w = get_weight(r, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_BV(attr1_vals, attr2_vals, w)

    plpy.notice("len of Is: %d" % len(lisa.Is))

    # find clustering of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order)
Example #16
0
def moran_local_rate(t, numerator, denominator, significance, num_ngbrs,
                     permutations, geom_column, id_col, w_type):
    """
    Moran's I Local Rate
    Andy Eschbacher
    """

    plpy.notice('** Constructing query')

    # geometries with attributes that are null are ignored
    # resulting in a collection of not as near neighbors

    qvals = {
        "id_col": id_col,
        "numerator": numerator,
        "denominator": denominator,
        "geom_col": geom_column,
        "table": t,
        "num_ngbrs": num_ngbrs
    }

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Error: %s' % plpy.SPIError)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

        plpy.notice('r.nrows() = %d' % r.nrows())

    ## collect attributes
    numer = get_attributes(r, 1)
    denom = get_attributes(r, 2)

    w = get_weight(r, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_Rate(numer,
                                          denom,
                                          w,
                                          permutations=permutations)

    # find units of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    ## TODO: Decide on which return values here
    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order, lisa.y)
Example #17
0
def moran_local_bv(t, attr1, attr2, significance, num_ngbrs, permutations,
                   geom_column, id_col, w_type):
    plpy.notice('** Constructing query')

    qvals = {
        "num_ngbrs": num_ngbrs,
        "attr1": attr1,
        "attr2": attr2,
        "table": t,
        "geom_col": geom_column,
        "id_col": id_col
    }

    q = get_query(w_type, qvals)

    try:
        r = plpy.execute(q)
        plpy.notice('** Query returned with %d rows' % len(r))
    except plpy.SPIError:
        plpy.notice('** Query failed: "%s"' % q)
        plpy.notice('** Error: %s' % plpy.SPIError)
        plpy.notice('** Exiting function')
        return zip([None], [None], [None], [None])

    ## collect attributes
    attr1_vals = get_attributes(r, 1)
    attr2_vals = get_attributes(r, 2)

    # create weights
    w = get_weight(r, w_type, num_ngbrs)

    # calculate LISA values
    lisa = ps.esda.moran.Moran_Local_BV(attr1_vals, attr2_vals, w)

    plpy.notice("len of Is: %d" % len(lisa.Is))

    # find clustering of significance
    lisa_sig = lisa_sig_vals(lisa.p_sim, lisa.q, significance)

    plpy.notice('** Finished calculations')

    return zip(lisa.Is, lisa_sig, lisa.p_sim, w.id_order)
Example #18
0
def dijkstra(config, source, target, stoptosensor=''):
    '''
    returns the transfo list needed to go from source referential to target
    referential
    '''

    # get all transformations involved in the transfo tree list
    transfo_list = plpy.execute(
        """
        select array_aggmult(tt.transfos) as trf
        from li3ds.platform_config pf
        join li3ds.transfo_tree tt on tt.id = ANY(pf.transfo_trees)
        where pf.id = {}
        """.format(config)
    )[0]['trf']

    transfo_list_coma_separated = ','.join(map(str, transfo_list))

    # list of adjacent nodes (referentials)
    # adj_list = [ref1: [ref7, ref1], ref2: [ref3]...]
    result = plpy.execute(
        """
        select
            r.id
            , array_agg(t.target)
              filter (where t.target is not NULL) as adj_list
              -- we keep a NULL column instead of an array
              -- with a null value inside
        from li3ds.referential r
        left join li3ds.transfo t
            -- we only keep direct transformations
            on t.source = r.id
            and array[t.id] <@ array[{}]::integer[]
        group by r.id
        """.format(transfo_list_coma_separated)
    )
    # build graph
    # graph = {ref1: [(1, ref7), (1, ref3)...], ...}
    graph = {}
    for column in result:
        if column['adj_list'] is not None:
            graph[column['id']] = [(1, idt) for idt in column['adj_list']]
        else:
            graph[column['id']] = []

    if source not in graph:
        raise Exception("No referential with id {}".format(source))
    if target not in graph:
        raise Exception("No referential with id {}".format(target))

    M = set()
    d = {source: 0}
    p = {}
    next_nodes = [(0, source)]

    while next_nodes:

        dx, x = heappop(next_nodes)
        if x in M:
            continue

        M.add(x)

        for w, y in graph[x]:
            if y in M:
                continue
            dy = dx + w
            if y not in d or d[y] > dy:
                d[y] = dy
                heappush(next_nodes, (dy, y))
                p[y] = x

    shortest_path = [target]
    x = target
    while x != source:
        try:
            x = p[x]
        except KeyError:
            plpy.notice("No path from ref:{} to ref:{} with config {}"
                        .format(source, target, config))
            return []
        shortest_path.insert(0, x)

    if stoptosensor:
        # if a sensor type was requested we want to return
        # the first referential matching that type
        for ref in shortest_path:
            found = plpy.execute("""
                select r.id, s.type from referential r
                join sensor s on r.sensor = s.id
                where r.id = {}""".format(ref))
            if found[0]['type'] == stoptosensor:
                return [found[0]['id']]
        raise Exception(
            "No referential in path with type {}".format(stoptosensor))

    # we have referentials now we need all transformations
    # assembling refs by pair
    ref_pair = [
        shortest_path[i:i + 2]
        for i in range(0, len(shortest_path) - 1)]

    transfos = []
    for ref_source, ref_target in ref_pair:
        transfos.append(plpy.execute(
            """
            select id
            from li3ds.transfo
            where source = {} and target = {}
            and array[id] <@ array[{}]::integer[]
            """.format(ref_source, ref_target, transfo_list_coma_separated))[0]['id'])

    return transfos