| 1 |
# Copyright (C) 2003 by Intevation GmbH |
| 2 |
# Authors: |
| 3 |
# Martin Mueller <[email protected]> |
| 4 |
# Bernhard Herzog <[email protected]> |
| 5 |
# |
| 6 |
# This program is free software under the GPL (>=v2) |
| 7 |
# Read the file COPYING coming with the software for details. |
| 8 |
|
| 9 |
"""Basic interface to a PostGIS database""" |
| 10 |
|
| 11 |
from __future__ import generators |
| 12 |
|
| 13 |
try: |
| 14 |
import psycopg |
| 15 |
except ImportError: |
| 16 |
psycopg = None |
| 17 |
|
| 18 |
import table |
| 19 |
import wellknowntext |
| 20 |
|
| 21 |
from data import SHAPETYPE_POLYGON, SHAPETYPE_ARC, SHAPETYPE_POINT, RAW_WKT |
| 22 |
|
| 23 |
def has_postgis_support(): |
| 24 |
"""Return whether this Thuban instance supports PostGIS connections |
| 25 |
|
| 26 |
Having PostGIS support means that the psycopg module can be |
| 27 |
imported. |
| 28 |
""" |
| 29 |
return psycopg is not None |
| 30 |
|
| 31 |
def psycopg_version(): |
| 32 |
return psycopg.__version__ |
| 33 |
|
| 34 |
if psycopg is not None: |
| 35 |
type_map = [(psycopg.STRING, table.FIELDTYPE_STRING), |
| 36 |
(psycopg.INTEGER, table.FIELDTYPE_INT), |
| 37 |
(psycopg.FLOAT, table.FIELDTYPE_DOUBLE)] |
| 38 |
|
| 39 |
|
| 40 |
class ConnectionError(Exception): |
| 41 |
|
| 42 |
"""Class for exceptions occurring when establishing a Databse connection""" |
| 43 |
|
| 44 |
|
| 45 |
class PostGISConnection: |
| 46 |
|
| 47 |
"""Represent a PostGIS database |
| 48 |
|
| 49 |
A PostGISConnection instance has the following public attributes: |
| 50 |
|
| 51 |
dbname -- The name of the database |
| 52 |
host, port -- Host and port to connect to |
| 53 |
user -- The user name to connect as. |
| 54 |
|
| 55 |
All of these attributes are strings and may be empty strings to |
| 56 |
indicate default values. |
| 57 |
""" |
| 58 |
|
| 59 |
def __init__(self, dbname, host="", user="", password="", dbtype="", |
| 60 |
port=""): |
| 61 |
self.dbname = dbname |
| 62 |
self.host = host |
| 63 |
self.port = port |
| 64 |
self.user = user |
| 65 |
self.password = password |
| 66 |
self.dbtype = dbtype |
| 67 |
self.connect() |
| 68 |
|
| 69 |
def connect(self): |
| 70 |
"""Internal: Establish the database connection""" |
| 71 |
params = [] |
| 72 |
for name in ("host", "port", "dbname", "user", "password"): |
| 73 |
val = getattr(self, name) |
| 74 |
if val: |
| 75 |
params.append("%s=%s" % (name, val)) |
| 76 |
try: |
| 77 |
self.connection = psycopg.connect(" ".join(params)) |
| 78 |
except psycopg.OperationalError, val: |
| 79 |
raise ConnectionError(str(val)) |
| 80 |
|
| 81 |
# determine the OID for the geometry type. This is PostGIS |
| 82 |
# specific. |
| 83 |
cursor = self.connection.cursor() |
| 84 |
cursor.execute("SELECT OID, typname FROM pg_type WHERE" |
| 85 |
+" typname = 'geometry'") |
| 86 |
row = cursor.fetchone() |
| 87 |
self.connection.commit() |
| 88 |
if row is not None: |
| 89 |
self.geometry_type = row[0] |
| 90 |
else: |
| 91 |
raise ValueError("Can't determine postgres type of geometries") |
| 92 |
|
| 93 |
def BriefDescription(self): |
| 94 |
"""Return a brief, one-line description of the connection |
| 95 |
|
| 96 |
The return value is suitable for a list box of all database |
| 97 |
connections. |
| 98 |
""" |
| 99 |
return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s" |
| 100 |
% self.__dict__) |
| 101 |
|
| 102 |
def Close(self): |
| 103 |
"""Close the database connection""" |
| 104 |
self.connection.close() |
| 105 |
|
| 106 |
def GeometryTables(self): |
| 107 |
"""Return a list with the names of all tables with a geometry column""" |
| 108 |
cursor = self.connection.cursor() |
| 109 |
cursor.execute("SELECT f_table_name FROM geometry_columns;") |
| 110 |
result = [row[0] for row in cursor.fetchall()] |
| 111 |
self.connection.commit() |
| 112 |
return result |
| 113 |
|
| 114 |
def cursor(self): |
| 115 |
"""Return a DB API 2.0 cursor for the database""" |
| 116 |
return self.connection.cursor() |
| 117 |
|
| 118 |
|
| 119 |
|
| 120 |
class PostGISColumn: |
| 121 |
|
| 122 |
"""Column description for a PostGISTable""" |
| 123 |
|
| 124 |
def __init__(self, name, type, index): |
| 125 |
self.name = name |
| 126 |
self.type = type |
| 127 |
self.index = index |
| 128 |
|
| 129 |
|
| 130 |
class PostGISTable: |
| 131 |
|
| 132 |
"""A Table in a PostGIS database""" |
| 133 |
|
| 134 |
def __init__(self, db, tablename): |
| 135 |
"""Initialize the PostGISTable. |
| 136 |
|
| 137 |
The db parameter should be an instance of PostGISConnection and |
| 138 |
tablename the name of a table in the database represented by db. |
| 139 |
""" |
| 140 |
self.db = db |
| 141 |
self.tablename = tablename |
| 142 |
self.column_map = {} |
| 143 |
self._fetch_table_information() |
| 144 |
|
| 145 |
def _fetch_table_information(self): |
| 146 |
"""Internal: Update information about the table""" |
| 147 |
self.columns = [] |
| 148 |
cursor = self.db.cursor() |
| 149 |
cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename) |
| 150 |
description = cursor.description |
| 151 |
|
| 152 |
for i in range(len(description)): |
| 153 |
for pgtyp, tabletyp in type_map: |
| 154 |
if pgtyp == description[i][1]: |
| 155 |
col = PostGISColumn(description[i][0], tabletyp, i) |
| 156 |
break |
| 157 |
else: |
| 158 |
if description[i][1] == self.db.geometry_type: |
| 159 |
self.geometry_column = description[i][0] |
| 160 |
# No matching table type. Ignore the column. |
| 161 |
# FIXME: We should at least print a warning about |
| 162 |
# ignored columns |
| 163 |
continue |
| 164 |
self.columns.append(col) |
| 165 |
|
| 166 |
for col in self.columns: |
| 167 |
self.column_map[col.name] = col |
| 168 |
self.column_map[col.index] = col |
| 169 |
|
| 170 |
# Build query string for ReadRowAsDict |
| 171 |
self.query_stmt = ("SELECT %s from %s" |
| 172 |
% (", ".join([col.name for col in self.columns]), |
| 173 |
self.tablename)) |
| 174 |
|
| 175 |
def DBConnection(self): |
| 176 |
"""Return the dbconnection used by the table""" |
| 177 |
return self.db |
| 178 |
|
| 179 |
def TableName(self): |
| 180 |
"""Return the name of the table in the database""" |
| 181 |
return self.tablename |
| 182 |
|
| 183 |
def Title(self): |
| 184 |
"""Return the title of the table. |
| 185 |
|
| 186 |
The title is currently fixed and equal to the tablename |
| 187 |
""" |
| 188 |
return self.tablename |
| 189 |
|
| 190 |
def Dependencies(self): |
| 191 |
"""Return an empty tuple because a PostGISTable depends on nothing else |
| 192 |
""" |
| 193 |
return () |
| 194 |
|
| 195 |
def NumColumns(self): |
| 196 |
return len(self.columns) |
| 197 |
|
| 198 |
def Columns(self): |
| 199 |
return self.columns |
| 200 |
|
| 201 |
def Column(self, col): |
| 202 |
return self.column_map[col] |
| 203 |
|
| 204 |
def HasColumn(self, col): |
| 205 |
return self.column_map.has_key(col) |
| 206 |
|
| 207 |
def NumRows(self): |
| 208 |
cursor = self.db.cursor() |
| 209 |
cursor.execute("SELECT count(*) FROM %s" % self.tablename) |
| 210 |
return cursor.fetchone()[0] |
| 211 |
|
| 212 |
def ReadRowAsDict(self, row): |
| 213 |
cursor = self.db.cursor() |
| 214 |
cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row) |
| 215 |
result = {} |
| 216 |
for col, value in zip(self.columns, cursor.fetchone()): |
| 217 |
result[col.name] = value |
| 218 |
return result |
| 219 |
|
| 220 |
def ReadValue(self, row, col): |
| 221 |
cursor = self.db.cursor() |
| 222 |
cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" % |
| 223 |
(self.column_map[col].name, self.tablename, row)) |
| 224 |
return cursor.fetchone()[0] |
| 225 |
|
| 226 |
def ValueRange(self, col): |
| 227 |
cursor = self.db.cursor() |
| 228 |
name = self.column_map[col].name |
| 229 |
cursor.execute("SELECT min(%s), max(%s) FROM %s" % |
| 230 |
(name, name, self.tablename)) |
| 231 |
return tuple(cursor.fetchone()) |
| 232 |
|
| 233 |
def UniqueValues(self, col): |
| 234 |
cursor = self.db.cursor() |
| 235 |
name = self.column_map[col].name |
| 236 |
cursor.execute("SELECT %s FROM %s GROUP BY %s" % |
| 237 |
(name, self.tablename, name)) |
| 238 |
return [row[0] for row in cursor.fetchall()] |
| 239 |
|
| 240 |
def SimpleQuery(self, left, comparison, right): |
| 241 |
if comparison not in ("==", "!=", "<", "<=", ">=", ">"): |
| 242 |
raise ValueError("Comparison operator %r not allowed" % comparison) |
| 243 |
|
| 244 |
if comparison == "==": |
| 245 |
comparison = "=" |
| 246 |
|
| 247 |
if isinstance(right, PostGISColumn): |
| 248 |
right_template = right.name |
| 249 |
params = () |
| 250 |
else: |
| 251 |
right_template = "%s" |
| 252 |
params = (right,) |
| 253 |
|
| 254 |
query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \ |
| 255 |
% (self.tablename, left.name, comparison, right_template) |
| 256 |
|
| 257 |
cursor = self.db.cursor() |
| 258 |
cursor.execute(query, params) |
| 259 |
result = [] |
| 260 |
while 1: |
| 261 |
row = cursor.fetchone() |
| 262 |
if row is None: |
| 263 |
break |
| 264 |
result.append(row[0]) |
| 265 |
return result |
| 266 |
|
| 267 |
|
| 268 |
class PostGISShape: |
| 269 |
|
| 270 |
def __init__(self, shapeid, data): |
| 271 |
self.shapeid = shapeid |
| 272 |
self.data = data |
| 273 |
|
| 274 |
def compute_bbox(self): |
| 275 |
""" |
| 276 |
Return the bounding box of the shape as a tuple (minx,miny,maxx,maxy) |
| 277 |
""" |
| 278 |
xs = [] |
| 279 |
ys = [] |
| 280 |
for part in self.Points(): |
| 281 |
for x, y in part: |
| 282 |
xs.append(x) |
| 283 |
ys.append(y) |
| 284 |
return (min(xs), min(ys), max(xs), max(ys)) |
| 285 |
|
| 286 |
def ShapeID(self): |
| 287 |
return self.shapeid |
| 288 |
|
| 289 |
def Points(self): |
| 290 |
return wellknowntext.parse_wkt_thuban(self.data) |
| 291 |
|
| 292 |
def RawData(self): |
| 293 |
return self.data |
| 294 |
|
| 295 |
|
| 296 |
shapetype_map = {"POLYGON": SHAPETYPE_POLYGON, |
| 297 |
"MULTIPOLYGON": SHAPETYPE_POLYGON, |
| 298 |
"MULTILINESTRING": SHAPETYPE_ARC, |
| 299 |
"POINT": SHAPETYPE_POINT} |
| 300 |
|
| 301 |
|
| 302 |
class PostGISShapeStore(PostGISTable): |
| 303 |
|
| 304 |
"""Shapestore interface to a table in a PostGIS database""" |
| 305 |
|
| 306 |
def Table(self): |
| 307 |
"""Return self since a PostGISShapeStore is its own table.""" |
| 308 |
return self |
| 309 |
|
| 310 |
def OrigShapeStore(self): |
| 311 |
"""Return None since the PostGISShapeStore is not derived from another |
| 312 |
""" |
| 313 |
return None |
| 314 |
|
| 315 |
def ShapeType(self): |
| 316 |
"""Return the type of the shapes in the shapestore.""" |
| 317 |
cursor = self.db.cursor() |
| 318 |
cursor.execute("SELECT type FROM geometry_columns WHERE" |
| 319 |
" f_table_name=%s", (self.tablename,)) |
| 320 |
result = cursor.fetchone()[0] |
| 321 |
cursor.close() |
| 322 |
return shapetype_map[result] |
| 323 |
|
| 324 |
def RawShapeFormat(self): |
| 325 |
"""Return the raw data format of the shape data. |
| 326 |
|
| 327 |
For the PostGISShapeStore this is RAW_WKT. |
| 328 |
""" |
| 329 |
return RAW_WKT |
| 330 |
|
| 331 |
def NumShapes(self): |
| 332 |
# The number of shapes is the same as the number of rows, |
| 333 |
# assuming that the geometry can't be NULL. |
| 334 |
return self.NumRows() |
| 335 |
|
| 336 |
def BoundingBox(self): |
| 337 |
"""Return the bounding box of all shapes in the postgis table""" |
| 338 |
minx = miny = maxx = maxy = None |
| 339 |
x=[] |
| 340 |
y=[] |
| 341 |
cursor = self.db.cursor() |
| 342 |
try: |
| 343 |
# Using the extent function is postgis specific. An OGC |
| 344 |
# Simple Features compliant solution would be to use a query |
| 345 |
# like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and |
| 346 |
# calculate the bounding box by hand from that |
| 347 |
cursor.execute("SELECT extent(%s) FROM %s;" |
| 348 |
% (self.geometry_column, self.tablename)) |
| 349 |
result = cursor.fetchone() |
| 350 |
if result: |
| 351 |
(minx, miny), (maxx, maxy) \ |
| 352 |
= wellknowntext.parse_wkt_thuban(result[0])[0] |
| 353 |
return (minx, miny, maxx, maxy) |
| 354 |
finally: |
| 355 |
cursor.close() |
| 356 |
|
| 357 |
def Shape(self, shapeid): |
| 358 |
cursor = self.db.cursor() |
| 359 |
cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d" |
| 360 |
% (self.geometry_column, self.tablename, shapeid)) |
| 361 |
wkt = cursor.fetchone()[0] |
| 362 |
cursor.close() |
| 363 |
return PostGISShape(shapeid, wkt) |
| 364 |
|
| 365 |
def AllShapes(self): |
| 366 |
cursor = self.db.cursor() |
| 367 |
cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid" |
| 368 |
% (self.geometry_column, self.tablename)) |
| 369 |
while 1: |
| 370 |
result = cursor.fetchone() |
| 371 |
if result is None: |
| 372 |
return |
| 373 |
yield PostGISShape(result[0], result[1]) |
| 374 |
|
| 375 |
|
| 376 |
def ShapesInRegion(self, bbox): |
| 377 |
"""Generate all shapes overlapping the region given by bbox.""" |
| 378 |
# IMPORTANT:This will work for PostGIS < 0.8 |
| 379 |
left, bottom, right, top = bbox |
| 380 |
geom = (("POLYGON((" + ", ".join(["%f %f"] * 5) + "))") |
| 381 |
% (left, bottom, left, top, right, top, right, bottom, |
| 382 |
left, bottom)) |
| 383 |
cursor = self.db.cursor() |
| 384 |
cursor.execute("SELECT gid, AsText(%s) FROM %s" |
| 385 |
" WHERE %s && GeometryFromText('%s', -1) ORDER BY gid" |
| 386 |
% (self.geometry_column, self.tablename, |
| 387 |
self.geometry_column, geom)) |
| 388 |
while 1: |
| 389 |
result = cursor.fetchone() |
| 390 |
if result is None: |
| 391 |
return |
| 392 |
yield PostGISShape(result[0], result[1]) |
Parent Directory
| 
Revision Log