Ubuntu PostGresql Installhttps://help.ubuntu.com/community/PostgreSQL NOTE: for the python PostGreSQL module you also need: sudo apt-get install python-dev Ubuntu EWN2 Create POSTGIS Databasecreatedb -U postgres -W -h localhost -E UTF8 -T template0 gis_test createlang -U postgres -W -h localhost plpgsql gis_test psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/postgis.sql psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql psql -U postgres -W -h localhost -d gis_test -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql OSX Create Spatial DatabaseTo setup a database (create one using your favorite tool) for PostGIS use, in a Terminal: sudo su - postgres -c '/usr/local/pgsql/bin/createlang plpgsql [dbname]' Where [dbname] is the name of the database. If you get an error about some role other than postgres not existing, add " -U postgres" after the dbname, before the closing quote. Then: sudo su - postgres -c '/usr/local/pgsql/bin/psql -d [dbname] -f /usr/local/pgsql/share/contrib/postgis-1.5/postgis.sql' sudo su - postgres -c '/usr/local/pgsql/bin/psql -d [dbname] -f /usr/local/pgsql/share/contrib/postgis-1.5/spatial_ref_sys.sql' NOTE: createlang and much of the SQL in lwpostgis.sql require a role with Postgres superuser permissions, and ownership of a database is not enough. So it's best to use the 'su - postgres' as above instead of trying to substitute another role. Create Spatial Tablehttp://postgis.refractions.net/docs/ch04.html#Create_Spatial_Table 4326 - WGS 84 Long Lat CREATE SEQUENCE points_id_seq; CREATE TABLE points ( id INTEGER PRIMARY KEY DEFAULT NEXTVAL('points_id_seq') ); SELECT AddGeometryColumn('points', 'location', 4326, 'POINT', 2); CREATE INDEX points_location_idx ON points USING GIST ( location ); Import a Shapefileshp2pgsql -c -s 4269 -D -i -I HU10/WBD_10points.shp public.hu10_pa | psql -U postgres -W -h localhost -d gis_test or you can use the Spit plugin in QGIS Test for Point in Polygonselect * from hu10_pa where ST_CONTAINS( the_geom, ST_GeomFromText('SRID=4269;POINT(-77.44 40.78)') ); Python PostGreSQL http://initd.org/psycopg/ OSX: Download build and install - works as advertised Ubuntu: sudo apt-get install python-dev
sudo apt-get install libpq-dev sudo easy_install psycopg2 Translate a ShapefileMove a shapefile from one location to another - e.g. move an outline of washington DC to overlay on another part of the world
Change the SRID on the table to the new SRID select UpdateGeometrySRID ('public', '<TABLE_NAME>', 'the_geom', <SRID>) Translate the geometry to the new centroid For a single polygon you can just do this update "public"."<Table name>" set the_geom = ST_Translate (the_geom, <New Center X> - ST_X(ST_CENTROID(the_geom)), <New Center Y> - ST_Y(ST_CENTROID (the_geom))) If you have multiple polygons then you need to find the centroid of one of the features in the set that will have it's new centroid at the desired location and do this: update "public"."<Table name>" set the_geom = ST_Translate (the_geom, <New Center X> - <Old Center X>, <New Center Y> - <Old Center Y>)
Migrate from MySQL To PostGreSQLUse this tool to migrate an entire database from MySQL to Postgreshttps://bitbucket.org/iceone/mysql2pgsql/ Create a new database in MySql and copy in everything you want to move Create a new empty database in Postgres Then run this tool to move everything |
Tools and Utilities >