Paragon Corpoation PostGIS Spatial Database Engine OSGeo.org The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems       PostGreSQL Object Relational Database Management System
Home   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS  PostGIS Funding

Purpose of BostonGIS

BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.

If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.


GIS Tutorials on Opensource and OpenGIS technologies Tutorials
GIS Article comments Article and Tutorial Comments
Boston GIS BLog Rss FeedBoston GIS blog

PDF HTML All BostonGIS tutorials packaged together in an E-Book.


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
Glossary
GIS Blogs Around Boston
External GIS Blogs
External Papers Articles
GIS Quick Guides and References
OpenStreetMap and OpenLayers Tutorials
PostGIS, pgRouting, and PostgreSQL Tutorials
Part 1: Getting Started With PostGIS: An almost Idiot's Guide more ...
pgRouting: Loading OpenStreetMap with Osm2Po and route querying more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries more ...
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump more ...
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL more ...
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution more ...
Solving the Nearest Neighbor Problem in PostGIS more ...
PLR Part 2: PL/R and PostGIS more ...
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide more ...
Part 3: PostGIS Loading Data from Non-Spatial Sources more ...
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS more ...
Miscellaneous Tutorials/Cheatsheets/Examples
OGR2OGR Cheatsheet

Printer Friendly

OGR Tools

The OGR toolkit is a subset of GDAL project. GDAL is available on many operating systems including Linux, Unix, Mac, and Windows. You can find binaries at GDAL Binaries. It is also included as part of the QGIS Desktop. GDAL comes packaged with several command line tools. The ones we find most useful are:

  • ogrinfo - inspects a GIS datasource and spits out summary data or detailed information about the layers, kinds of geometries found in the file.
  • ogr2ogr - this is a command line tool that converts one Ogr defined data source to another Ogr data source. Ogr supports many data formats here is a subset of the ones we commonly use: ESRI Shapefile, MapInfo Tab file, CSV, DBF, GML, KML, Interlis, SQLite, GeoPackage,SpatiaLite, ODBC, ESRI GeoDatabase (MDB format), ESRI GDB database, PostGIS/PostgreSQL, MySQL.

If you want to use GDAL on a desktop, the easiest way to get started is to install QGIS Desktop which has installers available for Linux, Windows, and Mac. If you are on a server, then you should install GDAL via your OS Packager. Windows users who don't want QGIS (or want a non-install required set of binaries) can use one of the GISInternals binaries which includes GDAL and MapServer.

For the rest of this article, we will assume you are using GDAL via QGIS desktop

