database.py 5.84 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
#!/usr/bin/env python3

# Copyright (C) 2021:
#   Helmholtz-Zentrum Potsdam Deutsches GeoForschungsZentrum GFZ
#
# This program is free software: you can redistribute it and/or modify it
# under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or (at
# your option) any later version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero
# General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program. If not, see http://www.gnu.org/licenses/.


import logging
import psycopg2
import pyproj
import geopandas
from shapely.ops import transform


# Initialize log
logger = logging.getLogger(__name__)


class Database:
    """A Database object includes the necessary credentials to connect to a given postGIS
    database and makes use of different methods to request information on buildings,
    roads and uploads tile-based entries.

    Input dataset:
    The source of information are sets of raster data and a raster index file that
    includes filepaths and raster footprints. These sources can either be:

    Args:
            host (str): Postgres Database host direction.
            dbname (str): Postgres database name.
            port (str): Port where the Postgres database can be found.
            username (str): User to connect to the Postgres database.
            password (str or getpass.getpass): Password for username argument

    Attributes:
            self.host (str): Postgres Database host direction.
            self.dbname (str): Postgres database name.
            self.port (str): Port where the Postgres database can be found.
            self.username (str): User to connect to the Postgres database.
            self.password (str or getpass.getpass): Password for username argument

    """

    def __init__(self, host, dbname, port, username, password, **kwargs):
        self.host = host
        self.dbname = dbname
        self.port = port
        self.username = username
        self.password = password

        # Initialize other attributes
        self.connection = None
        self.cursor = None

    def create_connection_and_cursor(self):
        """Create a database connection and a cursor with psycopg2 with the
        given credentials and include the connection and cursor as new attributes.

        Attributes:
            self.connection (psycopg2.extensions.connection):
                                Connection object to the database.

            self.cursor (psycopg2.extensions.cursor):
                                Cursor object to run commands on the database.

        Returns:
            None
        """

        connection_string = "host={} dbname={} user={} password={} port={}".format(
            self.host, self.dbname, self.username, self.password, self.port
        )

        connection = psycopg2.connect(connection_string)
        setattr(self, "connection", connection)
        setattr(self, "cursor", connection.cursor())

    def get_crs_from_geometry_field(
        self, tablename, schema="public", geometry_field="geometry"
    ):
        """Returns the epsg/srid number from the specified table in the database

        Args:
            schema (str): Database schema where the database is located. Default = "public"
            tablename (str): Table name within database for searching (e.g. "obm_buildings")
            geometry_field (str): Name of the column with geometries. Default = "geometry"

        Returns:
            crs_number (str): Number of the epsg/srid code for the specified table.
        """

        crs_search_query = "SELECT Find_SRID('{}','{}','{}')".format(
            schema, tablename, geometry_field
        )
        self.cursor.execute(crs_search_query)
        crs_number = self.cursor.fetchone()[0]
        return crs_number

    def get_features_in_tile(
        self, tile, tablename, crs_number, schema="public", geometry_field="geometry"
    ):
        """Returns a GeoPandas dataframe with all features that intersect the given tile.
        The features are extracted from a specified table with Database.set_table().

        Args:
            tile (tile.Tile): Tile object with quadkey, crs and geometry attributes.
            tablename (str): Table name within database for searching (e.g. "obm_buildings")
            crs_number (str): Number of the epsg/srid code for the specified table.
            schema (str): Database schema where the database is located. Default = "public"
            geometry_field (str): Name of the column with geometries. Default = "geometry"


        Returns:
            output_features (geopandas.geodataframe.GeoDataFrame): GeoPandas dataframe
                    with all features and attributes that intersect the given quadtile.
        """
        geometry = tile.geometry
        print(str(geometry))
        print(tile.crs)
        print("epsg:{}".format(crs_number))
        if tile.crs != "epsg:{}".format(crs_number):
            project = pyproj.Transformer.from_proj(
                pyproj.Proj(tile.crs),
                pyproj.Proj("epsg:{}".format(crs_number)),
                always_xy=True,
            )
            geometry = transform(project.transform, geometry)
            print(str(geometry))

        sql_query = (
            "SELECT * "
            "FROM {}.{} "
            "WHERE ST_Intersects({},'SRID={};{}')".format(
                schema, tablename, geometry_field, crs_number, geometry
            )
        )

        output_features = geopandas.GeoDataFrame.from_postgis(
            sql_query,
            self.connection,
            geom_col=geometry_field,
            crs="epsg:{}".format(crs_number),
        )

        return output_features