PostGIS Tuning

January 14, 2011 Anita Graser

Besides many other interesting topics, Opengeo’s PostGIS tutorial discusses “Tuning PostgreSQL for Spatial”.

The following values are recommended for production environments:

shared_buffers: 75 % of database memory (500 MB)
work_mem: 16 MB
maintenance_work_mem: 128 MB
wal_buffers: 1 MB
checkpoint_segments: 6
random_page_cost: 2.0
seq_page_cost: 1.0

All of these configuration parameters can edited in the database configuration file, C:\Documents and Settings\%USER\.opengeo\pgdata\%USER. This is a regular text file and can be edited using Notepad or any other text editor. An easier way of editing this configuration is by using the built-in “Backend Configuration Editor”. In pgAdmin, go to File > Open postgresql.conf…. It will ask for the location of the file, and navigate to C:\Documents and Settings\%USER\.opengeo\pgdata\%USER.

The changes will not take effect until the server is restarted.

by underdark at 2:17 PM under postgis , postgresql , tuning (Comments)

Offline editing plugin for QGIS

January 2, 2011 Sourcepole

For data collection, it is a common situation to work with a laptop or a phone offline in the field. Upon returning to the network, the changes need to be synchronized with the master data source, e.g. a PostGIS database. If several persons are working simultaneously on the same datasets, it is difficult to merge the edits by hand, even if people don’t change the same features.

Therefore, Mathias Walker implemented an offline plugin for QGIS. This plugin automates the synchronisation by copying the content of a datasource (usually PostGIS or WFS-T) to a spatialite database and storing the offline edits to dedicated tables. After being connected to the network again, it is possible to apply the offline edits to the master dataset.

To give the plugin a try, unpack the sources, apply the patch ‘qgissvn.diff’ to a current svn version of QGIS. Then copy the offlineediting folder to $PREFIX/src/plugins and recompile QGIS.

The usage of the plugin is straightforward:

Open some vector layers, e.g. from a PostGIS or WFS-T datasource
Save the project
Press the ‘Convert to offline project’ button and select the layers to save. The content of the layers is saved to spatialite tables.
Edit the layers offline
After being connected again, upload the changes with the ‘Synchronize’ button

Screenshot

Presumably, the offline editing plugin will be part of the next QGIS version (1.6)

by admin at 9:32 PM under news , postgis , qgis , software , spatialite , wfs

A week in Tanzania

September 1, 2010 Linfiniti

I spent most of last week in Dar es Salaam, Tanzania. A lovely tropical country in the heart of Africa. I was there as part of a project I am working to create tools for Biodiversity Informatics practitioners. Of course the tools are based on Free Software:Quantum GIS and openModeller.

The attendees at the workshop were entertained by my talk about what FOSS is and why it is important, an introduction to QGIS slideshow (superbly presented by Marco Hugentobler), and ending with a tour of openModellerDesktop. We also did some live demonstrations of QGIS and openModeller, before going on to discuss details about how these tools can be used to support their Biodiversity Informatics workflows.

The meeting was funded by the Global Biodiversity Information Facility (GBIF) with Juan Bello as their representitive, and hosted by the Tanzanian Commission for Science and Technology (COSTECH).

In case you are unfamiliar with the aims of GBIF, they are facilitating the digitisation (or digitization for our american readers) of the worlds biodiversity records - herbarium records, museum collections and so on. COSTECH provides the local infrastructure and staff for the 'TanBif' node in Tanzania.

The meeting also included 'in-country' experts in the fields of GIS, Meteorology, Ecology, IT and so on. I think for all of the attendees, the concept of FOSS was a real eye-opener. African economies can't compare with those in Europe and the USA and the capital outlay for proprietary software that presents an irritation in the Western world is a major burden in the third world. So just knowing that they could dive in and use QGIS was a great revelation.

