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