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)
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)
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)
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)
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)
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;
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)
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
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])
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])
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])
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])
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)
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)
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)
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)
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