We finished our workshop a little early on the Friday so Marco and I offered to go along to the COSTECH offices and geo-enable their PostgreSQL species occurrence database and install QGIS on their desktop PC's running Windows XP. In the space of a couple of hours we were done - the major part of which was spent showing the TanBif staff members how to bring up the PostGIS layer in QGIS, perform simple queries and make maps. Having spent days in the past trying to get proprietary software like Oracle and Arc*** configured, optimised, licensed and generally usable, I was struck by just how easy and quick it is to get someone up and running with a robust enterprise ready PostGIS geospatial datastore and a user friendly Free Software desktop GIS like QGIS.

Thanks to the friendly Tanzanian folks for their hospitality - I look forward to my next visit! Here are some piccies from the trip...

Juan Bello telling us about the cool things you can do with a good Biodiversity Information repository.

The workshop attendees (Marco and Juan out of shot)

Marco showing Godfrey how to use QGIS to bring up their PostGIS Biodiversity dataset.

Godfrey proudly showing off his first map (made with QGIS)!

Marco killing a mosquito - he became something of an expert!

by Tim Sutton at 10:19 PM under postgis , postgres , qgis

Overpainting with Mapnik

August 21, 2010 Linfiniti

The problem

I've been having a little poke around with Mapnik today (awesome software!). One of the things on my todo list has been to sort out rendering issues with roads we have been having. Our last iteration described roads something like this:

A style...

<Style name="Freeway30th_style">
    <Rule>
        <LineSymbolizer>
            <CssParameter name="stroke">rgb(169,170,153)</CssParameter>
            <CssParameter name="stroke-width">12.26</CssParameter>
            <CssParameter name="stroke-linejoin">bevel</CssParameter>
            <CssParameter name="stroke-linecap">round</CssParameter>
            <CssParameter name="stroke-opacity">1</CssParameter>
        </LineSymbolizer>
        <LineSymbolizer>
            <CssParameter name="stroke">rgb(255,172,88)</CssParameter>
            <CssParameter name="stroke-width">12.16</CssParameter>
            <CssParameter name="stroke-linejoin">miter</CssParameter>
            <CssParameter name="stroke-linecap">round</CssParameter>
        </LineSymbolizer>
    </Rule>
</Style>

...and this layer definition...

<Layer name="Freeway30th" srs="+init=epsg:&srid;" maxzoom="39105.90277777778">
  <StyleName>Freeway30th_style</StyleName>
  <Datasource>
      <Parameter name="dbname">&dbname;</Parameter>
      <Parameter name="estimate_extent">0</Parameter>
      <Parameter name="extent">&extent;</Parameter>
      <Parameter name="geometry_field">&geometry_field;</Parameter>
      <Parameter name="host">&host;</Parameter>
      <Parameter name="password">&password;</Parameter>
      <Parameter name="port">&port;</Parameter>
      <Parameter name="srid">&srid;</Parameter>
      <Parameter name="table">(SELECT * FROM "l_roads" WHERE "type" = \
      'Freeway' ORDER BY LENGTH(&geometry_field;) DESC) as "l_roads"</Parameter>
      <Parameter name="type">&datasourcetype;</Parameter>
      <Parameter name="user">&password;</Parameter>
  </Datasource>
</Layer>

With the idea being to render freeways with a gray outline and orange center. Unfortunately, it doesnt produce good results:

The problem being those little line ends you see making gray splodges at the end of each segment.

The solution

Michael Migurski's blog discusses this issue a little in this article but doesnt directly explain how to achieve the desired effect. So here is what you do:

First the styles are split into two...

<Style name="Freeway30th_style-bottom">
   <Rule>
       <LineSymbolizer>
           <CssParameter name="stroke">rgb(169,170,153)</CssParameter>
           <CssParameter name="stroke-width">12.26</CssParameter>
           <CssParameter name="stroke-linejoin">bevel</CssParameter>
           <CssParameter name="stroke-linecap">round</CssParameter>
           <CssParameter name="stroke-opacity">1</CssParameter>
       </LineSymbolizer>
   </Rule>
 </Style>
 <Style name="Freeway30th_style-top">
   <Rule>
     <LineSymbolizer>
       <CssParameter name="stroke">rgb(255,172,88)</CssParameter>
       <CssParameter name="stroke-width">12.16</CssParameter>
       <CssParameter name="stroke-linejoin">miter</CssParameter>
       <CssParameter name="stroke-linecap">round</CssParameter>
     </LineSymbolizer>
   </Rule>
 </Style>

