def getParcelSCL(dataset, pid, ptype=''): conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcel_id = dataset['pcolumns']['parcel_id'] try: getTableDataSql = f""" SELECT h.obsid, h.hist FROM {dataset['tables']['scl']} h, {dataset['tables']['parcels']}{ptype} p WHERE h.pid = p.ogc_fid And p.{parcel_id} = '{pid}' ORDER By h.obsid Asc; """ # Return a list of tuples cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: print("No SCL time series found for", f"{dataset['tables']['parcels']}{ptype}") return data except Exception as err: print("Did not find data, please select the right database and table: ", err) return data.append('Ended with no data')
def getS2frames(dataset, pid, start, end, ptype=''): """Get the sentinel images frames from dias cataloge for the given parcel""" conn = db.conn(dataset['db']) dias_catalog = dataset['tables']['dias_catalog'] parcels_table = dataset['tables']['parcels'] parcel_id = dataset['pcolumns']['parcel_id'] # Get the S2 frames that cover a parcel identified by parcel # ID from the dias_catalogue for the selected date. end_date = pd.to_datetime(end) + pd.DateOffset(days=1) getS2framesSql = f""" SELECT reference, obstime, status FROM {dias_catalog}, {parcels_table}{ptype} WHERE card = 's2' And footprint && st_transform(wkb_geometry, 4326) And {parcel_id} = '{pid}' And obstime between '{start}' and '{end_date}' ORDER by obstime asc; """ # Read result set into a pandas dataframe df_s2frames = pd.read_sql_query(getS2framesSql, conn) return df_s2frames['reference'].tolist()
def getParcelById(dataset, pid, ptype='', withGeometry=False, wgs84=False): conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcels_table = dataset['tables']['parcels'] try: logging.debug("start queries") getTableSrid = f""" SELECT Find_SRID('', '{parcels_table}{ptype}', 'wkb_geometry');""" logging.debug(getTableSrid) cur.execute(getTableSrid) srid = cur.fetchone()[0] logging.debug(srid) cropname = dataset['pcolumns']['crop_name'] cropcode = dataset['pcolumns']['crop_code'] parcel_id = dataset['pcolumns']['parcel_id'] if withGeometry: if wgs84: geometrySql = ", st_asgeojson(st_transform(wkb_geometry, 4326)) as geom" else: geometrySql = ", st_asgeojson(wkb_geometry) as geom" else: geometrySql = "" getTableDataSql = f""" SELECT {parcel_id}::text as pid, {cropname} as cropname, {cropcode}::text as cropcode, st_srid(wkb_geometry) as srid{geometrySql}, st_area(st_transform(wkb_geometry, 3035))::integer as area, st_X(st_transform(st_centroid(wkb_geometry), 4326)) as clon, st_Y(st_transform(st_centroid(wkb_geometry), 4326)) as clat FROM {parcels_table}{ptype} WHERE {parcel_id} = '{pid}'; """ # Return a list of tuples # print(getTableDataSql) cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: logging.debug( f"No parcel found in the selected table with id ({pid}).") return data except Exception as err: print(err) logging.debug("Did not find data, please select the right database", "and table: ", err) return data.append('Ended with no data')
def getParcelPeers(dataset, pid, distance, maxPeers, ptype=''): conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcels_table = dataset['tables']['parcels'] try: logging.debug("start queries") getTableSrid = f""" SELECT Find_SRID('', '{parcels_table}{ptype}', 'wkb_geometry');""" logging.debug(getTableSrid) cur.execute(getTableSrid) srid = cur.fetchone()[0] logging.debug(srid) cropname = dataset['pcolumns']['crop_name'] parcel_id = dataset['pcolumns']['parcel_id'] getTableDataSql = f""" WITH current_parcel AS (select {cropname}, ST_Transform(wkb_geometry,3035) as geom FROM {parcels_table}{ptype} WHERE {parcel_id} = '{pid}') SELECT {parcel_id}::text as pid, st_distance(ST_Transform(wkb_geometry,3035), (SELECT geom FROM current_parcel)) As distance FROM {parcels_table}{ptype} WHERE {cropname} = (select {cropname} FROM current_parcel) And {parcel_id} != '{pid}' And st_dwithin(ST_Transform(wkb_geometry,3035), (SELECT geom FROM current_parcel), {distance}) And st_area(ST_Transform(wkb_geometry,3035)) > 3000.0 ORDER by st_distance(ST_Transform(wkb_geometry,3035), (SELECT geom FROM current_parcel)) asc LIMIT {maxPeers}; """ # Return a list of tuples # print(getTableDataSql) cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: print( "No parcel peers found in", f"{parcels_table} within {distance} meters from parcel {pid}") return data except Exception as err: print( "Did not find data, please select the right database and table: ", err) return data.append('Ended with no data')
def getTableCentroid(dataset, ptype=''): conn = db.conn(dataset['db']) getTablePolygonSql = f""" SELECT ST_Asgeojson(ST_Transform(ST_PointOnSurface(ST_Union(geom)), 4326)) As center FROM (SELECT wkb_geometry FROM {dataset['tables']['parcels']}{ptype} LIMIT 100) AS t(geom); """ # Read result set into a pandas dataframe df_tcent = pd.read_sql_query(getTablePolygonSql, conn) return df_tcent
def getParcelWeatherTS(dataset, pid, ptype): """Get the time series for the given parcel""" conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcels_table = dataset['tables']['parcels'] env_table = dataset['tables']['env'] parcel_id = dataset['pcolumns']['parcel_id'] try: getTableDataSql = f""" SELECT TO_CHAR(meteo_date, 'YYYY-MM-DD') meteo_date, tmin,tmax,tmean,prec FROM {env_table} e, {parcels_table}{ptype} p, public.era5_data, public.era5_grid WHERE p.{parcel_id} = '{pid}' AND e.grid_id = era5_grid.grid_id AND era5_grid.grid_id = era5_data.grid_id AND e.pid = p.ogc_fid ORDER BY meteo_date; """ # Return a list of tuples # print(getTableDataSql) cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: print("No time series found for", f"{pid} in the selected table '{env_table}'") return data except Exception as err: print("Did not find data, please select the right database and table: ", err) return data.append('Ended with no data')
def pids(dataset, limit=1, ptype='', random=False): conn = db.conn(dataset['db']) if random: randomSql = "TABLESAMPLE SYSTEM(0.1)" else: randomSql = "" getSql = f""" SELECT {dataset['pcolumns']['parcel_id']}::text as pids FROM {dataset['tables']['parcels']}{ptype} {randomSql} LIMIT {limit}; """ # Read result set into a pandas dataframe df = pd.read_sql_query(getSql, conn) return df
def getSRID(dataset, ptype=''): """Get the SRID""" # Get parcels SRID. conn = db.conn(dataset['db']) pgq_srid = f""" SELECT ST_SRID(wkb_geometry) FROM {dataset['tables']['parcels']}{ptype} LIMIT 1; """ df_srid = pd.read_sql_query(pgq_srid, conn) srid = df_srid['st_srid'][0] target_EPSG = int(srid) return target_EPSG
def getPolygonCentroid(dataset, pid, ptype=''): """Get the centroid of the given polygon""" conn = db.conn(dataset['db']) parcel_id = dataset['pcolumns']['parcel_id'] getParcelPolygonSql = f""" SELECT ST_Asgeojson(ST_transform(ST_Centroid(wkb_geometry), 4326)) As center, ST_Asgeojson(st_transform(wkb_geometry, 4326)) As polygon FROM {dataset['tables']['parcels']}{ptype} WHERE {parcel_id} = '{pid}' LIMIT 1; """ # Read result set into a pandas dataframe df_pcent = pd.read_sql_query(getParcelPolygonSql, conn) return df_pcent
def getParcelCentroid(dataset, pid, ptype=''): conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcel_id = dataset['pcolumns']['parcel_id'] try: getTableDataSql = f""" SELECT ST_Asgeojson(ST_transform(ST_Centroid(wkb_geometry), 4326)) FROM {dataset['tables']['parcels']}{ptype} WHERE {parcel_id} = '{pid}' LIMIT 1; """ # Return a list of tuples cur.execute(getTableDataSql) json_centroid = cur.fetchall()[0] return json.loads(json_centroid[0])['coordinates'] except Exception as err: print("Can not get the parcel centroid: ", err) return data.append('Ended with no data')
def getParcelTimeSeries(dataset, pid, ptype='', tstype='s2', band=None, scl=True, ref=False): """Get the time series for the given parcel""" conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] sigs_table = dataset['tables'][tstype] dias_catalog = dataset['tables']['dias_catalog'] parcels_table = dataset['tables']['parcels'] parcel_id = dataset['pcolumns']['parcel_id'] from_hists = f", {dataset['tables']['scl']} h" if scl else '' select_scl = ', h.hist' if scl else '' select_ref = ', d.reference' if ref else '' where_shid = 'And s.pid = h.pid And s.obsid = h.obsid' if scl else '' where_band = f"And s.band = '{band}' " if band else '' if tstype.lower() == 's2': where_tstype = "And band IN ('B02', 'B03', 'B04', 'B05', 'B08', 'B11', 'B2', 'B3', 'B4', 'B5', 'B8') " elif tstype.lower() == 'c6': where_tstype = "And band IN ('VVc', 'VHc') " elif tstype.lower() == 'bs': where_tstype = "And band IN ('VVb', 'VHb') " else: where_tstype = "" try: getTableDataSql = f""" SELECT extract('epoch' from d.obstime), s.band, s.count, s.mean, s.std, s.min, s.p25, s.p50, s.p75, s.max{select_scl}{select_ref} FROM {parcels_table}{ptype} p, {sigs_table} s, {dias_catalog} d{from_hists} WHERE p.ogc_fid = s.pid And p.{parcel_id} = '{pid}' And s.obsid = d.id {where_shid} {where_band} {where_tstype} ORDER By obstime, band asc; """ # Return a list of tuples # print(getTableDataSql) cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: print("No time series found for", f"{pid} in the selected signatures table '{sigs_table}'") return data except Exception as err: print("Did not find data, please select the right database and table: ", err) return data.append('Ended with no data')
def getParcelsByPolygon(dataset, polygon, ptype='', withGeometry=False, only_ids=True, wgs84=False): polygon = polygon.replace('_', ' ').replace('-', ',') conn = db.conn(dataset['db']) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) data = [] parcels_table = dataset['tables']['parcels'] try: logging.debug("start queries") getTableSrid = f""" SELECT Find_SRID('', '{parcels_table}{ptype}', 'wkb_geometry');""" logging.debug(getTableSrid) cur.execute(getTableSrid) srid = cur.fetchone()[0] logging.debug(srid) cropname = dataset['pcolumns']['crop_name'] cropcode = dataset['pcolumns']['crop_code'] parcel_id = dataset['pcolumns']['parcel_id'] if withGeometry: if wgs84: geometrySql = ", st_asgeojson(st_transform(wkb_geometry, 4326)) as geom" else: geometrySql = ", st_asgeojson(wkb_geometry) as geom" else: geometrySql = "" if only_ids: selectSql = f"{parcel_id} as pid{geometrySql}" else: selectSql = f""" {parcel_id} as pid, {cropname} As cropname, {cropcode} As cropcode, st_srid(wkb_geometry) As srid{geometrySql}, st_area(st_transform(wkb_geometry, 3035))::integer As area, st_X(st_transform(st_centroid(wkb_geometry), 4326)) As clon, st_Y(st_transform(st_centroid(wkb_geometry), 4326)) As clat""" getTableDataSql = f""" SELECT {selectSql} FROM {parcels_table}{ptype} WHERE st_intersects(wkb_geometry, st_transform(st_geomfromtext('POLYGON(({polygon}))', 4326), {srid})) LIMIT 100; """ # Return a list of tuples cur.execute(getTableDataSql) rows = cur.fetchall() data.append(tuple(etup.name for etup in cur.description)) if len(rows) > 0: for r in rows: data.append(tuple(r)) else: print(f"No parcel found in {parcels_table}{ptype} that", "intersects with the polygon.") return data except Exception as err: print("Did not find data, please select the right database and table: ", err) return data.append('Ended with no data')