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.

How to create Point Layers from x/y Data on the fly with PostGIS and QGIS

By underdark

2010-10-19

GIS, PostGIS, QGIS

2 Comments

Sometimes, we just want to visualize the contents of a PostGIS table containing some x/y data but no actual geometries in QGIS. But there the problems arise: We don’t have the right to add a geometry column, the table doesn’t have a suitable ID or OIDs (QGIS demands a unique integer ID) and we can’t or don’t want to mess with the database anyway. Loading the table with “Add PostGIS Layer” will result in a non-spatial layer (or fail if you use an older QGIS versions).

RT Sql Layer Plugin to the rescue!

I presented this plugin in a previous post. It allows you to execute any SQL SELECT statement, even really complex ones. Luckily, this time we don’t need anything fancy, only the two functions row_number() and makepoint():

select  
  row_number() over (order by col1)::int AS my_id,
  col1, 
  col2,
  x, y, 
  makepoint(x,y) as the_geom
from my_table

Visualizing PostGIS Queries in QGIS using RT Sql Layer Plugin

By underdark

2010-10-16

GIS, PostGIS, QGIS

1 Comment

Have you ever wondered how to comfortable visualize PostGIS queries? Meet “RT Sql Layer” a powerful and comfortable QGIS plugin that allows building and visualizing queries on your PostGIS data.

RT Sql Layer comes with a graphic query builder:

RT Sql Layer Query Builder dialog

It allows saving/loading of queries to speed up your work flow.

The query results will be loaded as a new layer:

Loaded query layer

RT Sql Layer is available through Faunalia Plugin Repository.

For another great example on what can be achieved with this plugin, read Carson Farmer’s post on “pgRouting, OpenStreetMap, and QGIS” where he describes how to build your own routing database and visualize routing results in QGIS with RT Sql Layer.

More on RT Sql Layer: How to create Point Layers from x/y Data on the fly with PostGIS and QGIS

PostGIS 1.5 Cheatsheet and Comparison

By underdark

2010-07-21

GIS, PostGIS

BostonGIS has compiled a great cheatsheet for PostGIS 1.5. You’ll find both a web version and a PDF version here: http://www.bostongis.com/postgis_quickguide.bqg.

Additionally, they have published an extensive comparison of SQL Server 2008 R2, Oracle 11G R2, and PostgreSQL/PostGIS 1.5 spatial features: http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008r2_oracle11gr2_postgis15_compare.