Slides from our presentations at FOSS4G 2014 in Portland/Oregon:

• From Nottingham to PDX: QGIS 2014 roundup
• State of QGIS Server
• Easy ETL with OGR

@PirminKalberer

In one week, the 2014 FOSS4G Conference will start in Portland/Oregon. Sourcepole supports this major event as a bronze sponsor.

Our conference contributions:

Workshop presented by Horst Düster (@moazagotl)

• Tuesday afternoon: QGIS Plugin Development with PyQt4 and PyQGIS

Presentations by Pirmin Kalberer (@PirminKalberer)

• Thursday, Session 2, Track 7, 13:00 - 13:25: State of QGIS Server
• Thursday, Session 2, Track 7, 13:30 - 13:55: From Nottingham to PDX: QGIS 2014 roundup
• Thursday, Session 3, Track 6, 16:25 - 13:25: Easy ETL with OGR

Meet Pirmin and Horst at Sourcepole’s exhibition booth and have a look at our latest products.

We’re looking forward to meet you in Portland next week!

Follow @Sourcepole for selected QGIS news and other Open Source Geospatial related infos.

The post GDAL/OGR 1.11.0 released appeared first on GFOSS Blog | GRASS GIS Courses.

This is an update to my previous post “WFS to PostGIS in 3 Steps”. Thanks to Even Rouault’s comments and improvements to GDAL, it is now possible to load Latin1-encoded WFS (like the one by data.wien.gv.at) into PostGIS in just one simple step.

To use the following instructions, you’ll have to get the latest GDAL (release-1600-gdal-mapserver.zip)

You only need to run SDKShell.bat to set up the environment and ogr2ogr is ready for action:

C:\Users\Anita>cd C:\release-1600-gdal-mapserver
C:\release-1600-gdal-mapserver>SDKShell.bat
C:\release-1600-gdal-mapserver>ogr2ogr -overwrite -f PostgreSQL PG:"user=myuser password=mypassword dbname=wien_ogd" "WFS:http://data.wien.gv.at/daten/geoserver/ows?service=WFS&request=GetFeature&version=1.1.0&typeName=ogdwien:BEZIRKSGRENZEOGD&srsName=EPSG:4326"

Thanks everyone for your comments and help!

This is just a quick “note to self” on some interesting information I picked up from the QGIS mailing list today. Kudos to David J. Bakeman for sharing this:

If both the input and the output arguments to ogr2ogr are directories then it will clip all of the shapes in the source directory and write them to the output directory.

So: ogr2ogr -clipsrc mask.shp output source

Shapefiles are the default so you don’t even need the -f “ESRI Shapefile”.

The following Shell script performs a conversion and reprojection of a folder full of Shapefiles to CSVs:

for f in `ls *.shp`; do
ogr2ogr -s_srs EPSG:4326 -t_srs EPSG:3035 -f CSV $f.csv $f -lco GEOMETRY=AS_XYZ
done

Thanks OGR!

Good news!

Support for MS SQL Server 2008 in QGIS is coming soon.   A native QGIS provider for MS SQL Server is currently being worked on to make using, managing, and editing SQL Server data in QGIS just as easy as PostGIS.

The work is being sponsored by the Australian company Digital Mapping Solutions. So a very big thanks to them for this great feature!

There is no ETA on when it will be added to the main QGIS build, but the provider is currently in testing stage and hopefully will be in there soon.

So if you have been itching to try SQL Server data in QGIS, hang in there as a good solution is just around the corner.

P.S The other blog posts on this topic I used ogr, this method will still work fine after the native provider is added, however the native driver will add a nicer interface including integration into the QBrowser, better optimization for the QGIS code, and hopefully same feel as the PostGIS experience.

I tell you. It always amazes me how much cool stuff you can do with great open source GIS software these days. GDAL is one of those great open source projects that I have just found a great use for (apart from just opening every raster type under the sun in QGIS).

GDAL has the ability to generate contours from a DEM, something that I have always wanted to try for my town but have never been able due to lack of a good DEMs.

Recently we purchased a set of DEMs that cover a large area as part of a study. Each DEM uses a grid size of 1mx1m. Prefect for generating contours.

Using the GdalTools QGIS plugin.

First make sure that you have the latest version of the GdalTools plugin installed (GdalTools should be installed by default with QGIS. If it’s not, search “Gdal” in the plugin installer). Enable the plugin once it’s installed.

