Last active
October 10, 2022 09:02
-
-
Save justinlewis/4398913 to your computer and use it in GitHub Desktop.
A simple script for converting PostGIS tables to shapefiles with ogr2ogr.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| USAGE: | |
| 1. Install Python, PyGreSQL (http://www.pygresql.org/), and FWTools (http://fwtools.maptools.org/) if not already installed. | |
| 2. Download the postgis2shp.py file. | |
| 3. Change the paramaters at the bottom to fit your needs | |
| 1. export_path = path to a directory you want the export data to be stored at. | |
| 2. host = host for the PostGIS database server (localhost by default) | |
| 3. username = PostGIS database username | |
| 4. password = PostGIS database password | |
| 5. db = PostGIS database name | |
| 4. Run it. There will be some interactivity in the shell. | |
| Note: On some instalations of PyGreSQL (Windows) the import must be written as import pg rather than import _pg. | |
| POTENTIAL IMPROVEMENTS: | |
| 1. This currently requires local installations of ogr. Another approach would be to use the pg2shape dumper native to PostGIS. This would require less dependencies for usage. | |
| KNOWN ISSUES: | |
| 1. The initial ogr2ogr export function exports the shapefile with unreliable projection information (SRID). To ensure the shapefile retains the correct SRID the script creates a 2nd shapefile with the correct SRID. The original export file is NOT deleted. | |
| a. This should be fixed so either the export function exports with the correct SRID or the original export is deleted. It seems to be a result of ogr not recognizing the geometry field or the getsrid() ogr function not working corectly. | |
| 2. Need to add code to delete original exported shape and leave the projected shape. | |
| 3. Rathers than automatically reproject the original shape export we could just try creating a .prj file for original export. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #### Author: Justin Lewis | |
| #### This script can be used to export a single pg table to shapefile. | |
| #### | |
| #### Developed in a linux environment but should require minimal modification to run on other platforms. | |
| #### | |
| #### Dependancies: | |
| ###### fwtools (gdal > ogr2ogr), PostGIS database, Python, PyGreSQL | |
| import os, _pg | |
| class pg_toshape: | |
| def pg_table_names(self, db): | |
| tableList = [] | |
| print "These are the tables in the 'public' schema of your database. Use them for reference for the next step." | |
| print "" | |
| sel = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' and table_catalog = '{db}';".format(db = db) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| for t in result: | |
| t = self.strip_list_entries(t) | |
| print t | |
| print "" | |
| print ">>>>>>>>>>>>>>>>>>>>>>>>>>>>" | |
| print "" | |
| table = raw_input("Enter the name of the table you want to export: ") | |
| if table != '': | |
| tableList.append(table) | |
| more = 'y' | |
| while more == 'y': | |
| more = raw_input("Any more? ('y' or 'n') ") | |
| if more == 'y': | |
| table = raw_input("Enter the name of the table: ") | |
| if table in tableList: | |
| print table, " is already in your list of tables." | |
| else: | |
| tableList.append(table) | |
| elif more == 'n': | |
| print "These are the tables you selected to export:" | |
| for t in tableList: | |
| print t | |
| return tableList | |
| def export_pg_table(self, export_path, pgtable_name, host, username, password, db, pg_sql_select): | |
| print "Exporting shapefile using the ", geom, "geometry field..." | |
| cmd = '''ogr2ogr -overwrite -f \"ESRI Shapefile\" {export_path}{pgtable_name}.shp PG:"host={host} user={username} dbname={db} password={password}" -sql "{pg_sql_select}"'''.format(pgtable_name = pgtable_name, export_path = export_path, host = host, username = username, db = db, password = password, pg_sql_select = pg_sql_select) | |
| os.system(cmd) | |
| def strip_list_entries(self, entry): | |
| entry = str(entry) | |
| entry = entry.lstrip("('") | |
| entry = entry.rstrip("',)") | |
| return entry | |
| def select_geom(self, pgtable_name, host, username, password, db): | |
| sel = "select f_geometry_column from geometry_columns where f_table_name = '{pgtable_name}';".format(pgtable_name = pgtable_name) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| ct = len(result) | |
| if ct > 1: | |
| geomList = [] | |
| print "There are ", ct, "geometry fields in {pgtable_name}.".format(pgtable_name = pgtable_name) | |
| print "-------------------------" | |
| for f in result: | |
| f = self.strip_list_entries(f) | |
| print f | |
| geomList.append(f) | |
| print "-------------------------" | |
| geom = raw_input("Enter the geom field to export: ") | |
| print "-------------------------" | |
| while geom not in geomList: | |
| print "-------------------------" | |
| print "Incorect field name. Try again." | |
| geom = raw_input("Enter the geom field to export: ") | |
| return geom | |
| else: | |
| for f in result: | |
| geom = self.strip_list_entries(f) | |
| return geom | |
| def select_field_names(self, pgtable_name, geom): | |
| sel = "select column_name from information_schema.columns where table_name = '{pgtable_name}' AND udt_name <> 'geometry';".format(pgtable_name = pgtable_name) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| fields = geom | |
| for f in result: | |
| f = self.strip_list_entries(f) | |
| fields = f + ", " + fields | |
| return fields | |
| def select_srid(self, pgtable_name, host, username, password, db, geom): | |
| sel = "SELECT ST_SRID({geom}) AS srid FROM {pgtable_name} GROUP BY srid;".format(pgtable_name = pgtable_name, geom = geom) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| for s in result: | |
| srid = self.strip_list_entries(s) | |
| print "" | |
| print "The source table is stored with srid = ", srid | |
| return srid | |
| def set_srid(self, srid): | |
| print "Generating a 2nd shapefile with the correct SRID and calling it {pgtable_name}_{srid}.shp...".format(pgtable_name = pgtable_name, srid = srid) | |
| cmd = "ogr2ogr -overwrite -a_srs EPSG:{srid} {export_path}{pgtable_name}_{srid}.shp {export_path}{pgtable_name}.shp".format(pgtable_name = pgtable_name, export_path = export_path, srid = srid) | |
| process = os.system(cmd) | |
| def transform_srid(self, source_srid, pgtable_name, export_path): | |
| print "-------------------------" | |
| print "Current SRID = ", source_srid | |
| print "-------------------------" | |
| answer = ['y', 'n'] | |
| set_new_srid = raw_input("Would you like to set a different SRID (y or n)? ") | |
| while set_new_srid not in answer: | |
| set_new_srid = raw_input("Would you like to set a different SRID (y or n)? ") | |
| if set_new_srid == 'y': | |
| new_srid = raw_input("What SRID would you like to transform the data to? ") | |
| cmd = "ogr2ogr -overwrite -s_srs EPSG:{source_srid} -t_srs EPSG:{new_srid} {export_path}{pgtable_name}_{new_srid}.shp {export_path}{pgtable_name}_{source_srid}.shp".format(pgtable_name = pgtable_name, source_srid = source_srid, new_srid = new_srid, export_path = export_path) | |
| process = os.system(cmd) | |
| else: | |
| pass | |
| if __name__ == '__main__': | |
| #### Export paramaters | |
| export_path = "~/Documents/github/postgis2shape/test_data/" | |
| #### Connection paramaters for the PostGIS database. | |
| host = "localhost" | |
| username = "username" | |
| password = "password" | |
| db = "database" | |
| conn = _pg.connect(dbname = db, host= host, port= 5432, user = username, passwd= password) | |
| #### Running the script | |
| pg_toshape = pg_toshape() | |
| table_list = pg_toshape.pg_table_names(db) | |
| for table in table_list: | |
| pgtable_name = table | |
| geom = pg_toshape.select_geom(pgtable_name, host, username, password, db) | |
| select_fields = pg_toshape.select_field_names(pgtable_name, geom) | |
| pg_sql_select = "SELECT " + select_fields + " FROM " + pgtable_name + ";" | |
| pg_toshape.export_pg_table(export_path, pgtable_name, host, username, password, db, pg_sql_select) | |
| source_srid = pg_toshape.select_srid(pgtable_name, host, username, password, db, geom) | |
| pg_toshape.set_srid(source_srid) | |
| pg_toshape.transform_srid(source_srid, pgtable_name, export_path) | |
| print "" | |
| print "Process complete." |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #### Author: Justin Lewis | |
| #### This script can be used to bulk export pg tables to shapefile. | |
| #### | |
| #### Developed in a linux environment but should require minimal modification to run on other platforms. | |
| #### | |
| #### Dependancies: | |
| ###### fwtools (gdal > ogr2ogr), PostGIS database, Python, PyGreSQL | |
| import os, _pg, sys | |
| class pg_toshape: | |
| def export_pg_table(self, export_path, pgtable_name, host, username, password, db, pg_sql_select): | |
| print "Exporting shapefile using the ", geom, "geometry field..." | |
| cmd = '''ogr2ogr -overwrite -f \"ESRI Shapefile\" {export_path}{pgtable_name}.shp PG:"host={host} user={username} dbname={db} password={password}" -sql "{pg_sql_select}"'''.format(pgtable_name = pgtable_name, export_path = export_path, host = host, username = username, db = db, password = password, pg_sql_select = pg_sql_select) | |
| process = os.system(cmd) | |
| def strip_list_entries(self, entry): | |
| entry = str(entry) | |
| entry = entry.lstrip("('") | |
| entry = entry.rstrip("',)") | |
| return entry | |
| def select_geom(self, pgtable_name, host, username, password, db): | |
| sel = "select f_geometry_column from geometry_columns where f_table_name = '{pgtable_name}';".format(pgtable_name = pgtable_name) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| ct = len(result) | |
| if ct > 1: | |
| geomList = [] | |
| print "There are ", ct, "geometry fields in {pgtable_name}.".format(pgtable_name = pgtable_name) | |
| print "-------------------------" | |
| for f in result: | |
| f = self.strip_list_entries(f) | |
| print f | |
| geomList.append(f) | |
| print "-------------------------" | |
| geom = raw_input("Enter the geom field to export: ") | |
| print "-------------------------" | |
| while geom not in geomList: | |
| print "-------------------------" | |
| print "Incorect field name. Try again." | |
| geom = raw_input("Enter the geom field to export: ") | |
| return geom | |
| else: | |
| for f in result: | |
| geom = self.strip_list_entries(f) | |
| return geom | |
| def select_field_names(self, pgtable_name, geom): | |
| sel = "select column_name from information_schema.columns where table_name = '{pgtable_name}' AND udt_name <> 'geometry';".format(pgtable_name = pgtable_name) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| length = len(result) | |
| print length | |
| fields = geom | |
| for f in result: | |
| f = self.strip_list_entries(f) | |
| fields = f + ", " + fields | |
| return fields | |
| def select_srid(self, pgtable_name, host, username, password, db, geom): | |
| sel = "SELECT ST_SRID({geom}) AS srid FROM {pgtable_name} GROUP BY srid;".format(pgtable_name = pgtable_name, geom = geom) | |
| sel = conn.query(sel) | |
| result = sel.getresult() | |
| for s in result: | |
| srid = self.strip_list_entries(s) | |
| print "" | |
| print "The source table is stored with srid = ", srid | |
| return srid | |
| def set_srid(self, srid): | |
| print "Generating a 2nd shapefile with the correct SRID and calling it {pgtable_name}_{srid}.shp...".format(pgtable_name = pgtable_name, srid = srid) | |
| cmd = "ogr2ogr -overwrite -a_srs EPSG:{srid} {export_path}{pgtable_name}_{srid}.shp {export_path}{pgtable_name}.shp".format(pgtable_name = pgtable_name, export_path = export_path, srid = srid) | |
| process = os.system(cmd) | |
| def transform_srid(self, source_srid, pgtable_name, export_path): | |
| print "-------------------------" | |
| print "Current SRID = ", source_srid | |
| print "-------------------------" | |
| answer = ['y', 'n'] | |
| set_new_srid = raw_input("Would you like to set a different SRID (y or n)? ") | |
| while set_new_srid not in answer: | |
| set_new_srid = raw_input("Would you like to set a different SRID (y or n)? ") | |
| if set_new_srid == 'y': | |
| new_srid = raw_input("What SRID would you like to transform the data to? ") | |
| cmd = "ogr2ogr -overwrite -s_srs EPSG:{source_srid} -t_srs EPSG:{new_srid} {export_path}{pgtable_name}_{new_srid}.shp {export_path}{pgtable_name}_{source_srid}.shp".format(pgtable_name = pgtable_name, source_srid = source_srid, new_srid = new_srid, export_path = export_path) | |
| process = os.system(cmd) | |
| else: | |
| pass | |
| if __name__ == '__main__': | |
| #### Export paramaters | |
| export_path = "//Clinton/gis/DevType_2010_12/parcels_geom_2012/no_rule_9/" | |
| #### Connection paramaters for the PostGIS database. | |
| host = "YOUR_HOST" | |
| username = "YOUR_USERNAME" | |
| password = "YOUR_PASSWORD" | |
| db = "YOUR_DATABASE" | |
| conn = _pg.connect(dbname = db, host= host, port= 5432, user = username ,passwd= password) | |
| #### Running the script | |
| pg_toshape = pg_toshape() | |
| #### Example batch export table list | |
| table_list = ['adams_parcels_2012', 'arapahoe_parcels_2012', 'boulder_parcels_2012', 'broomfield_parcels_2012', 'clearcreek_parcels_2012', 'denver_parcels_2012', | |
| 'douglas_parcels_2012', 'elbert_parcels_2012', 'gilpin_parcels_2012', 'jefferson_parcels_2010', 'weld_parcels_2012'] | |
| for table in table_list: | |
| pgtable_name = table | |
| #### Set a geom field name for batch exports or the select_geom function to interactively choose which geom field to use | |
| geom = 'the_geom' | |
| select_fields = pg_toshape.select_field_names(pgtable_name, geom) | |
| pg_sql_select = "SELECT " + select_fields + " FROM " + pgtable_name | |
| pg_toshape.export_pg_table(export_path, pgtable_name, host, username, password, db, pg_sql_select) | |
| source_srid = pg_toshape.select_srid(pgtable_name, host, username, password, db, geom) | |
| pg_toshape.set_srid(source_srid) | |
| print "" | |
| print "Process complete." |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment