| 1 |
# Copyright (C) 2003 by Intevation GmbH |
# Copyright (C) 2003, 2004 by Intevation GmbH |
| 2 |
# Authors: |
# Authors: |
| 3 |
# Bernhard Herzog <[email protected]> |
# Bernhard Herzog <[email protected]> |
| 4 |
# |
# |
| 17 |
import popen2 |
import popen2 |
| 18 |
import shutil |
import shutil |
| 19 |
import traceback |
import traceback |
| 20 |
|
import re |
| 21 |
|
|
| 22 |
import support |
import support |
| 23 |
|
|
| 116 |
self.socket_dir = socket_dir |
self.socket_dir = socket_dir |
| 117 |
|
|
| 118 |
# For the client side the socket directory can be used as the |
# For the client side the socket directory can be used as the |
| 119 |
# host the name starts with a slash. |
# host if the name starts with a slash. |
| 120 |
self.host = os.path.abspath(socket_dir) |
self.host = os.path.abspath(socket_dir) |
| 121 |
|
|
| 122 |
|
# name and password for the admin and an unprivileged user |
| 123 |
|
self.admin_name = "postgres" |
| 124 |
|
self.admin_password = "postgres" |
| 125 |
|
self.user_name = "observer" |
| 126 |
|
self.user_password = "telescope" |
| 127 |
|
|
| 128 |
# Map db names to db objects |
# Map db names to db objects |
| 129 |
self.known_dbs = {} |
self.known_dbs = {} |
| 130 |
|
|
| 142 |
shutil.rmtree(self.dbdir) |
shutil.rmtree(self.dbdir) |
| 143 |
os.mkdir(self.dbdir) |
os.mkdir(self.dbdir) |
| 144 |
|
|
| 145 |
run_command(["initdb", self.dbdir], |
run_command(["initdb", "-D", self.dbdir, "-U", self.admin_name], |
| 146 |
os.path.join(self.dbdir, "initdb.log")) |
os.path.join(self.dbdir, "initdb.log")) |
| 147 |
|
|
| 148 |
extra_opts = "-p %d" % self.port |
extra_opts = "-p %d" % self.port |
| 157 |
# server ourselves |
# server ourselves |
| 158 |
self.wait_for_postmaster() |
self.wait_for_postmaster() |
| 159 |
|
|
| 160 |
|
self.alter_user(self.admin_name, self.admin_password) |
| 161 |
|
self.create_user(self.user_name, self.user_password) |
| 162 |
|
|
| 163 |
def wait_for_postmaster(self): |
def wait_for_postmaster(self): |
| 164 |
"""Return when the database server is running |
"""Return when the database server is running |
| 165 |
|
|
| 171 |
while count < max_count: |
while count < max_count: |
| 172 |
try: |
try: |
| 173 |
run_command(["psql", "-l", "-p", str(self.port), |
run_command(["psql", "-l", "-p", str(self.port), |
| 174 |
"-h", self.host], |
"-h", self.host, "-U", self.admin_name], |
| 175 |
os.path.join(self.dbdir, "psql-%d.log" % count)) |
os.path.join(self.dbdir, "psql-%d.log" % count)) |
| 176 |
except: |
except RuntimeError: |
| 177 |
pass |
pass |
| 178 |
|
except: |
| 179 |
|
traceback.print_exc() |
| 180 |
else: |
else: |
| 181 |
break |
break |
| 182 |
time.sleep(0.5) |
time.sleep(0.5) |
| 185 |
raise RuntimeError("postmaster didn't start") |
raise RuntimeError("postmaster didn't start") |
| 186 |
|
|
| 187 |
def is_running(self): |
def is_running(self): |
| 188 |
"""Return true a postmaster process is running on self.dbdir |
"""Return whether a postmaster process is running on self.dbdir |
| 189 |
|
|
| 190 |
This method runs pg_ctl status on the dbdir so even if the |
This method runs pg_ctl status on the dbdir and returns True if |
| 191 |
object has just been created it is possible that this method |
that command succeeds and False otherwise. |
| 192 |
returns true if there's still a postmaster process running for |
|
| 193 |
self.dbdir. |
Note that it is possible that this method returns true even if |
| 194 |
|
the PostgreSQLServer instance has just been created and |
| 195 |
|
createdb() has not been called yet. This can happen, for |
| 196 |
|
instance, if the server has been started manually for debugging |
| 197 |
|
purposes after a test suite run. |
| 198 |
""" |
""" |
| 199 |
return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"]) |
return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"]) |
| 200 |
|
|
| 203 |
run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"], |
run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"], |
| 204 |
os.path.join(self.dbdir, "pg_ctl-stop.log")) |
os.path.join(self.dbdir, "pg_ctl-stop.log")) |
| 205 |
|
|
| 206 |
def new_postgis_db(self, dbname, tables = None): |
def new_postgis_db(self, dbname, tables = None, reference_systems = None, |
| 207 |
|
views = None): |
| 208 |
"""Create and return a new PostGISDatabase object using self as server |
"""Create and return a new PostGISDatabase object using self as server |
| 209 |
""" |
""" |
| 210 |
db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables) |
db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables, |
| 211 |
|
reference_systems = reference_systems, |
| 212 |
|
views = views) |
| 213 |
db.initdb() |
db.initdb() |
| 214 |
self.known_dbs[dbname] = db |
self.known_dbs[dbname] = db |
| 215 |
return db |
return db |
| 216 |
|
|
| 217 |
def get_static_data_db(self, dbname, tables): |
def get_static_data_db(self, dbname, tables, reference_systems, views): |
| 218 |
"""Return a PostGISDatabase for a database with the given static data |
"""Return a PostGISDatabase for a database with the given static data |
| 219 |
|
|
| 220 |
If no databasse of the name dbname exists, create a new one via |
If no databasse of the name dbname exists, create a new one via |
| 224 |
indicated data, return that. If the already existing db uses |
indicated data, return that. If the already existing db uses |
| 225 |
different data raise a value error. |
different data raise a value error. |
| 226 |
|
|
| 227 |
The tables argument should be a sequence of table specifications |
If the database doesn't exist, create a new one via |
| 228 |
where each specifications is a (tablename, shapefilename) pair. |
self.new_postgis_db. |
| 229 |
|
|
| 230 |
|
The parameters tables and reference_systems have the same |
| 231 |
|
meaning as for new_postgis_db. |
| 232 |
""" |
""" |
| 233 |
db = self.known_dbs.get(dbname) |
db = self.known_dbs.get(dbname) |
| 234 |
if db is not None: |
if db is not None: |
| 235 |
if db.has_data(tables): |
if db.has_data(tables, reference_systems, views): |
| 236 |
return db |
return db |
| 237 |
raise ValueError("PostGISDatabase named %r doesn't have tables %r" |
raise ValueError("PostGISDatabase named %r doesn't have tables %r" |
| 238 |
% (dbname, tables)) |
% (dbname, tables)) |
| 239 |
return self.new_postgis_db(dbname, tables) |
return self.new_postgis_db(dbname, tables, reference_systems, views) |
| 240 |
|
|
| 241 |
def get_default_static_data_db(self): |
def get_default_static_data_db(self): |
| 242 |
dbname = "PostGISStaticTests" |
dbname = "PostGISStaticTests" |
| 243 |
tables = [("landmarks", os.path.join("..", "Data", "iceland", |
srids = [(1, "proj=longlat datum=WGS84")] |
| 244 |
"cultural_landmark-point.shp")), |
tables = [ |
| 245 |
("political", os.path.join("..", "Data", "iceland", |
# Direct copies of the shapefiles. The shapeids are exactly |
| 246 |
|
# the same, except where changed with "gid_offset", of |
| 247 |
|
# course. Note that the test implementation requires that |
| 248 |
|
# all the landmard tables use an gid_offset of 1000. |
| 249 |
|
("landmarks", os.path.join("..", "Data", "iceland", |
| 250 |
|
"cultural_landmark-point.shp"), |
| 251 |
|
[("gid_offset", 1000)]), |
| 252 |
|
("political", os.path.join("..", "Data", "iceland", |
| 253 |
"political.shp")), |
"political.shp")), |
| 254 |
("roads", os.path.join("..", "Data", "iceland", |
("roads", os.path.join("..", "Data", "iceland", |
| 255 |
"roads-line.shp"))] |
"roads-line.shp")), |
| 256 |
return self.get_static_data_db(dbname, tables) |
|
| 257 |
|
# The polygon data as a MULTIPOLYGON geometry type |
| 258 |
|
("political_multi", os.path.join("..", "Data", "iceland", |
| 259 |
|
"political.shp"), |
| 260 |
|
[("force_wkt_type", "MULTIPOLYGON")]), |
| 261 |
|
|
| 262 |
|
# Copy of landmarks but using an srid != -1 |
| 263 |
|
("landmarks_srid", os.path.join("..", "Data", "iceland", |
| 264 |
|
"cultural_landmark-point.shp"), |
| 265 |
|
[("gid_offset", 1000), |
| 266 |
|
("srid", 1)]), |
| 267 |
|
|
| 268 |
|
# Copy of landmarks with a gid column called "point_id" instead |
| 269 |
|
# of "gid" and using an srid != -1. |
| 270 |
|
("landmarks_point_id", os.path.join("..", "Data", "iceland", |
| 271 |
|
"cultural_landmark-point.shp"), |
| 272 |
|
[("gid_offset", 1000), |
| 273 |
|
("srid", 1), |
| 274 |
|
("gid_column", "point_id")]), |
| 275 |
|
] |
| 276 |
|
views = [("v_landmarks", "SELECT * FROM landmarks_point_id")] |
| 277 |
|
return self.get_static_data_db(dbname, tables, srids, views) |
| 278 |
|
|
| 279 |
|
def connection_params(self, user): |
| 280 |
|
"""Return the connection parameters for the given user |
| 281 |
|
|
| 282 |
|
The return value is a dictionary suitable as keyword argument |
| 283 |
|
list to PostGISConnection. The user parameter may be either |
| 284 |
|
'admin' to connect as admin or 'user' to connect as an |
| 285 |
|
unprivileged user. |
| 286 |
|
""" |
| 287 |
|
return {"host": self.host, "port": self.port, |
| 288 |
|
"user": getattr(self, user + "_name"), |
| 289 |
|
"password": getattr(self, user + "_password")} |
| 290 |
|
|
| 291 |
|
def connection_string(self, user): |
| 292 |
|
"""Return (part of) the connection string to pass to psycopg.connect |
| 293 |
|
|
| 294 |
|
The string contains host, port, user and password. The user |
| 295 |
|
parameter must be either 'admin' or 'user', as for |
| 296 |
|
connection_params. |
| 297 |
|
""" |
| 298 |
|
params = [] |
| 299 |
|
for key, value in self.connection_params(user).items(): |
| 300 |
|
# FIXME: this doesn't do quiting correctly but that |
| 301 |
|
# shouldn't be much of a problem (people shouldn't be using |
| 302 |
|
# single quotes in filenames anyway :) ) |
| 303 |
|
params.append("%s='%s'" % (key, value)) |
| 304 |
|
return " ".join(params) |
| 305 |
|
|
| 306 |
|
def execute_sql(self, dbname, user, sql): |
| 307 |
|
"""Execute the sql statament and return a result for SELECT statements |
| 308 |
|
|
| 309 |
|
The user parameter us used as in connection_params. The dbname |
| 310 |
|
parameter must be the name of a database in the cluster. The |
| 311 |
|
sql parameter is the SQL statement to execute as a string. If |
| 312 |
|
the string starts with 'select' (matched case insensitively) the |
| 313 |
|
first row of the result will be returned. Otherwise the return |
| 314 |
|
value is None. |
| 315 |
|
""" |
| 316 |
|
conn = psycopg.connect("dbname=%s " % dbname |
| 317 |
|
+ self.connection_string(user)) |
| 318 |
|
cursor = conn.cursor() |
| 319 |
|
cursor.execute(sql) |
| 320 |
|
if sql.lower().startswith("select"): |
| 321 |
|
row = cursor.fetchone() |
| 322 |
|
else: |
| 323 |
|
row = None |
| 324 |
|
conn.commit() |
| 325 |
|
conn.close() |
| 326 |
|
return row |
| 327 |
|
|
| 328 |
|
def server_version(self): |
| 329 |
|
"""Return the server version as a tuple (major, minor, patch) |
| 330 |
|
|
| 331 |
|
Each item in the tuple is an int. |
| 332 |
|
""" |
| 333 |
|
result = self.execute_sql("template1", "admin", "SELECT version();")[0] |
| 334 |
|
match = re.match(r"PostgreSQL (\d+\.\d+\.\d+)", result) |
| 335 |
|
if match: |
| 336 |
|
return tuple(map(int, match.group(1).split("."))) |
| 337 |
|
else: |
| 338 |
|
raise RutimeError("Cannot determine PostgreSQL server version" |
| 339 |
|
" from %r" % result) |
| 340 |
|
|
| 341 |
|
def require_authentication(self, required): |
| 342 |
|
"""Switch authentication requirements on or off |
| 343 |
|
|
| 344 |
|
When started for the first time no passwords are required. Some |
| 345 |
|
tests want to explicitly test whether Thuban's password |
| 346 |
|
infrastructure works and switch password authentication on |
| 347 |
|
explicitly. When switching it on, there should be a |
| 348 |
|
corresponding call to switch it off again in the test case' |
| 349 |
|
tearDown method or in a finally: block. |
| 350 |
|
""" |
| 351 |
|
# Starting with PostgreSQL 7.3 the pg_hba.conf file has an |
| 352 |
|
# additional column with a username. Query the server version |
| 353 |
|
# and generate a file in the correct format. |
| 354 |
|
if self.server_version() >= (7, 3): |
| 355 |
|
user = "all" |
| 356 |
|
else: |
| 357 |
|
user = "" |
| 358 |
|
if required: |
| 359 |
|
contents = "local all %s password\n" % user |
| 360 |
|
else: |
| 361 |
|
contents = "local all %s trust\n" % user |
| 362 |
|
f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w") |
| 363 |
|
f.write(contents) |
| 364 |
|
f.close() |
| 365 |
|
run_command(["pg_ctl", "-D", self.dbdir, "reload"], |
| 366 |
|
os.path.join(self.dbdir, "pg_ctl-reload.log")) |
| 367 |
|
|
| 368 |
|
|
| 369 |
|
def create_user(self, username, password): |
| 370 |
|
"""Create user username with password in the database""" |
| 371 |
|
self.execute_sql("template1", "admin", |
| 372 |
|
"CREATE USER %s PASSWORD '%s';" % (username,password)) |
| 373 |
|
|
| 374 |
|
def alter_user(self, username, password): |
| 375 |
|
"""Change the user username's password in the database""" |
| 376 |
|
self.execute_sql("template1", "admin", |
| 377 |
|
"ALTER USER %s PASSWORD '%s';" % (username,password)) |
| 378 |
|
|
| 379 |
|
|
| 380 |
class PostGISDatabase: |
class PostGISDatabase: |
| 381 |
|
|
| 382 |
"""A PostGIS database in a PostgreSQLServer""" |
"""A PostGIS database in a PostgreSQLServer""" |
| 383 |
|
|
| 384 |
def __init__(self, server, postgis_sql, dbname, tables = None): |
def __init__(self, server, postgis_sql, dbname, tables = None, |
| 385 |
|
reference_systems = (), views = None): |
| 386 |
|
"""Initialize the PostGISDatabase |
| 387 |
|
|
| 388 |
|
Parameters: |
| 389 |
|
|
| 390 |
|
server -- The PostgreSQLServer instance containing the |
| 391 |
|
database |
| 392 |
|
|
| 393 |
|
postgis_sql -- Filename of the postgis.sql file with the |
| 394 |
|
postgis initialization code |
| 395 |
|
|
| 396 |
|
dbname -- The name of the database |
| 397 |
|
|
| 398 |
|
tables -- Optional description of tables to create in the |
| 399 |
|
new database. If given it should be a list of |
| 400 |
|
(tablename, shapefilename) pairs meaning that a table |
| 401 |
|
tablename will be created with the contents of the given |
| 402 |
|
shapefile or (tablename, shapefilename, extraargs) |
| 403 |
|
triples. The extraargs should be a list of key, value |
| 404 |
|
pairs to use as keyword arguments to upload_shapefile. |
| 405 |
|
|
| 406 |
|
reference_systems -- Optional description of spatial |
| 407 |
|
reference systems. If given, it should be a sequence of |
| 408 |
|
(srid, params) pairs where srid is the srid defined by |
| 409 |
|
the proj4 paramter string params. The srid can be given |
| 410 |
|
as an extra parameter in the tables list. |
| 411 |
|
|
| 412 |
|
views -- Optional description of views. If given it should |
| 413 |
|
be a list of (viewname, select_stmt) pairs where |
| 414 |
|
viewname is the name of the view to be created and |
| 415 |
|
select_stmt is the select statement to use as the basis. |
| 416 |
|
The views will be created after the tables and may refer |
| 417 |
|
to them in the select_stmt. |
| 418 |
|
""" |
| 419 |
self.server = server |
self.server = server |
| 420 |
self.postgis_sql = postgis_sql |
self.postgis_sql = postgis_sql |
| 421 |
self.dbname = dbname |
self.dbname = dbname |
| 422 |
self.tables = tables |
self.tables = tables |
| 423 |
|
self.views = views |
| 424 |
|
if reference_systems: |
| 425 |
|
self.reference_systems = reference_systems |
| 426 |
|
else: |
| 427 |
|
# Make sure that it's a sequence we can iterate over even if |
| 428 |
|
# the parameter's None |
| 429 |
|
self.reference_systems = () |
| 430 |
|
|
| 431 |
def initdb(self): |
def initdb(self): |
| 432 |
"""Remove the old db directory and create and initialize a new database |
"""Remove the old db directory and create and initialize a new database |
| 433 |
""" |
""" |
| 434 |
run_command(["createdb", "-p", str(self.server.port), |
run_command(["createdb", "-p", str(self.server.port), |
| 435 |
"-h", self.server.host, self.dbname], |
"-h", self.server.host, "-U", self.server.admin_name, |
| 436 |
|
self.dbname], |
| 437 |
os.path.join(self.server.dbdir, "createdb.log")) |
os.path.join(self.server.dbdir, "createdb.log")) |
| 438 |
run_command(["createlang", "-p", str(self.server.port), |
run_command(["createlang", "-p", str(self.server.port), |
| 439 |
"-h", self.server.host, "plpgsql", self.dbname], |
"-h", self.server.host, "-U", self.server.admin_name, |
| 440 |
|
"plpgsql", self.dbname], |
| 441 |
os.path.join(self.server.dbdir, "createlang.log")) |
os.path.join(self.server.dbdir, "createlang.log")) |
| 442 |
# for some reason psql doesn't exit with an error code if the |
# for some reason psql doesn't exit with an error code if the |
| 443 |
# file given as -f doesn't exist, so we check manually by trying |
# file given as -f doesn't exist, so we check manually by trying |
| 446 |
f.close() |
f.close() |
| 447 |
del f |
del f |
| 448 |
run_command(["psql", "-f", self.postgis_sql, "-d", self.dbname, |
run_command(["psql", "-f", self.postgis_sql, "-d", self.dbname, |
| 449 |
"-p", str(self.server.port), "-h", self.server.host], |
"-p", str(self.server.port), "-h", self.server.host, |
| 450 |
|
"-U", self.server.admin_name], |
| 451 |
os.path.join(self.server.dbdir, "psql.log")) |
os.path.join(self.server.dbdir, "psql.log")) |
| 452 |
|
|
| 453 |
|
self.server.execute_sql(self.dbname, "admin", |
| 454 |
|
"GRANT SELECT ON geometry_columns TO PUBLIC;") |
| 455 |
|
self.server.execute_sql(self.dbname, "admin", |
| 456 |
|
"GRANT SELECT ON spatial_ref_sys TO PUBLIC;") |
| 457 |
|
|
| 458 |
|
for srid, params in self.reference_systems: |
| 459 |
|
self.server.execute_sql(self.dbname, "admin", |
| 460 |
|
"INSERT INTO spatial_ref_sys VALUES" |
| 461 |
|
" (%d, '', %d, '', '%s');" |
| 462 |
|
% (srid, srid, params)) |
| 463 |
if self.tables is not None: |
if self.tables is not None: |
| 464 |
for tablename, shapefile in self.tables: |
def unpack(item): |
| 465 |
upload_shapefile(shapefile, self, tablename) |
extra = {"force_wkt_type": None, "gid_offset": 0, |
| 466 |
|
"srid": -1} |
| 467 |
def has_data(self, tables): |
if len(info) == 2: |
| 468 |
return self.tables == tables |
tablename, shapefile = info |
| 469 |
|
else: |
| 470 |
|
tablename, shapefile, kw = info |
| 471 |
|
for key, val in kw: |
| 472 |
|
extra[key] = val |
| 473 |
|
return tablename, shapefile, extra |
| 474 |
|
|
| 475 |
|
for info in self.tables: |
| 476 |
|
tablename, shapefile, kw = unpack(info) |
| 477 |
|
upload_shapefile(shapefile, self, tablename, **kw) |
| 478 |
|
|
| 479 |
|
if self.views is not None: |
| 480 |
|
for viewname, select_stmt in self.views: |
| 481 |
|
self.server.execute_sql(self.dbname, "admin", |
| 482 |
|
"CREATE VIEW %s AS %s" % (viewname, |
| 483 |
|
select_stmt)) |
| 484 |
|
self.server.execute_sql(self.dbname, "admin", |
| 485 |
|
"GRANT SELECT ON %s TO PUBLIC;" |
| 486 |
|
% viewname) |
| 487 |
|
|
| 488 |
|
def has_data(self, tables, reference_systems, views): |
| 489 |
|
return (self.tables == tables |
| 490 |
|
and self.reference_systems == reference_systems |
| 491 |
|
and self.views == views) |
| 492 |
|
|
| 493 |
|
|
| 494 |
def find_postgis_sql(): |
def find_postgis_sql(): |
| 547 |
The name of the postgis_sql file is determined by find_postgis_sql() |
The name of the postgis_sql file is determined by find_postgis_sql() |
| 548 |
- psycopg can be imported successfully. |
- psycopg can be imported successfully. |
| 549 |
""" |
""" |
| 550 |
|
# run_command currently uses Popen4 which is not available under |
| 551 |
|
# Windows, for example. |
| 552 |
|
if not hasattr(popen2, "Popen4"): |
| 553 |
|
return "Can't run PostGIS test because popen2.Popen4 does not exist" |
| 554 |
|
|
| 555 |
try: |
try: |
| 556 |
run_command(["pg_ctl", "--help"], None) |
run_command(["pg_ctl", "--help"], None) |
| 557 |
except RuntimeError: |
except RuntimeError: |
| 584 |
if _cannot_run_postgis_tests: |
if _cannot_run_postgis_tests: |
| 585 |
raise support.SkipTest(_cannot_run_postgis_tests) |
raise support.SkipTest(_cannot_run_postgis_tests) |
| 586 |
|
|
| 587 |
def point_to_wkt(coords): |
def skip_if_addgeometrycolumn_does_not_use_quote_ident(): |
| 588 |
|
"""Skip a test if the AddGeometryColumn function doesn't use quote_ident |
| 589 |
|
|
| 590 |
|
If the AddGeometryColumn function doesn't use quote_ident it doesn't |
| 591 |
|
support unusual table or column names properly, that is, it will |
| 592 |
|
fail with errors for names that contain spaces or double quotes. |
| 593 |
|
|
| 594 |
|
The test performed by this function is a bit simplistic because it |
| 595 |
|
only tests whether the string 'quote_ident' occurs anywhere in the |
| 596 |
|
postgis.sql file. This will hopefully work because when this was |
| 597 |
|
fixed in postgis CVS AddGeometryColumn was the first function to use |
| 598 |
|
quote_ident. |
| 599 |
|
""" |
| 600 |
|
f = file(find_postgis_sql()) |
| 601 |
|
content = f.read() |
| 602 |
|
f.close() |
| 603 |
|
if content.find("quote_ident") < 0: |
| 604 |
|
raise support.SkipTest("AddGeometryColumn doesn't use quote_ident") |
| 605 |
|
|
| 606 |
|
def coords_to_point(coords): |
| 607 |
"""Return string with a WKT representation of the point in coords""" |
"""Return string with a WKT representation of the point in coords""" |
| 608 |
x, y = coords[0] |
x, y = coords[0] |
| 609 |
return "POINT(%r %r)" % (x, y) |
return "POINT(%r %r)" % (x, y) |
| 610 |
|
|
| 611 |
def polygon_to_wkt(coords): |
def coords_to_polygon(coords): |
| 612 |
"""Return string with a WKT representation of the polygon in coords""" |
"""Return string with a WKT representation of the polygon in coords""" |
| 613 |
poly = [] |
poly = [] |
| 614 |
for ring in coords: |
for ring in coords: |
| 615 |
poly.append(", ".join(["%r %r" % p for p in ring])) |
poly.append(", ".join(["%r %r" % p for p in ring])) |
| 616 |
return "POLYGON((%s))" % "), (".join(poly) |
return "POLYGON((%s))" % "), (".join(poly) |
| 617 |
|
|
| 618 |
def arc_to_wkt(coords): |
def coords_to_multilinestring(coords): |
| 619 |
"""Return string with a WKT representation of the arc in coords""" |
"""Return string with a WKT representation of the arc in coords""" |
| 620 |
poly = [] |
poly = [] |
| 621 |
for ring in coords: |
for ring in coords: |
| 622 |
poly.append(", ".join(["%r %r" % p for p in ring])) |
poly.append(", ".join(["%r %r" % p for p in ring])) |
| 623 |
return "MULTILINESTRING((%s))" % "), (".join(poly) |
return "MULTILINESTRING((%s))" % "), (".join(poly) |
| 624 |
|
|
| 625 |
def upload_shapefile(filename, db, tablename): |
def coords_to_multipolygon(coords): |
| 626 |
|
"""Return string with a WKT representation of the polygon in coords""" |
| 627 |
|
poly = [] |
| 628 |
|
for ring in coords: |
| 629 |
|
poly.append(", ".join(["%r %r" % p for p in ring])) |
| 630 |
|
return "MULTIPOLYGON(((%s)))" % ")), ((".join(poly) |
| 631 |
|
|
| 632 |
|
wkt_converter = { |
| 633 |
|
"POINT": coords_to_point, |
| 634 |
|
"MULTILINESTRING": coords_to_multilinestring, |
| 635 |
|
"POLYGON": coords_to_polygon, |
| 636 |
|
"MULTIPOLYGON": coords_to_multipolygon, |
| 637 |
|
} |
| 638 |
|
|
| 639 |
|
def upload_shapefile(filename, db, tablename, force_wkt_type = None, |
| 640 |
|
gid_offset = 0, gid_column = "gid", srid = -1): |
| 641 |
|
"""Upload a shapefile into a new database table |
| 642 |
|
|
| 643 |
|
Parameters: |
| 644 |
|
|
| 645 |
|
filename -- The name of the shapefile |
| 646 |
|
|
| 647 |
|
db -- The PostGISDatabase instance representing the database |
| 648 |
|
|
| 649 |
|
tablename -- The name of the table to create and into which the data |
| 650 |
|
is to be inserted |
| 651 |
|
|
| 652 |
|
force_wkt_type -- If given the real WKT geometry type to use instead |
| 653 |
|
of the default that would be chosen based on the type of |
| 654 |
|
the shapefile |
| 655 |
|
|
| 656 |
|
gid_offset -- A number to add to the shapeid to get the value for |
| 657 |
|
the gid column (default 0) |
| 658 |
|
|
| 659 |
|
gid_column -- The name of the column with the shape ids. Default |
| 660 |
|
'gid'. If None, no gid column will be created. The |
| 661 |
|
name is directly used in SQL statements, so if it |
| 662 |
|
contains unusualy characters the caller should provide |
| 663 |
|
a suitable quoted string. |
| 664 |
|
|
| 665 |
|
srid -- The srid of the spatial references system used by the table |
| 666 |
|
and the data |
| 667 |
|
""" |
| 668 |
import dbflib, shapelib |
import dbflib, shapelib |
| 669 |
|
|
| 670 |
|
# We build this map here because we need shapelib which can only be |
| 671 |
|
# imported after support.initthuban has been called which we can't |
| 672 |
|
# easily do in this module because it's imported by support. |
| 673 |
|
shp_to_wkt = { |
| 674 |
|
shapelib.SHPT_POINT: "POINT", |
| 675 |
|
shapelib.SHPT_ARC: "MULTILINESTRING", |
| 676 |
|
shapelib.SHPT_POLYGON: "POLYGON", |
| 677 |
|
} |
| 678 |
|
|
| 679 |
server = db.server |
server = db.server |
| 680 |
dbname = db.dbname |
dbname = db.dbname |
| 681 |
conn = psycopg.connect("host=%s port=%s dbname=%s" |
conn = psycopg.connect("dbname=%s " % dbname |
| 682 |
% (server.host, server.port, dbname)) |
+ db.server.connection_string("admin")) |
| 683 |
cursor = conn.cursor() |
cursor = conn.cursor() |
| 684 |
|
|
| 685 |
shp = shapelib.ShapeFile(filename) |
shp = shapelib.ShapeFile(filename) |
| 688 |
dbflib.FTInteger: "INTEGER", |
dbflib.FTInteger: "INTEGER", |
| 689 |
dbflib.FTDouble: "DOUBLE PRECISION"} |
dbflib.FTDouble: "DOUBLE PRECISION"} |
| 690 |
|
|
| 691 |
insert_formats = ["%(gid)s"] |
insert_formats = [] |
| 692 |
fields = ["gid INT"] |
if gid_column: |
| 693 |
|
insert_formats.append("%(gid)s") |
| 694 |
|
|
| 695 |
|
fields = [] |
| 696 |
|
fields_decl = [] |
| 697 |
|
if gid_column: |
| 698 |
|
fields.append(gid_column) |
| 699 |
|
fields_decl.append("%s INT" % gid_column) |
| 700 |
for i in range(dbf.field_count()): |
for i in range(dbf.field_count()): |
| 701 |
ftype, name, width, prec = dbf.field_info(i) |
ftype, name, width, prec = dbf.field_info(i) |
| 702 |
fields.append("%s %s" % (name, typemap[ftype])) |
fields.append(name) |
| 703 |
|
fields_decl.append("%s %s" % (name, typemap[ftype])) |
| 704 |
insert_formats.append("%%(%s)s" % name) |
insert_formats.append("%%(%s)s" % name) |
| 705 |
stmt = "CREATE TABLE %s (\n %s\n);" % (tablename, |
stmt = "CREATE TABLE %s (\n %s\n);" % (tablename, |
| 706 |
",\n ".join(fields)) |
",\n ".join(fields_decl)) |
| 707 |
cursor.execute(stmt) |
cursor.execute(stmt) |
| 708 |
#print stmt |
#print stmt |
| 709 |
|
|
| 710 |
numshapes, shapetype, mins, maxs = shp.info() |
numshapes, shapetype, mins, maxs = shp.info() |
| 711 |
if shapetype == shapelib.SHPT_POINT: |
wkttype = shp_to_wkt[shapetype] |
| 712 |
convert = point_to_wkt |
if force_wkt_type: |
| 713 |
wkttype = "POINT" |
wkttype = force_wkt_type |
| 714 |
elif shapetype == shapelib.SHPT_POLYGON: |
convert = wkt_converter[wkttype] |
|
convert = polygon_to_wkt |
|
|
wkttype = "POLYGON" |
|
|
elif shapetype == shapelib.SHPT_ARC: |
|
|
convert = arc_to_wkt |
|
|
wkttype = "MULTILINESTRING" |
|
|
else: |
|
|
raise ValueError("Unsupported Shapetype %r" % shapetype) |
|
| 715 |
|
|
| 716 |
cursor.execute("select AddGeometryColumn('%(dbname)s'," |
cursor.execute("select AddGeometryColumn('%(dbname)s'," |
| 717 |
"'%(tablename)s', 'the_geom', '-1', '%(wkttype)s', 2);" |
"'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);" |
| 718 |
% locals()) |
% locals()) |
| 719 |
|
fields.append("the_geom") |
| 720 |
|
insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)") |
| 721 |
|
|
| 722 |
insert_formats.append("GeometryFromText(%(the_geom)s, -1)") |
insert = ("INSERT INTO %s (%s) VALUES (%s)" |
| 723 |
|
% (tablename, ", ".join(fields), ", ".join(insert_formats))) |
|
insert = ("INSERT INTO %s VALUES (%s)" |
|
|
% (tablename, ", ".join(insert_formats))) |
|
| 724 |
|
|
| 725 |
for i in range(numshapes): |
for i in range(numshapes): |
| 726 |
data = dbf.read_record(i) |
data = dbf.read_record(i) |
| 727 |
data["tablename"] = tablename |
data["tablename"] = tablename |
| 728 |
data["gid"] = i |
if gid_column: |
| 729 |
|
data["gid"] = i + gid_offset |
| 730 |
|
data["srid"] = srid |
| 731 |
data["the_geom"] = convert(shp.read_object(i).vertices()) |
data["the_geom"] = convert(shp.read_object(i).vertices()) |
| 732 |
#print insert % data |
#print insert % data |
| 733 |
cursor.execute(insert, data) |
cursor.execute(insert, data) |
| 734 |
|
|
| 735 |
|
cursor.execute("GRANT SELECT ON %s TO PUBLIC;" % tablename) |
| 736 |
|
|
| 737 |
conn.commit() |
conn.commit() |
Parent Directory
| 
Revision Log
| 
Patch