These 2 command line tools can be accessed via QGIS shell menu of your QGIS install. To start using these tools. The below are instructions for windows, but are very similar for other OS>
  1. InstallQGIS.
  2. Launch the OSGeo4W Shell (it will be called something different on other OS or you can just launch your regular command line on other OS) - in windows this is found under Start->Programs->QGIS ..
  3. From the shell - cd into your directory that has the data you want to convert
  4. Getting more Help

    If you want a comprehensive listing of options offered by ogr2ogr or ogrinfo, run the following at the FW Tools Shell.

    ogr2ogr --help
    ogrinfo --help
    
    

    Conversions from MapInfo to Other formats

    Conversion from MapInfo to ESRI Shape

    ogr2ogr -f "ESRI Shapefile" mydata.shp mydata.tab

    Conversion from MapInfo to PostGIS

    ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab

    Note: for the above, you can leave out the host if its localhost and user and password if you have your authentication set to trust.

    Importing as a different table name

    In the below example, we don't want OGR to create a table called mytable. We instead want to call the table something different like newtablename. To do so we use the nln option.

    ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename

    When OGR guesses wrong or fails to guess

    Sometimes OGR does not output the right projection, particularly with Units of Feet or data that has no projection info or the projection information can't be easily translated to your system. Sometimes OGR can't match the projection to one in your spatial_ref_sys table so creates a new entry in that table. In these cases you have to tell OGR what the output projection is. You do this with the -a_srs flag.

    ogr2ogr -f "PostgreSQL" -a_srs "EPSG:2249" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab

    In the above example I told OGR2OGR to assume the source/output projection is in Massachusetts Mainland US Ft. Note: All Spatial Ref Systems can be found in the spatial_ref_sys table of PostGIS or the Data/gcs.csv file of your FW Tools install.

    Conversions from PostGIS to Other formats

    Conversion from PostGIS to ESRI Shape

    The pgsql2shp and shp2pgsql are usually the best tools for converting back and forth between PostGIS and ESRI for 2 main reasons.

    • It has fewer idiosyncracies when converting data
    • It has a lot fewer dependencies so can fit on your floppy.



    If you really want to use Ogr2Ogr for this kind of conversion, below is the standard way to do it


    ogr2ogr -f "ESRI Shapefile" mydata.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" "mytable"

    Selecting specific fields, sets of data and Geometry

    Sometimes you have more than one geometry field in a table, and ESRI shape can only support one geometry field per shape. Also you may only want a subset of data. In these cases, you will need to select the geometry field to use. The most flexible way to do this is to use the -sql command which will take any sql statement.


    ogr2ogr -f "ESRI Shapefile" mydata.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" -sql "SELECT name, the_geom FROM neighborhoods"

    Example snippet converting from PostGIS to KML

    ogr2ogr -f "KML" neighborhoods.kml PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" -sql "select gid, name, the_geom from neighborhoods"
    The below outputs the neighborhood table to KML and sets the KML description field to name of neighborhood
    ogr2ogr -f "KML" neighborhoods.kml PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" neighborhoods -dsco NameField=name

    Exporting multiple tables from PostGIS using Ogr2Ogr

    One way in which ogr2ogr excels above using the pgsql2shp tool is that ogr2ogr can export multiple tables at once. This is pretty handy for sharing your postgis data with others who do not have a postgis database.

    The code below will export all your postgis tables out into a folder called mydatadump in ESRI shape (shp) format.

    ogr2ogr -f "ESRI Shapefile" mydatadump PG:"host=myhost user=myloginname dbname=mydbname password=mypassword"

    The code below will export all your postgis tables out into a folder called mydatadump in MapInfo .tab format.

    ogr2ogr -f "MapInfo File" mydatadump PG:"host=myhost user=myloginname dbname=mydbname password=mypassword"

    Now most of the time you probably only want to output a subset of your postgis tables rather than all your tables. This code exports only the neighborhoods and parcels tables to a folder called mydatadump in ESRI shapefile format

    ogr2ogr -f "ESRI Shapefile" mydatadump PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" neighborhood parcels

    Conversion from TIGER to other formats

    Topologically Integrated Geographic Encoding and Referencing system (TIGER) is the US Census Bureaus proprietary format for exporting US Census geographic and statistical data. Starting in 2007, they will be using ESRI Shapefile (SHP) as there official export format. So this section may be a bit obsolete for the upcoming versions.

    To get the files for your location - you can browse their archive at http://www.census.gov/geo/www/tiger/index.html

    Reading the meta data using ogrinfo


    ogrinfo TGR25025.RTI

    Conversion from Tiger to ESRI shape

    Give me all layers in TGR25025

    The tiger files contain a set of layers, so unlike the other outputs we have done, we will specify a folder to dump all the layers into

    ogr2ogr -f "ESRI Shapefile" masuffolk TGR25025.RTI

    Note: The above outputs all the tiger layers in the TGR25025 set into a folder called masuffolk that resides within our data folder that we have cded to.

    Just One Layer

    ogr2ogr -f "ESRI Shapefile" sufcomp.shp TGR25025.RT1 layer CompleteChain

    In the above, we are asking for just the CompleteChain layer and to output to a new file called sufcomp.shp. Note it will output shp and the corresponding shx, and prj files.

    Conversion from TIGER to MapInfo

    The conversion follows a similar path to ESRI Shape

    All Layers - Each Layer as a single file

    The below will create a folder masuf and output all the layers into that folder and give each a tab file extension


    ogr2ogr -f "MapInfo File" masuf TGR25025.RT1

    Single Layer - Single File

    ogr2ogr -f "MapInfo File" sufcomp.tab TGR25025.RT1 layer CompleteChain

    Conversion from Tiger to PostGIS

    ogr2ogr -update -append -f "PostGreSQL" PG:"host=myserver user=myusername dbname=mydbname password=mypassword" TGR25025.RT1 layer CompleteChain -nln masuf -a_srs "EPSG:4269"

    Note in the above we needed to put the -update -append option because OGR2OGR will try to create a folder if given a file with no extension, which translates to creating a new database.

    We also put in the -nln masuf to prevent OGR2OGR from creating the table name as CompleteChain

    Lastly we put in EPSG:4269 to tell OGR to just assume Tiger is in NAD 83 long lat. Without this it creates an entry in the spatial_ref_sys table which is equivalent to the already existing well known 4269.

    ESRI GeoDatabase (MDB format)

    FWTools at least for windows, has the ESRI Geodatabase driver (referred to as PGeo for Personal Geodatabase) baked into the binary. This makes it relatively simple to export data out of the Personal Geodatabase format into other formats such as PostgreSQL or ESRI Shape

    If you want a sample geo database to play with, try the MassGIS annotation personal geo database http://www.mass.gov/mgis/ftpgnm.htm

    See list of Feature Classes

    ogrinfo C:\GISData\Geonames.mdb
    INFO: Open of `c:\GISData\Geonames.mdb'
          using driver `PGeo' successful.
    1: GEONAMES_ANNO_PLACES
    2: GEONAMES_ANNO_HYDRO
    3: GEONAMES_ANNO_HYPSO
    

    Import Personal Geodatabase to PostGIS

    To import all feature classes and assign a particular spatial ref

    ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb password=somepassword port=5432" C:\GISData\Geonames.mdb -a_srs EPSG:26986

    Import 1 feature class and reproject and rename geometry column. This example brings in a feature class that is of projection NAD 83 MA meters and transforms to NAD 83 longlat and also renames the feature class ma_hydro

    ogr2ogr -f "PostgreSQL" PG:"host=localhost user=someuser dbname=somedb" C:\Data\Geonames.mdb GEONAMES_ANNO_HYDRO -a_srs EPSG: 26986 -t_srs EPSG:4269 -nln ma_hydro -lco GEOMETRY_NAME=the_geom_4269

    Importing from ESRI Shape to MySQL

    MySQL 4 and 5 support spatial objects to a limited extent. The functionality is not as rich as PostGIS, but for basic mapping work and limited spatial analysis, and if you already have MySQL installed, it may suit your needs just fine. Below is a snippet of code that will import a shape file called world_adm0.shp into a MySQL database and will rename it world. NOTE: that if you have a virgin MySQL database with no geometry_columns or spatial_ref_sys table, then those will automatically be created.

    ogr2ogr -f "MySQL" MYSQL:"mydb,host=myhost,user=mylogin,password=mypassword,port=3306" -nln "world" -a_srs "EPSG:4326" path/to/world_adm0.shp

    Using OGR to Import Non-spatial Data

    While OGR was designed primarily to transform data between different spatial datasources, it is a little known fact that it can be used as well for importing non-spatial datasources such as Dbase files and CSV files.

    Importing Dbase file into PostgreSQL using Ogr2Ogr

    In the below example, we will demonstrate importing a Dbase file into PostgreSQL using OGR2OGR

    ogr2ogr -f "PostgreSQL" PG:"host=myserver user=myusername dbname=mydbname password=mypassword" sometable.dbf -nln "sometable"

    download

    Post Comments About OGR2OGR Cheatsheet
SpatiaLite Tutorials
Boston External Map Examples
SQL Server 2008 Tutorials
UMN Mapserver Tutorials
General Commentary
Boston GIS      Copyright 2024      Paragon Corporation