PostGIS (pronounced Post-jis) is an open source software that adds spatial analysis capabilities to PostgreSQL, a leading open source object-relational database system. PostGIS support has been available with GeoMedia since 2011 (product version 6.1 or newer). PostGIS data server provides both read and write capabilities within GeoMedia thus enabling end users to leverage the whole suite of GeoMedia functionalities for performing analysis on a PostGIS database. In this post that acts as a primer, we explain the details of using PostGIS with GeoMedia along with few illustrations.
As prerequisites you need to have GeoMedia with PostGIS installed and PostgreSQL with PostGIS add-on installed. Here are the steps in brief:
Installing PostGIS GDO on GeoMedia
- Download the latest copy of PostGIS data server from Codeplex.
- Extract the downloaded ZIP file to a folder of your choice and register GMPsgCtl.ocx and PostGISGDO.dll using regsvr32.exe, a Windows utility. Make sure you run the command prompt as Administrator.
- Once you perform the above two steps, PostGIS gets listed in the New Connection dialog.
Setting up PostgreSQL – PostGIS
- Download and install PostgreSQL from EnterpriseDB
- Installing PostGIS – there are two ways of doing this
- Using StackBuilder Utility that comes with PostgreSQL: Though this is the easiest, the StackBuilder might not give you the latest version of PostGIS add-on. Hence use the next recommended option
- Download latest PostGIS version from OSGeo, now it is version 2.1.3-1 and perform the installation
Creating your first PostGIS Database
Invoke pgAdmin III from the Windows start menu and connect to the default PostgreSQL Server with the password that you created during installation.
Creating a new database is a simple process:
- Right click on Databases to bring up a New Database dialog
- Provide a name for your new database and specify the owner as postgres.
- On the Definition tab choose template1 as the template. The SQL code for database creation is auto-generated and can be observed in SQL tab.
- Click OK to create the new database.
- Once the database is created it gets added to the Databases list. Double click on it to connect. Note that this would be a plain PostgreSQL database with no spatial metadata.
- Click on SQL on the pgAdmin III toolbar that brings up the query window where you can key in and execute SQL statements. To enable spatial features on the database, execute the following statement:
CREATE EXTENSION postgis;
To use this spatially enabled database with GeoMedia, certain metadata tables need to be created. This part is done using PsgDBUtils delivered with GeoMedia’s PostGIS data server. Head over to the folder where you extracted PostGISGDO and bring up PsgDBUtils. Make a connection to the database created in the above steps.
After successfully connecting to the database, create simple metadata tables followed by INGR metadata tables. Now you are all set to use PostGIS database on GeoMedia.
Bringing data to PostGIS database
Now that we have the entire setup right from the database to GeoMedia environment, how do we bring data into the PostGIS database? This is fairly easy and you can use GeoMedia’s Output to Feature Classes (OTFC) to do this. The source database could be anything – Access, SQL Server, Oracle or any GeoMedia-supported format and the target would be PostGIS. Therefore, make two database connections – one to your source database and another to PostGIS database. Invoke OTFC and select all the necessary features in the source connection. Ensure that the target is PostGIS database, and click OK .
If you want to transform the coordinate system, you can use the Target Coordinate System option in the Advanced tab. Existing coordinate system can be assigned or a new coordinate system can be loaded (in .csf or .prj) format.
Once the OTFC operation is complete, the data displays in the map window. We urge you to make use of GeoMedia’s feature caching because it can go a long way in making data display and queries compute faster.
- PostgreSQL allows any database to be used as a template to create a new database. This feature is really handy and you can choose to use the database created in the above steps (prior to OTFC operations) as a template to create newer databases compatible with GeoMedia. This can save you from creating various types of metadata every time you need a new database.
- This template feature can also be used when multiple copies of the same database needs to be shared with multiple users. Database replication takes few seconds to minutes based on the data size.
- We recommend database administrators handling multiple user environments to create users with lesser privileges instead of using postgres superuser. More about this in a later post.
About the Author
Chethan is a research oriented GIS Professional who is passionate about geospatial industry workflows and their applications in the real world. He has been working for GeoMedia’s Quality Assurance since 2011. Apart from GIS his interests are social media and telecommunications.