Load the DEM into QGIS using the Load Raster icon.

DEM loaded in QGIS

Now head up to the menu Raster->Extraction->Contour

Raster menu in QGIS

Select the settings that you need. For this DEM I am going to generate 250mm contours.

Contour dialog.

Take note of the text area at the bottom of the dialog as that is the exact command sent to GDAL in order to generate the contours. If you take a copy of that you can run it on the command line for batch processing later.

Hit ok.

250mm contours from the DEM

BAM! :)

Using the command line/shell.

Using QGIS for a one off DEM is fine and dandy but what if you have 3000 DEMs that you need to process. To hell with doing that by hand!

Remember the contour tool in QGIS told us the exact command line args to use, so creating a shell script for automating the process is pretty easy.

for f in *.asc
do
  echo "Processing $f"
 gdal_contour -a ELEV -i 0.25 $f $f-250mm.shp
done

The above code will loop though the current directory and process all the DEMs generating 250mm contours for each one. It saves each new contour file as {filename}-250mm.shp. You will need to change *.asc to whatever format your DEM is in.

Copy the above code into a file somewhere and call it generate_contours.sh. This can then be called from the command line using

sh generate_contours.sh

Running sh on Windows

If you’re a windows user you will need to run OsGeo4W Shell in order to use sh.

Loading OsGeo4w shell.

Once the shell is loaded you can just call:

sh generate_contours.sh

Output from running generate_contours.sh

Final remarks

I ran the above sh script on a folder with about 2500 DEMs and it took around 4 hours to complete the whole folder. Of course performance will vary but it seems pretty quick considering.

Once again the possibly to use open source GIS tools to get my work done is amazing.  No expensive software here.

So far I’m not aware of any line smoothing/generalizing abilities using GDAL/OGR.  Although you can import the contours into GRASS and use that: http://grass.osgeo.org/wiki/V.generalize_tutorial

You can also generate the contours using GDAL via Python but that is a topic for another day.

Turns out the last blog post I did on this subject contained a few errors, mainly that QGIS wouldn’t render the layer when you opened it.

The answer is so obvious it’s almost embarrassing :)

In order to open and display a SQL Server 2008 layer in QGIS correctly, via OGR, you must have a geometry_columns table in your database with the name, geometry type and srid of the layer. That’s it! Oh look, it was even right in front of me in the OGR code for the mssqlspatial driver.

int OGRMSSQLSpatialTableLayer::FetchSRSId()
{
    CPLODBCStatement oStatement = CPLODBCStatement( poDS->GetSession() );
    oStatement.Appendf( "select srid from geometry_columns "
                    "where f_table_schema = '%s' and f_table_name = '%s'",
                    pszSchemaName, pszTableName );

    if( oStatement.ExecuteSQL() && oStatement.Fetch() )
    {
        if ( oStatement.GetColData( 0 ) )
            nSRSId = atoi( oStatement.GetColData( 0 ) );
    }

    return nSRSId;
}

So the process to open a MS SQL 2008 spatial layer in OGR is as follows.

There are two main tables which tell OGR how to read a layers projection:

• geometry_columns
• spatial_ref_sys

geometry_columns contains the table name and the key for the table spatial_ref_sys which contains the projection string. The projection string is the info that QGIS needs in order to correctly render a layer.

The easiest way to get the correct tables is to let OGR handle it for you via ogr2ogr, then just adding any other tables you may have already in your database to the geometry_columns table.

So to get ogr2ogr to create the right tables for you it’s as simple as running the following command from inside the OSGeo4W shell, changing the connection string part of course:

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=geodb;trusted_connection=yes" "rivers.tab"

(sample taken from http://www.gdal.org/ogr/drv_mssqlspatial.html)

Uploading even just one table this way will create both tables and fill in the needed info.
The geometry_columns table:

The spatial_ref_sys table:

So if you have already existing tables in your MS SQL 2008 database that were loaded, via say MapInfo’s EasyLoader, you would just upload one table via ogr2ogr to create the two tables needed by QGIS(using OGR) and then add the other tables to the geometry_columns table. If they are all in the same projection than you are in luck as you will only need to upload one in order to get the right strings in the spatial_ref_sys table, if not just upload a small sample for each projection.

Then you can open the table in QGIS using:

uri = "MSSQL:server={serverName};database={databaseName};tables={tableName};trusted_connection=yes"
qgis.utils.iface.addVectorLayer(uri,'{yourLayerNameHere}','ogr')

Tip: In order to test you have correctly set the table in geometry_columns you can run another ogr tool ogrinfo:

ogrinfo -al "MSSQL:server=localhost;database={your database};tables={your table}" -fid 1

If you see a value in Layer SRS WKT: then chances are it’s set right and QGIS should be able to render it, however if you see: Layer SRS WKT:(unknown) Than chances are QGIS will not render it correctly.

Hopefully this help people use MS SQL 2008 Spatial with QGIS, a important step I think in the world of using QGIS on Windows (especially when you don’t have the freedom to run PostGIS:) ).

