def __init__(
self,
*,
geom_table_column_names: Union[str, Iterable[str]],
geom_table: Union[Query, str],
mapping_table: Optional[Union[Query, str]] = None,
geom_column: str = "geom",
geom_table_join_on: Optional[str] = None,
):
if isinstance(geom_table_column_names, str):
location_id_column_names = get_name_and_alias(
geom_table_column_names)[1]
else:
location_id_column_names = [
get_name_and_alias(c)[1] for c in geom_table_column_names
]
super().__init__(
geom_table_column_names=geom_table_column_names,
location_id_column_names=location_id_column_names,
geom_table=geom_table,
mapping_table=mapping_table,
geom_column=geom_column,
geom_table_join_on=geom_table_join_on,
location_table_join_on="id" if mapping_table is not None else None,
)
def __init__(
self,
*,
geom_table_column_names: Union[str, Iterable[str]],
geom_table: Union[Query, str],
geom_column: str = "geom",
):
if isinstance(geom_table_column_names, str):
location_id_column_names = get_name_and_alias(geom_table_column_names)[1]
else:
location_id_column_names = [
get_name_and_alias(c)[1] for c in geom_table_column_names
]
super().__init__(
geom_table_column_names=geom_table_column_names,
location_id_column_names=location_id_column_names,
geom_table=geom_table,
geom_column=geom_column,
)
def column_names(self) -> List[str]:
cols = ["location_id"]
geom_table_cols = [
get_name_and_alias(c)[1]
for c in self._get_aliased_geom_table_cols("geom_table")
]
if not ("date_of_first_service" in geom_table_cols
and "date_of_last_service" in geom_table_cols):
cols += ["date_of_first_service", "date_of_last_service"]
cols += geom_table_cols
return cols
def _make_query(self):
loc_table_alias = "loc_table"
if hasattr(self.geom_table, "fully_qualified_table_name") and (
self.geom_table.fully_qualified_table_name == get_db().location_table
):
# No need to join location_table to itself
geom_table_alias = loc_table_alias
join_clause = ""
else:
geom_table_alias = "geom_table"
join_clause = self._join_clause(loc_table_alias, geom_table_alias)
geom_table_cols_string = ", ".join(
self._get_aliased_geom_table_cols(geom_table_alias)
)
loc_table_cols_string = f"{loc_table_alias}.id AS location_id"
geom_table_col_aliases = [
get_name_and_alias(c)[1] for c in self._geom_table_cols
]
if not (
"date_of_first_service" in geom_table_col_aliases
and "date_of_last_service" in geom_table_col_aliases
):
# If we're not selecting dates from the geom table, we need to
# select them from the location table
loc_table_cols_string += f""",
{loc_table_alias}.date_of_first_service,
{loc_table_alias}.date_of_last_service
"""
sql = f"""
SELECT
{loc_table_cols_string},
{geom_table_cols_string}
FROM {get_db().location_table} AS {loc_table_alias}
{join_clause}
"""
return sql
def get_geom_query(self) -> str:
"""
Returns a SQL query which can be used to map locations (identified by
the values in self.location_id_columns) to their geometries (in a column
named "geom").
"""
geom_table_alias = "geom_table"
# List of column names whose aliases are in self.location_id_columns
columns = [
c for c in self._get_aliased_geom_table_cols(geom_table_alias)
if get_name_and_alias(c)[1] in self.location_id_columns
] + [f"{self._geom_col} AS geom"]
# For versioned-cell spatial unit, the geometry table _is_ the location table.
# In this case 'location_id' is one of the location ID columns but
# isn't in self._geom_table_cols, so we specify it separately.
if "location_id" in self.location_id_columns:
columns = [f"{geom_table_alias}.id AS location_id"] + columns
sql = f"SELECT {','.join(columns)} FROM ({self.geom_table.get_query()}) AS {geom_table_alias}"
return sql