osm_poi_matchmaker.dao.poi_base.POIBase.query_from_cache() - Code Metrics - KAMI911/osm_poi_matchmaker - Measure and Improve Code Quality continuously with Scrutinizer

POIBase.query_from_cache() A
last analyzed 2023-01-03 16:24 UTC

↳ Parent: osm_poi_matchmaker.dao.poi_base
Complexity

Conditions
Size

Total Lines	11
Code Lines	9
Duplication

Lines	0
Ratio	0 %
Importance

Changes
Metric	Value
cc	3
eloc	9
nop	3
dl	0
loc	11
rs	9.95
c	0
b	0
f	0
# -*- coding: utf-8 -*-

try:
    import logging
    import sys
    import geopandas as gpd
    import pandas as pd
    import sqlalchemy
    import time
    import math
    from math import isnan

    from osm_poi_matchmaker.utils import config, poitypes
    from osm_poi_matchmaker.dao.data_structure import Base
    import psycopg2
except ImportError as err:
    logging.error('Error %s import module: %s', __name__, err)
    logging.exception('Exception occurred')

    sys.exit(128)


class POIBase:
    """Represents the full database.

    :param db_conection: Either a sqlalchemy database url or a filename to be used with sqlite.

    """

    def __init__(self, db_connection, retry_counter=100, retry_sleep=30):
        reco = 0  # Actual retry counter
        self.db_retry_counter = retry_counter
        self.db_retry_sleep = retry_sleep
        self.db_connection = db_connection
        self.db_filename = None
        if '://' not in db_connection:
            self.db_connection = 'sqlite:///{}'.format(self.db_connection)
        if self.db_connection.startswith('sqlite'):
            self.db_filename = self.db_connection
        try:
            self.engine = sqlalchemy.create_engine(self.db_connection, client_encoding='utf8', echo=False)

        except psycopg2.OperationalError as e:

            logging.error('Database error: %s', e)
            if self.retry_counter >= reco:

                logging.error('Cannot connect to database with %s connection string', self.db_connection)

            else:
                logging.error('Cannot connect to the database. It will retry within %s seconds. [%s/%s]',

                              self.db_retry_sleep, reco, self.db_retry_counter)
                time.sleep(self.db_retry_sleep)
                self.engine = sqlalchemy.create_engine(self.db_connection, echo=False)
                self.db_retry_counter += 1
        self.Session = sqlalchemy.orm.sessionmaker(bind=self.engine)

        Base.metadata.create_all(self.engine)

    @property
    def pool(self):

        return self.engine

    @property
    def session(self):

        return self.Session()

    def __del__(self):
        self.session.close()
        self.engine.dispose()

    def query_all_pd(self, table):

        '''
        Load all POI data from SQL
        :param table: Name of table where POI data is stored
        :return: Full table read from SQL database table
        '''
        return pd.read_sql_table(table, self.engine)

    def query_all_gpd(self, table):
        '''
        Load all POI data from SQL that contains gometry
        :param table: Name of table where POI data is stored
        :return: Full table with poi_lat and poi_long fileds read from SQL database table
        '''
        query = sqlalchemy.text('select * from {} where poi_geom is not NULL'.format(table))
        data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='poi_geom')
        data['poi_lat'] = data['poi_geom'].x
        data['poi_lon'] = data['poi_geom'].y
        return data

    def query_all_gpd_in_order(self, table):
        '''
        Load all POI data from SQL that contains gometry and ordered by poi_common_id and postcode
        :param table: Name of table where POI data is stored
        :return: Full table with poi_lat and poi_long fileds read from SQL database table
        '''
        query = sqlalchemy.text('''SELECT * FROM {}
                                     WHERE poi_geom is not NULL
                                     ORDER BY poi_common_id ASC, poi_postcode ASC, poi_addr_street ASC,
                                       poi_addr_housenumber ASC'''.format(table))
        data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='poi_geom')
        data['poi_lat'] = data['poi_geom'].x
        data['poi_lon'] = data['poi_geom'].y
        return data

    def count_all_gpd(self, table):
        '''
        Load all POI data from SQL that contains geometry
        :param table: Name of table where POI data is stored
        :return: Full table with poi_lat and poi_long fields read from SQL database table
        '''
        query = sqlalchemy.text('select count(*) from {} where poi_geom is not NULL'.format(table))
        data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='poi_geom')
        return data

    def query_from_cache(self, node_id, object_type):

        if node_id > 0:
            query = sqlalchemy.text(
                'select * from poi_osm_cache where osm_id = :node_id and osm_object_type = :object_type limit 1')

            data = pd.read_sql(query, self.engine, params={'node_id': int(node_id), 'object_type': object_type.name})

            if not data.values.tolist():

                return None
            else:
                return data.to_dict('records')[0]
        else:
            return None

    def query_ways_nodes(self, way_id):

        if way_id > 0:

            query = sqlalchemy.text('select nodes from planet_osm_ways where id = :way_id limit 1')
            data = pd.read_sql(query, self.engine, params={'way_id': int(way_id)})
            return data.values.tolist()[0][0]
        else:
            return None

    def query_relation_nodes(self, relation_id):

        query = sqlalchemy.text('select members from planet_osm_rels where id = :relation_id limit 1')

        data = pd.read_sql(query, self.engine, params={'relation_id': int(abs(relation_id))})
        return data.values.tolist()[0][0]

    def query_osm_shop_poi_gpd(self, lon: float, lat: float, ptype: str = 'shop', name: str = '', avoid_name: str = '', street_name: str = '',

                               housenumber: str = '', conscriptionnumber: str = '', city: str = '',
                               distance_perfect: int = None, distance_safe: int = None, distance_unsafe: int = None,

                               with_metadata: bool = True):
        '''
        Search for POI in OpenStreetMap database based on POI type and geom within preconfigured distance

        :param lon:
        :param lat:
        :param ptype:
        :param name:
        :param avoid_name:
        :param street_name:
        :param housenumber:
        :param conscriptionnumber:
        :param city:
        :param distance_perfect:
        :param distance_safe:
        :param distance_unsafe:
        :parm with_metadata:
        :return:
        '''
        buffer = 10
        query_arr = []
        query_params = {}
        query_type, distance = poitypes.getPOITypes(ptype)

        # If we have PO common defined unsafe search radius distance, then use it (or use defaults specified above)

        if distance_unsafe is None or distance_unsafe == '' or math.isnan(distance_unsafe):
            distance_unsafe = config.get_geo_default_poi_unsafe_distance()
        query_params.update({'distance_unsafe': distance_unsafe})
        if distance_safe is None or distance_safe == '' or math.isnan(distance_safe):
            distance_safe = config.get_geo_default_poi_distance()
        query_params.update({'distance_safe': distance_safe})
        if lon is not None and lon != '':
            query_params.update({'lon': lon})
        if lat is not None and lat != '':
            query_params.update({'lat': lat})
        query_params.update({'buffer': buffer})
        # Do not match with other specified names and brands
        if name is not None and name != '':
            query_name = ' AND (LOWER(TEXT(name)) ~* LOWER(TEXT(:name)) OR LOWER(TEXT(brand)) ~* LOWER(TEXT(:name)))'

            query_params.update({'name': '.*{}.*'.format(name)})
            # If we have PO common defined safe search radius distance, then use it (or use defaults specified above)

            if distance_perfect is None or distance_perfect != '' or math.isnan(distance_perfect):
                distance_perfect = config.get_geo_default_poi_perfect_distance()
            query_params.update({'distance_perfect': distance_perfect})
        else:
            query_name = '' 

        # Do not match with other specified names and brands
        if avoid_name is not None and avoid_name != '':
            query_avoid_name = ' AND (LOWER(TEXT(name)) !~* LOWER(TEXT(:avoid_name)) AND LOWER(TEXT(brand)) !~* LOWER(TEXT(:avoid_name)))'

            query_params.update({'avoid_name': '.*{}.*'.format(avoid_name)})
        else:
            query_avoid_name = '' 

        if with_metadata is True:
            metadata_fields = ' osm_user, osm_uid, osm_version, osm_changeset, osm_timestamp, '
        else:
            metadata_fields = ''
        if street_name is not None and street_name != '':
            street_query = ' AND LOWER(TEXT("addr:street")) = LOWER(TEXT(:street_name))'
            query_params.update({'street_name': street_name})
        else:
            street_query = ''
        if housenumber is not None and housenumber != '':
            housenumber_query = ' AND LOWER(TEXT("addr:housenumber")) = LOWER(TEXT(:housenumber))'
            query_params.update({'housenumber': housenumber})
        else:
            housenumber_query = ''
        if conscriptionnumber is not None and conscriptionnumber != '':
            conscriptionnumber_query = ' AND LOWER(TEXT("addr:conscriptionnumber")) = LOWER(TEXT(:conscriptionnumber))'

            query_params.update({'conscriptionnaumber': conscriptionnaumber})

        else:
            conscriptionnumber_query = ''
        if city is not None and city != '':
            city_query = ' AND LOWER(TEXT("addr:city")) = LOWER(TEXT(:city))'
            query_params.update({'city': city})
        else:
            city_query = ''
        logging.debug('%s %s: %s, %s (NOT %s), %s %s %s (%s) [%s, %s, %s]', lon, lat, ptype, name, avoid_name, city,

                      street_name, housenumber, conscriptionnumber, distance_perfect, distance_safe, distance_unsafe)

        # Looking for way (building)
        if query_name is not None and query_name != '' and city_query is not None and city_query != '' and \

                conscriptionnumber_query is not None and conscriptionnumber_query != '':
            query_text = '''
            --- WITH NAME, WITH CONSCRIPTINNUMBER, WITH CITY
            --- The way selector with conscriptionnumber and city
            SELECT name, osm_id, {metadata_fields} 965 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon
            WHERE ({query_type}) AND osm_id > 0 {query_name} {conscriptionnumber_query} {city_query}
            UNION ALL
            --- The node selector with conscriptionnumber and city
            SELECT name, osm_id, {metadata_fields} 965 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(planet_osm_point.way) as lon,
                   ST_Y(planet_osm_point.way) as lat
            FROM planet_osm_point
            WHERE ({query_type}) AND osm_id > 0 {query_name} {conscriptionnumber_query} {city_query}
            UNION ALL
            --- The relation selector with conscriptionnumber and city
            SELECT name, osm_id, {metadata_fields} 965 AS priority, 'relation' AS node, shop, amenity,
                   "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                   "addr:conscriptionnumber", '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon
            WHERE ({query_type}) AND osm_id < 0 {query_name} {conscriptionnumber_query} {city_query}
            '''
            query = sqlalchemy.text(query_text.format(query_type=query_type, query_name=query_name,
                                                      metadata_fields=metadata_fields,
                                                      conscriptionnumber_query=conscriptionnumber_query,

                                                      city_query=city_query))
            logging.debug(str(query))
            data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params=query_params)

            '''perf_query = sqlalchemy.text('EXPLAIN ' + query_text.format(query_type=query_type, query_name=query_name,

                                                      metadata_fields=metadata_fields,
                                                      conscriptionnumber_query=conscriptionnumber_query,

                                                      city_query=city_query))
            perf = gpd.GeoDataFrame.from_postgis(perf_query, self.engine, geom_col='way', params=query_params)

            logging.debug(perf)
            '''

            if not data.empty:
                logging.debug(data.to_string())
                return data.iloc[[0]]
        if query_name is not None and query_name != '' and city_query is not None and city_query != '' and \

                street_query is not None and street_query != '' and \
                housenumber_query is not None and housenumber_query != '':
            query_text = '''
            --- WITH NAME, WITH CITY, WITH STREETNAME, WITH HOUSENUMBER
            --- The way selector with city, street name and housenumber
            SELECT name, osm_id, {metadata_fields} 940 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon
            WHERE ({query_type}) AND osm_id > 0 {query_name} {city_query} {street_query} {housenumber_query}
            UNION ALL
            --- The node selector with street name and housenumber
            SELECT name, osm_id, {metadata_fields} 940 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(planet_osm_point.way) as lon,
                   ST_Y(planet_osm_point.way) as lat
            FROM planet_osm_point
            WHERE ({query_type}) AND osm_id > 0 {query_name} {city_query} {street_query} {housenumber_query}
            UNION ALL
            --- The relation selector with street name and housenumber
            SELECT name, osm_id, {metadata_fields} 940 AS priority, 'relation' AS node, shop, amenity,
                   "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                   "addr:conscriptionnumber", '0' as distance, way, ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon
            WHERE ({query_type}) AND osm_id < 0 {query_name} {city_query} {street_query} {housenumber_query}
            '''
            query = sqlalchemy.text(query_text.format(query_type=query_type, query_name=query_name,
                                                      metadata_fields=metadata_fields,
                                                      street_query=street_query,
                                                      city_query=city_query,
                                                      housenumber_query=housenumber_query))
            logging.debug(str(query))
            data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params=query_params)

            '''
            perf_query = sqlalchemy.text('EXPLAIN ' + query_text.format(query_type=query_type, query_name=query_name,

                                                      metadata_fields=metadata_fields,
                                                      street_query=street_query,
                                                      city_query=city_query,
                                                      housenumber_query=housenumber_query))
            perf = gpd.GeoDataFrame.from_postgis(perf_query, self.engine, geom_col='way', params=query_params)

            logging.debug(perf)
            '''

            if not data.empty:
                logging.debug(data.to_string())
                return data.iloc[[0]]
        if street_query is not None and street_query != '':
            # Using street name for query
            if housenumber_query is not None and housenumber_query != '':
                query_arr.append('''
                --- WITH NAME, WITH STREETNAME, WITH HOUSENUMBER
                --- The way selector with street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 950 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                       "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                       ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                       ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
                FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE ({query_type}) AND osm_id > 0 {query_name} {street_query} {housenumber_query}
                    AND ST_DistanceSphere(way, point.geom) < :distance_perfect
                UNION ALL
                --- The node selector with street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 950 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                       "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                       ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(planet_osm_point.way) as lon,
                       ST_Y(planet_osm_point.way) as lat
                FROM planet_osm_point, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE ({query_type}) AND osm_id > 0 {query_name} {street_query} {housenumber_query}
                    AND ST_DistanceSphere(way, point.geom) < :distance_perfect
                UNION ALL
                --- The relation selector with street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 950 AS priority, 'relation' AS node, shop, amenity,
                       "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                       "addr:conscriptionnumber", ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                       ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
                FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE ({query_type}) AND osm_id < 0 {query_name} {street_query} {housenumber_query}
                    AND ST_DistanceSphere(way, point.geom) < :distance_perfect
                ''')
            query_arr.append('''
            --- WITH NAME, WITH STREETNAME, NO HOUSENUMBER
            --- The way selector with street name and without housenumber
            SELECT name, osm_id, {metadata_fields} 970 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id > 0 {query_name} {street_query})
                AND ST_DistanceSphere(way, point.geom) < :distance_safe
            UNION ALL
            --- The node selector with street name and without housenumber
            SELECT name, osm_id, {metadata_fields} 970 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(planet_osm_point.way) as lon,
                   ST_Y(planet_osm_point.way) as lat
            FROM planet_osm_point, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id > 0 {query_name} {street_query})
                AND ST_DistanceSphere(way, point.geom) < :distance_safe
            UNION ALL
            --- The relation selector with street name and without housenumber
            SELECT name, osm_id, {metadata_fields} 970 AS priority, 'relation' AS node, shop, amenity,
                   "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                   "addr:conscriptionnumber", ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id < 0 {query_name} {street_query})
                AND ST_DistanceSphere(way, point.geom) < :distance_safe
            ''')
        else:
            if housenumber_query is not None and housenumber_query != '':
                query_arr.append('''
                --- WITH NAME, NO STREETNAME, WITH HOUSENUMBER
                --- The way selector without street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 970 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                       "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                       ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                       ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
                FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE (({query_type}) AND osm_id > 0 {query_name} {housenumber_query})
                    AND ST_DistanceSphere(way, point.geom) < :distance_safe
                UNION ALL
                --- The node selector without street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 970 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                       "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                       ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(planet_osm_point.way) as lon,
                       ST_Y(planet_osm_point.way) as lat
                FROM planet_osm_point, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE (({query_type}) AND osm_id > 0 {query_name} {housenumber_query})
                    AND ST_DistanceSphere(way, point.geom) < :distance_safe
                UNION ALL
                --- The relation selector without street name and with housenumber
                SELECT name, osm_id, {metadata_fields} 970 AS priority, 'relation' AS node, shop, amenity,
                       "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                       "addr:conscriptionnumber",
                       ST_DistanceSphere(way, point.geom) as distance, way,
                       ST_AsEWKT(way) as way_ewkt,
                       ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                       ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
                FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                WHERE ({query_type}) AND osm_id < 0 {query_name} {housenumber_query}
                    AND ST_DistanceSphere(way, point.geom) < :distance_safe
                ''')
        # Trying without street name and house number in case when the street name and/or the house not matching at all

        query_arr.append('''
        --- WITH NAME, NO STREETNAME, NO HOUSENUMBER
        --- The way selector without street name and without housenumber
        SELECT name, osm_id, {metadata_fields} 980 AS priority, 'way' AS node, shop, amenity, "addr:housename",
               "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
               ST_DistanceSphere(way, point.geom) as distance, way,
               ST_AsEWKT(way) as way_ewkt,
               ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
               ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
        FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
        WHERE (({query_type}) AND osm_id > 0 {query_name} )
            AND ST_DistanceSphere(way, point.geom) < :distance_safe
        UNION ALL
        --- The node selector without street name and without housenumber
        SELECT name, osm_id, {metadata_fields} 980 AS priority, 'node' AS node, shop, amenity, "addr:housename",
               "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
               ST_DistanceSphere(way, point.geom) as distance, way,
               ST_AsEWKT(way) as way_ewkt,
               ST_X(planet_osm_point.way) as lon,
               ST_Y(planet_osm_point.way) as lat
        FROM planet_osm_point, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
        WHERE (({query_type}) AND osm_id > 0 {query_name} )
            AND ST_DistanceSphere(way, point.geom) < :distance_safe
        UNION ALL
        --- The relation selector without street name and without housenumber
        SELECT name, osm_id, {metadata_fields} 980 AS priority, 'relation' AS node, shop, amenity,
               "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
               "addr:conscriptionnumber",
               ST_DistanceSphere(way, point.geom) as distance, way,
               ST_AsEWKT(way) as way_ewkt,
               ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
               ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
        FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
        WHERE (({query_type}) AND osm_id < 0 {query_name} )
            AND ST_DistanceSphere(way, point.geom) < :distance_safe
        ''')
        query_arr.append('''
            --- NO NAME, NO STREETNAME, NO HOUSENUMBER
            --- The way selector without name and street name
            SELECT name, osm_id, {metadata_fields} 990 AS priority, 'way' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id > 0 {query_avoid_name} )
                AND ST_DistanceSphere(way, point.geom) < :distance_unsafe
            UNION ALL
            --- The node selector without name and street name
            SELECT name, osm_id, {metadata_fields} 990 AS priority, 'node' AS node, shop, amenity, "addr:housename",
                   "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                   ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(planet_osm_point.way) as lon,
                   ST_Y(planet_osm_point.way) as lat
            FROM planet_osm_point, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id > 0 {query_avoid_name} )
                AND ST_DistanceSphere(way, point.geom) < :distance_unsafe
            UNION ALL
            --- The relation selector without name street name
            SELECT name, osm_id, {metadata_fields} 990 AS priority, 'relation' AS node, shop, amenity,
                   "addr:housename", "addr:housenumber", "addr:postcode", "addr:city", "addr:street",
                   "addr:conscriptionnumber",
                   ST_DistanceSphere(way, point.geom) as distance, way,
                   ST_AsEWKT(way) as way_ewkt,
                   ST_X(ST_PointOnSurface(planet_osm_polygon.way)) as lon,
                   ST_Y(ST_PointOnSurface(planet_osm_polygon.way)) as lat
            FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE (({query_type}) AND osm_id < 0 {query_avoid_name} )
                AND ST_DistanceSphere(way, point.geom) < :distance_unsafe
        ''')
        query_text = 'UNION ALL'.join(query_arr) + 'ORDER BY priority ASC, distance ASC;'
        query = sqlalchemy.text(query_text.format(query_type=query_type, query_name=query_name,
                                                  query_avoid_name=query_avoid_name,
                                                  metadata_fields=metadata_fields,
                                                  street_query=street_query,
                                                  city_query=city_query,
                                                  housenumber_query=housenumber_query))
        logging.debug(str(query))
        data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params=query_params)

        '''
        perf_query = sqlalchemy.text('EXPLAIN ' + query_text.format(query_type=query_type, query_name=query_name,

                                                  metadata_fields=metadata_fields,
                                                  street_query=street_query,
                                                  city_query=city_query,
                                                  housenumber_query=housenumber_query))
        perf = gpd.GeoDataFrame.from_postgis(perf_query, self.engine, geom_col='way', params=query_params)

        logging.debug(perf)
        '''

        if not data.empty:

            logging.debug(data.to_string())
            return data.iloc[[0]]
        else:
            return None

    def query_osm_building_poi_gpd(self, lon, lat, city, postcode, street_name='', housenumber='',

                                   in_building_percentage=0.50, distance=60):
        '''
        Looking for a building (actually a way) around the POI node with same address with within a preconfigured distance.

        Actually this method helps to find a building for a single node.
        :param lon: Longitude of POI node type coordinate.
        :param lat: Latitude of POI node type coordinate.
        :param: city: Name of city where the POI node is.
        :param: postcode: Postcode of area where the POI node is.
        :param: street_name: Name of street where the POI node is.
        :param: housenumber: House number of street where the POI node is.
        :param: in_building_percentage: In building line argument is a float8 between 0 and 1 representing fraction of

          total linestring length the point has to be located.
          Documentation: https://postgis.net/docs/ST_LineInterpolatePoint.html
        :param: distance: Look buildings around the POI node within this radius (specified in meter).

        :return: A new node within the building with same address.
        '''
        buffer = 10
        # When we got all address parts, then we should try to fetch only one coordinate pair of building geometry

        if street_name is not None and street_name != '' and housenumber is not None and housenumber != '':

            street_query = ' AND LOWER(TEXT("addr:street")) = LOWER(TEXT(:street_name))'
            housenumber_query = ' AND LOWER(TEXT("addr:housenumber")) = LOWER(TEXT(:housenumber))'
        else:
            return None
        query = sqlalchemy.text('''
            --- The building selector based on POI node distance and
            --- city, postcode, street name and housenumber of building
            --- Make a line that connects the building and POI coords, using one point from union
            SELECT name, building, osm_id, 'way' AS node, "addr:housename",
                "addr:housenumber", "addr:postcode", "addr:city", "addr:street", "addr:conscriptionnumber",
                 ST_Y(ST_PointOnSurface(way)) as lat_in_b,
                 ST_X(ST_PointOnSurface(way)) as lon_in_b,
                 ST_Y(ST_LineInterpolatePoint(ST_GeometryN(ST_Intersection(way,
                 ST_MakeLine(ST_PointOnSurface(way), point.geom)), 1), :ibp)) as lat,
                 ST_X(ST_LineInterpolatePoint(ST_GeometryN(ST_Intersection(way,
                 ST_MakeLine(ST_PointOnSurface(way), point.geom)), 1), :ibp)) as lon,
                ST_DistanceSphere(way, point.geom) as distance, way,
                ST_PointOnSurface(way) in_building, ST_AsEWKT(way) as way_ewkt,
                ST_AsEWKT(ST_PointOnSurface(way)) in_building_ewkt
            FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
            WHERE building <> '' AND osm_id > 0 AND ST_DistanceSphere(way, point.geom) < :distance
                {street_query} {housenumber_query}
            ORDER BY distance ASC LIMIT 1'''.format(street_query=street_query, housenumber_query=housenumber_query))

        data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params={'lon': lon, 'lat': lat,

                                                                                         'distance': distance,

                                                                                         'buffer': buffer,

                                                                                         'street_name': street_name,

                                                                                         'housenumber': housenumber,

                                                                                         'ibp': in_building_percentage})

        if data.empty:

            return None
        else:
            return data

    def query_poi_in_water(self, lon, lat):

        distance = 1
        try:
            query = sqlalchemy.text('''
            SELECT * FROM
              (SELECT osm_id, way, ST_DistanceSphere(way, point.geom) as distance
              FROM planet_osm_polygon, (SELECT ST_SetSRID(ST_MakePoint(:lon, :lat), 4326) as geom) point
              WHERE (water IS NOT NULL OR waterway IS NOT NULL)
              ORDER BY distance ASC LIMIT 1) AS geo
            WHERE geo.distance < :distance
              ''')
            data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params={'lon': lon, 'lat': lat,

                                                                                             'distance': distance})

            return data
        except Exception as err:

            logging.error(err)
            logging.exception('Exception occurred')

    def query_name_road_around(self, lon, lat, name='', with_metadata=True, mode='both'):

        '''
        Search for road with name specified around the lon, lat point location in OpenStreetMap database based on

        within preconfigured distance
        :param lon: Longitude parameter. Looking for roads around this geom.
        :param lat: Latitude parameter. Looking for roads around this geom.
        :param name: Name of the road. Search OpenStreetMap "highway" tagged ways with this name tag.

        :param with_metadata: Query OpenStreetMap metadata information
        :param mode: Looking for name, metaphone or both
        :return: GeoDataFrame of distance ordered result.
        '''
        try:
            distance = config.get_geo_default_poi_road_distance()
            if with_metadata is True:
                metadata_fields = ' osm_user, osm_uid, osm_version, osm_changeset, osm_timestamp, '
            else:
                metadata_fields = ''
            # Looking for way (road)
            if mode == 'both':
                name_query = '("name" = :name OR dmetaphone(name) = dmetaphone(:name))'
            elif mode == 'name':
                name_query = '("name" = :name)'
            elif mode == 'metaphone':
                name_query = 'dmetaphone(name) = dmetaphone(:name)'
            else:
                name_query = '("name" = :name OR dmetaphone(name) = dmetaphone(:name))'
            query = sqlalchemy.text('''
                SELECT * FROM
                  (SELECT name, osm_id, highway, {metadata_fields}
                    ST_DistanceSphere(way, point.geom) as distance, way, ST_AsEWKT(way) as way_ewkt
                  FROM planet_osm_line, (SELECT ST_SetSRID(ST_MakePoint(:lon,:lat), 4326) as geom) point
                  WHERE "highway" is not NULL
                    AND {name_query}
                  ORDER BY distance ASC LIMIT 1) AS geo
                WHERE geo.distance < :distance
                '''.format(metadata_fields=metadata_fields, name_query=name_query))
            data = gpd.GeoDataFrame.from_postgis(query, self.engine, geom_col='way', params={'lon': lon, 'lat': lat,

                                                                                             'distance': distance,

                                                                                             'name': '{}'.format(name)})

            data.sort_values(by=['distance'])
            return data
        except Exception as err:

            logging.error(err)
            logging.exception('Exception occurred')

KAMI911 / osm_poi_matchmaker

POIBase.query_from_cache() A last analyzed 2023-01-03 16:24 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

POIBase.query_from_cache() A
last analyzed 2023-01-03 16:24 UTC
