def calc_postgis(self):
pg_io = self.inputs[0]
original_projection = pg_io.epsg
io_query, params = pg_io.get_query()
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(original_projection))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(
pg_io.geom_column, 3857)
else:
original_projection = None
buffer_query = 'ST_Union(ST_Buffer({}, %s))'.format(geom_query)
if original_projection:
buffer_query = 'ST_Transform({}, {})'.format(buffer_query,
original_projection)
query = 'SELECT {buffer} as {geocol} ' \
'FROM ({query}) as foo'.format(buffer=buffer_query,
geocol=pg_io.geom_column,
query=io_query.rstrip(';'))
params.insert(0, self.buffer_size)
logger.debug(query)
return df_from_postgis(pg_io.engine, query, params,
pg_io.geom_column, pg_io.epsg)
def calc_postgis(self):
"""
Uses DWithin plus K-Nearest Neighbor (KNN) query
"""
featureio = self.inputs[0]
pointio = self.inputs[1]
feature_geom, epsg = featureio.geom_column, featureio.epsg
point_json = json.loads(pointio.read(
format=formats.JSON))['features'][0]
point_epsg = pointio.get_epsg()
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(epsg))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
epsg = 3857
io_query, params = featureio.get_query()
point_geom = 'ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(\'' \
'{geojson}\'),{point_epsg}), {epsg})'.\
format(geojson=json.dumps(point_json['geometry']),
point_epsg=point_epsg, epsg=epsg)
dist1 = """, (SELECT ST_Distance(
ST_Transform({table0}.{geom0},{epsg}),
ST_Transform(point, {epsg}))
FROM {point_geom} as point
ORDER BY {table0}.{geom0} <#> point LIMIT 1) as distance FROM
""".format(table0=featureio.table,
geom0=feature_geom,
point_geom=point_geom,
epsg=epsg)
dist2 = """
WHERE ST_DWithin({point_geom},
ST_Transform({table0}.{geom0},{epsg}), {distance})
""".format(table0=featureio.table,
geom0=feature_geom,
point_geom=point_geom,
epsg=epsg,
distance=self.distance)
dist3 = ' ORDER BY distance ASC'
query = re.sub('FROM', dist1, io_query).rstrip(';')
if 'WHERE' in query:
query = re.sub('WHERE', dist2 + ' AND ', query)
else:
query += dist2
query += dist3
logger.debug(query)
return df_from_postgis(featureio.engine,
query, params, feature_geom, epsg)
def calc_postgis(self):
"""
Uses DWithin plus K-Nearest Neighbor (KNN) query
"""
featureio = self.inputs[0]
pointio = self.inputs[1]
feature_geom, epsg = featureio.geom_column, featureio.epsg
point_json = json.loads(
pointio.read(format=formats.JSON))['features'][0]
point_epsg = pointio.get_epsg()
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(epsg))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
epsg = 3857
io_query, params = featureio.get_query()
point_geom = 'ST_Transform(ST_SetSRID(ST_GeomFromGeoJSON(\'' \
'{geojson}\'),{point_epsg}), {epsg})'.\
format(geojson=json.dumps(point_json['geometry']),
point_epsg=point_epsg, epsg=epsg)
dist1 = """, (SELECT ST_Distance(
ST_Transform({table0}.{geom0},{epsg}),
ST_Transform(point, {epsg}))
FROM {point_geom} as point
ORDER BY {table0}.{geom0} <#> point LIMIT 1) as distance FROM
""".format(table0=featureio.table,
geom0=feature_geom,
point_geom=point_geom,
epsg=epsg)
dist2 = """
WHERE ST_DWithin({point_geom},
ST_Transform({table0}.{geom0},{epsg}), {distance})
""".format(table0=featureio.table,
geom0=feature_geom,
point_geom=point_geom,
epsg=epsg,
distance=self.distance)
dist3 = ' ORDER BY distance ASC'
query = re.sub('FROM', dist1, io_query).rstrip(';')
if 'WHERE' in query:
query = re.sub('WHERE', dist2 + ' AND ', query)
else:
query += dist2
query += dist3
logger.debug(query)
return df_from_postgis(featureio.engine, query, params, feature_geom,
epsg)
def calc_postgis(self):
pg_io = self.inputs[0]
geom0, epsg = pg_io.geom_column, pg_io.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(epsg)
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(geom0, 3857)
geom_query = ', ST_Area({}) as area'.format(geom_query)
query, params = pg_io.get_query()
query = query.replace('FROM', '{} FROM'.format(geom_query))
logger.debug(query)
return df_from_postgis(pg_io.engine, query, params, geom0, epsg)
def calc_postgis(self):
pg_io = self.inputs[0]
geom0, epsg = pg_io.geom_column, pg_io.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(epsg)
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(geom0, 3857)
geom_query = ', ST_Area({}) as area'.format(geom_query)
query, params = pg_io.get_query()
query = query.replace('FROM', '{} FROM'.format(geom_query))
logger.debug(query)
return df_from_postgis(pg_io.engine, query, params, geom0, epsg)
def calc_postgis(self):
pg_io = self.inputs[0]
io_query, params = pg_io.get_query()
geom0, epsg = pg_io.geom_column, pg_io.epsg
if self.combined:
query = 'SELECT ST_Centroid(ST_Union({geom})) as {geom}' \
' from ({query}) as foo'.format(geom=geom0,
query=io_query.rstrip(';'))
else:
query = re.sub('"{}"'.format(geom0),
'ST_Centroid("{geom}") as {geom}'.format(
geom=geom0), io_query, 1)
return df_from_postgis(pg_io.engine, query, params, geom0, epsg)
def calc_postgis(self):
pg_io = self.inputs[0]
io_query, params = pg_io.get_query()
geom0, epsg = pg_io.geom_column, pg_io.epsg
if self.combined:
query = 'SELECT ST_Centroid(ST_Union({geom})) as {geom}' \
' from ({query}) as foo'.format(geom=geom0,
query=io_query.rstrip(';'))
else:
query = re.sub(
'"{}"'.format(geom0),
'ST_Centroid("{geom}") as {geom}'.format(geom=geom0), io_query,
1)
return df_from_postgis(pg_io.engine, query, params, geom0, epsg)
def calc_postgis(self):
featureio = self.inputs[0]
geom0, epsg = featureio.geom_column, featureio.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(epsg)
geom_query = geom0
geometry_type = featureio.geometry_type
length_func = 'ST_Perimeter' if 'POLYGON' in geometry_type.upper() \
else 'ST_Length'
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(geom_query, 3857)
geom_query = ', {}({}) as length'.format(length_func, geom_query)
query, params = featureio.get_query()
query = query.replace('FROM', '{} FROM'.format(geom_query))
logger.debug(query)
return df_from_postgis(featureio.engine, query, params, geom0, epsg)
def calc_postgis(self):
featureio = self.inputs[0]
geom0, epsg = featureio.geom_column, featureio.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(epsg)
geom_query = geom0
geometry_type = featureio.geometry_type
length_func = 'ST_Perimeter' if 'POLYGON' in geometry_type.upper() \
else 'ST_Length'
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(
geom_query, 3857)
geom_query = ', {}({}) as length'.format(length_func, geom_query)
query, params = featureio.get_query()
query = query.replace('FROM', '{} FROM'.format(geom_query))
logger.debug(query)
return df_from_postgis(featureio.engine, query, params, geom0, epsg)
def calc_postgis(self):
cross_queries = []
cross_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
cross_queries.append(io_query.rstrip(';'))
cross_params.extend(params)
joinstr = ' AND ' if 'WHERE' in cross_queries[0].upper() else ' WHERE '
query = '{query0} {join} (SELECT ST_Touches(ST_Transform(' \
'{table}.{geom0},{epsg}), ST_Union(ST_Transform(' \
'q2.{geom1},{epsg}))) from ({query1}) as q2)'\
.format(query0=cross_queries[0], join=joinstr, geom0=geom0,
geom1=geom1, epsg=epsg, query1=cross_queries[1],
table=first.table)
return df_from_postgis(first.engine, query, cross_params, geom0, epsg)
def calc_postgis(self):
cross_queries = []
cross_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
cross_queries.append(io_query.rstrip(';'))
cross_params.extend(params)
joinstr = ' AND ' if 'WHERE' in cross_queries[0].upper() else ' WHERE '
query = '{query0} {join} (SELECT ST_Touches(ST_Transform(' \
'{table}.{geom0},{epsg}), ST_Union(ST_Transform(' \
'q2.{geom1},{epsg}))) from ({query1}) as q2)'\
.format(query0=cross_queries[0], join=joinstr, geom0=geom0,
geom1=geom1, epsg=epsg, query1=cross_queries[1],
table=first.table)
return df_from_postgis(first.engine, query, cross_params, geom0, epsg)
def calc_postgis(self):
first = self.inputs[0]
within_queries = []
within_params = []
geom0 = first.geom_column
epsg = first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
within_queries.append(io_query.rstrip(';'))
within_params.extend(params)
joinstr = ' AND ' if 'WHERE' in within_queries[0].upper() else ' WHERE '
query = '{query0} {join} ST_Within(ST_Transform({geom0},{epsg}), ' \
'(SELECT ST_Union(ST_TRANSFORM({geom1},{epsg})) ' \
'from ({query1}) as q2))'\
.format(query0=within_queries[0], join=joinstr, geom0=geom0,
geom1=geom1, epsg=epsg, query1=within_queries[1])
return df_from_postgis(first.engine, query, params, geom0, epsg)
def calc_postgis(self):
equals_queries = []
equals_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
equals_queries.append(io_query.rstrip(';'))
equals_params.extend(params)
joinstr = ' AND ' if 'WHERE' in equals_queries[0].upper() else ' WHERE '
query = '{query0} {join} {geom0} IN (SELECT {geom1} ' \
'FROM ({query1}) as second)'.format(query0=equals_queries[0],
query1=equals_queries[1],
join=joinstr,
geom0=geom0,
geom1=geom1)
logger.debug(query)
return df_from_postgis(first.engine, query, equals_params, geom0, epsg)
def calc_postgis(self):
first = self.inputs[0]
within_queries = []
within_params = []
geom0 = first.geom_column
epsg = first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
within_queries.append(io_query.rstrip(';'))
within_params.extend(params)
joinstr = ' AND ' if 'WHERE' in within_queries[0].upper(
) else ' WHERE '
query = '{query0} {join} ST_Within(ST_Transform({geom0},{epsg}), ' \
'(SELECT ST_Union(ST_TRANSFORM({geom1},{epsg})) ' \
'from ({query1}) as q2))'\
.format(query0=within_queries[0], join=joinstr, geom0=geom0,
geom1=geom1, epsg=epsg, query1=within_queries[1])
return df_from_postgis(first.engine, query, params, geom0, epsg)
def calc_postgis(self):
equals_queries = []
equals_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
equals_queries.append(io_query.rstrip(';'))
equals_params.extend(params)
joinstr = ' AND ' if 'WHERE' in equals_queries[0].upper(
) else ' WHERE '
query = '{query0} {join} {geom0} IN (SELECT {geom1} ' \
'FROM ({query1}) as second)'.format(query0=equals_queries[0],
query1=equals_queries[1],
join=joinstr,
geom0=geom0,
geom1=geom1)
logger.debug(query)
return df_from_postgis(first.engine, query, equals_params, geom0, epsg)
def calc_postgis(self):
"""
Uses K-Nearest Neighbor (KNN) query
"""
diff_queries = []
diff_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(epsg))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
epsg = 3857
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
diff_queries.append(io_query.rstrip(';'))
diff_params.insert(0, params)
diff_params = [item for x in diff_params for item in x]
dist1 = """, (SELECT ST_Distance(
ST_Transform({table0}.{geom0},{epsg}),
ST_Transform(query2.{geom1},{epsg}))
as distance
""".format(table0=self.inputs[0].table,
geom0=geom0,
geom1=geom1,
epsg=epsg)
dist2 = """
ORDER BY {table0}.{geom0} <#> query2.{geom1} LIMIT 1) FROM
""".format(table0=self.inputs[0].table,
geom0=geom0,
geom1=geom1,
epsg=epsg)
dist3 = ' ORDER BY distance ASC'
query = re.sub(
'FROM', dist1 + ' FROM (' + diff_queries[1] + ') as query2 ' +
dist2, diff_queries[0]) + dist3
return df_from_postgis(first.engine, query, diff_params, geom0, epsg)
def calc_postgis(self):
union_queries = []
union_params = []
first = self.inputs[0]
second = self.inputs[1]
geom0, epsg = first.geom_column, first.epsg
geom1, epsg1 = second.geom_column, second.epsg
if ''.join(first.columns) != ''.join(second.columns):
raise GaiaException('Inputs must have the same columns')
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
union_queries.append(io_query.rstrip(';'))
union_params.extend(params)
if epsg1 != epsg:
geom1_query = 'ST_Transform({},{})'.format(geom1, epsg)
union_queries[1] = union_queries[1].replace(
'"{}"'.format(geom1), geom1_query)
query = '({query0}) UNION ({query1})'\
.format(query0=union_queries[0], query1=union_queries[1])
return df_from_postgis(first.engine, query, union_params, geom0, epsg)
def calc_postgis(self):
"""
Uses K-Nearest Neighbor (KNN) query
"""
diff_queries = []
diff_params = []
first = self.inputs[0]
geom0, epsg = first.geom_column, first.epsg
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(epsg))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
epsg = 3857
geom1 = self.inputs[1].geom_column
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
diff_queries.append(io_query.rstrip(';'))
diff_params.insert(0, params)
diff_params = [item for x in diff_params for item in x]
dist1 = """, (SELECT ST_Distance(
ST_Transform({table0}.{geom0},{epsg}),
ST_Transform(query2.{geom1},{epsg}))
as distance
""".format(table0=self.inputs[0].table,
geom0=geom0,
geom1=geom1,
epsg=epsg)
dist2 = """
ORDER BY {table0}.{geom0} <#> query2.{geom1} LIMIT 1) FROM
""".format(table0=self.inputs[0].table,
geom0=geom0,
geom1=geom1,
epsg=epsg)
dist3 = ' ORDER BY distance ASC'
query = re.sub('FROM', dist1 + ' FROM (' + diff_queries[1] +
') as query2 ' + dist2, diff_queries[0]) + dist3
return df_from_postgis(first.engine, query, diff_params, geom0, epsg)
def calc_postgis(self):
union_queries = []
union_params = []
first = self.inputs[0]
second = self.inputs[1]
geom0, epsg = first.geom_column, first.epsg
geom1, epsg1 = second.geom_column, second.epsg
if ''.join(first.columns) != ''.join(second.columns):
raise GaiaException('Inputs must have the same columns')
for pg_io in self.inputs:
io_query, params = pg_io.get_query()
union_queries.append(io_query.rstrip(';'))
union_params.extend(params)
if epsg1 != epsg:
geom1_query = 'ST_Transform({},{})'.format(geom1, epsg)
union_queries[1] = union_queries[1].replace(
'"{}"'.format(geom1), geom1_query)
query = '({query0}) UNION ({query1})'\
.format(query0=union_queries[0], query1=union_queries[1])
return df_from_postgis(first.engine,
query, union_params, geom0, epsg)
def calc_postgis(self):
pg_io = self.inputs[0]
original_projection = pg_io.epsg
io_query, params = pg_io.get_query()
srs = osr.SpatialReference()
srs.ImportFromEPSG(int(original_projection))
if not srs.GetAttrValue('UNIT').lower().startswith('met'):
geom_query = 'ST_Transform({}, {})'.format(pg_io.geom_column, 3857)
else:
original_projection = None
buffer_query = 'ST_Union(ST_Buffer({}, %s))'.format(geom_query)
if original_projection:
buffer_query = 'ST_Transform({}, {})'.format(
buffer_query, original_projection)
query = 'SELECT {buffer} as {geocol} ' \
'FROM ({query}) as foo'.format(buffer=buffer_query,
geocol=pg_io.geom_column,
query=io_query.rstrip(';'))
params.insert(0, self.buffer_size)
logger.debug(query)
return df_from_postgis(pg_io.engine, query, params, pg_io.geom_column,
pg_io.epsg)