I might even do a video tutorial when I get some free time after my exams and my wedding.

Note:The following post requires gdal 1.8

A lot of times we need to send/use 3D dxf files, we used to use FME however FME is not free or cheap. So I went looking for a free solution.

If you have gdal 1.8 it’s just one simple command line run using, what is becoming my favorite GIS tool, ogr2ogr.

All you have to do is run:

ogr2ogr -f "DXF" {outFile} {inFile} -zfield {ColumnWithZValue}

So in my case I ran:

ogr2ogr -f "DXF" C:\Temp\contourswarwick.dxf C:\Temp\Contours.TAB -zfield Height

I haven’t fully tested it but {outfile} can be any file ogr supports.

and the output:

Top view of contour layer

The side view of the above image.

In the words of, the not so great, Charlie Sheen. WINNING!

Happy mapping :D

If you load .csv files through “Add vector layer”, all columns are interpreted as strings. That’s most likely not what you want, but it’s OGR’s default behaviour:

The OGR CSV driver returns all attribute columns with a type of string if no field type information file (with .csvt extension) is available.

Let’s create a .csvt file then!

The .csvt file has to have the same name as the .csv file it describes. (Same concept used in shapefiles.) It enables definition of the following data types: Integer, Real, String, Date (YYYY-MM-DD), Time (HH:MM:SS+nn) and DateTime (YYYY-MM-DD HH:MM:SS+nn).

A .csvt file contains only one line and the types for each column have to be quoted and comma separated, e.g.

"Integer","Real","String"

You can even specify width and precision of each column, e.g.

"Integer(6)","Real(5.5)","String(22)"

Read more at: www.gdal.org/ogr/drv_csv.html

Suppose you have a DBF (.dbf) file containing X and Y values that you want to import and save as a spatial layer.

QGIS doesn’t support direct import of a DBF file as a map layer, however, we can use some command line magic to convert it to a CSV file and then use the Delimited Text plugin to get the job done.

Your DBF file should have an id for each record and fields containing X and Y values. If it has additional fields that should be OK as well.

First convert the DBF to a comma delimited file using ogr2ogr:
ogr2ogr -f CSV my_csv my_data.dbf

If you don’t have ogr2ogr see http://www.gdal.org/index.html.

This will create my_data.csv in the my_csv directory. You are now ready to bring it into QGIS.

Here are the steps to import the CSV:

1. Start QGIS
2. If not already enabled, use the plugin manager to enable the Delimited Text plugin
3. Click on the Delimited Text icon in the Plugin toolbar or choose it from the Plugins menu
4. Browse to the location of your CSV file
5. Enter a name for the layer
6. Under Selected delimiters, check Comma
7. If your X and Y fields aren’t automatically determined, set them using the drop-down boxes
8. The sample text should show how the file is being parsed—if it looks right click OK, otherwise adjust the settings
9. The layer is added to QGIS

At this point the layer behaves pretty much like any other QGIS layer. To save it as a shapefile, right click on its name in the legend and choose Save as…

The 1.8.0 release is a major new feature release with the following highlights:

• New GDAL drivers: GTX, HF2, JPEGLS, JP2OpenJPEG, JPIPKAK, KMLSUPEROVERLAY, LOS/LAS, MG4Lidar, NTv2, OZI, PDF, RASDAMAN, XYZ
• New OGR drivers: AeronavFAA, ArcObjects , GPSBabel, HTF, LIBKML, MSSQLSpatial, NAS, OpenAir, PDS, PGDump, SOSI, SUA, WFS
• Significantly improved OGR drivers: DXF, GML
• New implemented RFCs
• New utility: gdallocationinfo

More complete information on the new features and fixes can be found in the GDAL/OGR 1.8.0 Release News.