and then the layer is now rendered as two layers, the bottom layer first, then the top:

<Layer name="Freeway30th-bottom" srs="+init=epsg:&srid;" maxzoom="39105.90277777778">
    <StyleName>Freeway30th_style-bottom</StyleName>
    <Datasource>
        <Parameter name="dbname">&dbname;</Parameter>
        <Parameter name="estimate_extent">0</Parameter>
        <Parameter name="extent">&extent;</Parameter>
        <Parameter name="geometry_field">&geometry_field;</Parameter>
        <Parameter name="host">&host;</Parameter>
        <Parameter name="password">&password;</Parameter>
        <Parameter name="port">&port;</Parameter>
        <Parameter name="srid">&srid;</Parameter>
        <Parameter name="table">(SELECT * FROM "l_roads" WHERE "type" = \
        'Freeway' ORDER BY LENGTH(&geometry_field;) DESC) as "l_roads"</Parameter>
        <Parameter name="type">&datasourcetype;</Parameter>
        <Parameter name="user">&password;</Parameter>
    </Datasource>
</Layer>
<Layer name="Freeway30th-top" srs="+init=epsg:&srid;" maxzoom="39105.90277777778">
    <StyleName>Freeway30th_style-top</StyleName>
    <Datasource>
        <Parameter name="dbname">&dbname;</Parameter>
        <Parameter name="estimate_extent">0</Parameter>
        <Parameter name="extent">&extent;</Parameter>
        <Parameter name="geometry_field">&geometry_field;</Parameter>
        <Parameter name="host">&host;</Parameter>
        <Parameter name="password">&password;</Parameter>
        <Parameter name="port">&port;</Parameter>
        <Parameter name="srid">&srid;</Parameter>
        <Parameter name="table">(SELECT * FROM "l_roads" WHERE "type" = \
        'Freeway' ORDER BY LENGTH(&geometry_field;) DESC) as "l_roads"</Parameter>
        <Parameter name="type">&datasourcetype;</Parameter>
        <Parameter name="user">&password;</Parameter>
     </Datasource>
</Layer>

A much cleaner rendering!

Note

This approach consumes more cpu time and hits your database harder than the 'messier' approach shown first.

Also you can see in the example above, I have adopted Michaels approach of rendering long lines first.

Have fun with your mapnik maps!

by Tim Sutton at 10:00 PM under postgis , postgres

Introduction to PostGIS

August 17, 2010 Linfiniti

Horst and I are spending the week up in Johannesburg at the Satellite Applications Center in Hartebeeshoek. We are doing yet another week long training course (I hope I'm not working the poor guy too hard :-P ). This time we are doing:

- Two days QGIS (with a little GRASS)
- One day PostGIS
- Two days geospatial programming with Bash, Python and QGIS

Tomorrow we start with the PostGIS component. Horst and I have been compiling some course notes for the PostGIS module which we are making available to the world as per usual. The pdf still has some rendering issues - we are aware of that. The document tries to walk the reader through the basics of using SQL and then some basic activities with PostGIS and working with geometries.

I hope some of you out there find it useful - let us know if you do! Also if you have any improvements to make, we'd love to hear from you.

Here is a quick pic or two from the course:

by Tim Sutton at 10:50 PM under foss , postgis , postgres , qgis

QGIS Planet

PostGIS Tuning

Offline editing plugin for QGIS

A week in Tanzania

Overpainting with Mapnik

The problem

The solution

Note

Introduction to PostGIS

Sustaining Members

Blog List

Tags

QGIS Planet

PostGIS Tuning

Offline editing plugin for QGIS

A week in Tanzania

Overpainting with Mapnik

The problem

The solution

Note

Introduction to PostGIS