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')
