Spatial Mounty

Postgresql PostGIS How to set your search_path

2011-12-09T08:45:00.001+01:00

The problem:
You want to have all your spatial data in a schema other than "public" in your Postgresql PostGIS database and don't want to have to always specify the schema.tablename when creating new tables.

The solution:

Close the connection to the database you want to change
run the sql query

ALTER DATABASE DatabaseName SET search_path = '$user','geodata', 'public';

in this example the schema 'geodata' is the primary schema where I want to store all my spatial tables. The order in which Postgresql "searches" where to create the table is then defined in the same order as you have listed in your SET search_path statement.

If you just want to change the search_path for a current session you can use:
SET search_path = '$user','geodata', 'public';

now you can merrily go and create a new table and it will automatically be created in your 'geodata' schema.

if you already have the tables in the 'public' schema and want to move them to your new shiny 'geodata' schema it is really easy to do:

ALTER TABLE name SET SCHEMA new_schema

now if you do this don't forge that your geometry_columns won't automatically reflect your schema changes and must be updated by using

SELECT Populate_Geometry_Columns('geodata.myspatial_table'::regclass);

cheers
Michael

How to upgrade Geoserver 2.1.1 to 2.1.2

2011-11-23T12:42:00.001+01:00

From 2.1.1 to 2.1.2, Ubuntu 10.04 Server, Tomcat6

Note: all those commands need to be done as root (or alternatively, run sudo before the command)

Stop Apache tomcat:
```
/etc/init.d/tomcat stop 
```

Backup the your directories, this could be different from your own installation: /var/geoserver-data, /opt/tomcat/webapps/geoserver/

tar -cf geoserver-data-nov-17.tar /var/geoserver-data # takes quite long on current geoserver installation (orthofotos, ...)
gzip geoserver-data-nov-17.tar
tar -cf geoserver-app-nov-17.tar /opt/tomcat/webapps/geoserver
gzip geoserver-app-nov-17.tar

Additionally, backup web.xml file from WEB-INF directory:

cp /opt/tomcat/webapps/geoserver/WEB-INF/web.xml web-backup-nov17.xml

Download latest (in this case: 2.1.2) geoserver version (war archive)

wget http://downloads.sourceforge.net/geoserver/geoserver-2.1.2-war.zip

Unzip it
```
unzip geoserver-2.1.2-war.zip
```

Copy it to the tomcat webapps directory

cp geoserver.war /opt/tomcat/webapps/geoserver.war

Delete old geoserver installation
```
rm -rf /opt/tomcat/webapps/geoserver/
```
Start Tomcat again
```
/etc/init.d/tomcat start
```
Wait... (this can take a while, monitor CPU usage via htop or other tools)
Check /opt/tomcat/webapps/ directory, you should see a subdirectory geoserver very soon
```
ls -la /opt/tomcat/webapps/
```
Verify that new geoserver version is running by trying to access http://gs1.gomogi.com:8080/geoserver/
We're not done yet, let's put the old configuration file in place again:
```
cp web-backup-nov17.xml /opt/tomcat/webapps/geoserver/WEB-INF/web.xml
```
Restart tomcat once again and all should be good in update land.
```
/etc/init.d/tomcat restart
```

Why use OpenERP in a small business

2011-07-10T15:05:00.002+02:00

Well it did take more than 1/2 hour to get OpenERP up and running last night. To be precise it took exactly 4,75 hours. I must say that I'm impressed with the modules, features and useability. Now I need at least 1 week to play/customize it and we are off to the races.

Why did I install an Enterprise Resource Planning (ERP) web application for my small business? Well my company GOMOGI like all other small businesses around the world need to do all the same stuff like big companies on a smaller scale like:

manage and engage customers (Customer Relationship Management (CRM))
marketing our products
write bills/invoices
manage projects with AGILE SCRUM methodology
Getting Things Done way to organize my busy multitasking days, weeks, months and years

This keeps all our STUFF in one place and accessible online at anytime no matter where I am (onsite with a customer, traveling, anywhere). No backing up issues since our hosting provider takes care of that for us.

OpenERP with its modular design allowed me to install only the modules I need now to keep me running with the ability to expand and add new features easily when I need them as we grow as a company.

I also have a biased view since I love technology and OpenERP in the background uses my favourite things Python as its development language and Postgresql as its database, both things I use for my spatial work load. PostGIS the spatial extension of Postgresql and Python the scripting language of ESRI products.

What Python version do my geospatial apps/libraries use?

2011-06-08T12:33:00.001+02:00

Setting up a new PC and I was interested to know what version of Python is being installed and used by various GIS applications and libraries.

The problem many new users have is finding what version to install with what software. So here is the run down of what is using what Python Version.

FWTools 2.4.7 (Windows 32bit) uses Python 2.3.4
ESRI ArcGIS 10 uses Python 2.6.5 on installation
ESRI ArcGIS 9.3 uses Python 2.5.1
OSGeo4w uses Python 2.5.2
Google AppEngine as of 08.06.2011 uses Python 2.5.2 Roadmap has 2.7 in sight
Ubuntu Server 10.04 has Python 2.6.5 as a standard instal
Django supports any Python version from 2.4 to 2.7

A quick note
GdalOgrInPython GDAL this link for details

Dependencies

Python 2.X (and 3.X since GDAL 1.7.0). Python 2 versions known to work range from 2.3 to 2.7.
libgdal (1.5.0 or greater) and header files (gdal-devel)
numpy (1.0.0 or greater) and header files (numpy-devel) (not explicitly required, but many examples and utilities will not work without it). Note that at the time of writing, there's not yet any Python 3 port of NumPy.

Michael

Quick Start Guide to OpenStreetMap (OSM)

2011-03-21T21:51:00.004+01:00

As I find myself teaching more and more people to get started with the OpenStreetMap (OSM) project the first "Newbie" steps are just still to complicated for non - tech users who want to add a simple house or Point of Interest based off Bing Images aka not using GPS.

It is really quite straight forward. Lets take for example I want to add a parking lot as POINT so the parking lot symbol will show when rendered with the standard mapnik renderer aka the main map you see when first starting to us OSM maps.

Register for a free account here you need this to do editing.
Zoom to a small area where you want to add some data using the map interface. If you can see your entire city that is to much and is not allowed, try zooming in on a smaller area it will be faster too.
Select the online editor by just holding your mouse over the Edit Tab and select the online editor for this example I'm using Potlatch2 (You need FLASH installed) .
Now double click one time on the sport where you want the parking symbol to show up. Then select the type of object using the drop down list and select "Car Parking".
Add a Name like "guest parking" if you don't know it you can ignore it.
Add the number of spaces if you know how many there are if not just ignore it.
Click Save in the upper right hand corner and add a simple comment like "added parking spot"
DONE

I managed to keep it under 10 Steps. I hope this helps those of you just starting out and wishing for quick bang introduction to OSM.

Please feel free to let me know if this works out for you.

Free Geodata

2011-01-26T11:14:00.003+01:00

Whenever you have a spatial project it usually begins with some data. Now what if you don't have any data? Well here come some sources to your rescue. EDEN Data Archive has a huge list of free data sources

OpenStreetMap

International

Imagery

Arizona Regional Image Archive (AVHRR, AVIRIS, MSS, TM, SPOT for six southwestern states including California)
Canadian Geospatial Data Infrastructure
Earth Explorer (Landsat, AVHRR and aerial photos)
Earth Observing System Data Gateway (Atmosphere, land, ocean, solar and cryosphere imagery)
Geoscience Australia (Free Landsat 7, MODIS & AVHRR data)
Global Land Cover Facility (ASTER, Landsat, MODIS, AVHRR)
Global Visualization Viewer (Free ASTER, MODIS and Landsat Imagery)
Landsat.org (Huge collection of Landsat imagery; not all free)
NASA Image Server (Landsat 4,5,7)
Terraserver (Quickie air photo images from Microsoft)
University of Nevada Landsat TM Archive (State of Nevada)
USGS EROS Data Center (Many sensors with links to data access)

Land Cover Data

The U.S. Environmental Protection Agency's Land Cover Data Directory
Global Land Cover 2000
Global Land Cover Facility
GAP analysis program (land cover data by U.S. State)
USGS Geographic Data Download (older LULC data)
National Land Cover Dataset from USGS (seamless and the most current for the US)

NLCD Class Definitions in html or EXCEL .dbf format

Python and related spatial resources

2011-01-07T11:02:00.014+01:00

To start the first post of the new year here are some thoughts and resources to anyone just starting out in GIS who has NO computer science training at all and wants quick access to great python spatial resources in print and on the web. James Fee over at his blog post has also some thoughts on python for 2011.

Why Python? well ESRI the "world number one" in GIS uses it as its scripting language of choice and VBA will eventually die. Plus the open source GIS scene is also in love with python and of course I just like it.

Python books I invested some money into when I started out. The rest of the python resources are of course all coming from web sources, blog posts and from reading a TON of documentation.

First off here are two good free books to get you started:

These ones cost some money if you still read hard copies ( I do )

Second on the list are bloggers who use Python:

Sean Gillies New York, US
Armin Ronacher Graz, Austria
Paolo Corti
GeoSpatialPython
GIS and Python Lab a web blog with reference to GISPYTHONLAB

Other spatial Python sources or projects on the web:

GeoDjango a cool fast web framework based on Django
Mapfish a mapping framework using ~~Pylons~~ Pyramid, Openlayers, Geoext, ExtJS
GISPythonLab open source GIS tools for Python programmers
Geoscript adds spatial capabilities to dynamic scripting languages
Mapnik a web mapping library for making nice maps
Pysal v1 cross-platform library of spatial analysis functions written in Python
Python Package index listing available GIS packages

Non Spatial Python web resources and training materials for Python

Compiled python binaries pythonlibs
Python Pycon Videos http://python.mirocommunity.org/
Google's Python Class

Feel free to help me out add to this collection of links and post a comment.
cheers michael

Open spatial software and others

2010-12-28T09:44:00.004+01:00

For anyone just starting out in OPEN GIS or spatial technologies in general its hard to find the wright tools/software for the job. Here is a list of free and some open source, software (not just spatial ones) I use all the time.

Database: Postgresql with PostGIS
Desktop GIS: Quantum GIS
Web Map Server: Geoserver
Web Map Javascript Library: Openlayers
Web Rich Internet Application (RIA): ExtJS
Web Map RIA: Geoext
Web Map environment: Opengeo
Google Earth
Spatial Libraries: FWTools, Geotools
OpenStreetMap Import to Postgresql: osm2pgsql
Windows Mobile 5.x, 6.x OSM data collection: osmtracker
Scripting Languages: Python, Javascript
Desktop Graphics: Inkscape (vector), GIMP (raster)
Mind Mapping: FreeMind
Screen capure: Greenshot (windows only)
Text editor: Notepad++ (windows only)
Versioning subversion: Tortoise SVN (windows)
Virtual Machines: VMWare Player
Operating Systems: Windows 7 and Ubuntu 10.10
Windows Explorer: Total Commander
Organizing: Keepass
Zipping: Izarc
Integrated Development Environment (IDE): Eclipse, Aptana, PyCharm
Web Project tracking: trac
Business Software OpenERP for customer relation managment, and project management

All of these are either free for personal use or open source so you can download them now and get started.

cheers for now
michael

Postgresql pgagent Windows 7 32bit trouble starting service

2010-12-23T15:24:00.000+01:00

Another adventure with datbase stuff. I have a new machine and need to back up my Postgresql/PostGIS databases and want to use PGAGENT. I have used my trusty .bat files and windows scheduler up until now and just want to try something new.

First I read the great post from postgresonline journal Setting up PgAgent and Doing Scheduled Backups. Its old 2008 but no matter. So I followed all the steps and then damm..

Trouble getting pgagent to start. On my German machine this nice error shows when I try to start the service/dienst " Dienst "PostgreSQL Scheduling Agent - pgagent" wurde auf "Lokaler Computer" gestartet und dann angehalten. Einige Dienste werden automatisch angehalten, wenn sie nicht von anderen Diensten oder Programmen verwendet werden."

This basically says screw you because of one or more of the following causes:

wrong username
wrong password
user has no access privaleges to the db
user has no wrights to execute stuf

So I check the pgpass.conf file located %APPDATA%\postgresql\pgpass.conf You can get the location by typing echo %appdata% inthe command prompt window

Solution is that the user ./postgres didn't have the correct permissions so I just did a quick permissions change and super duper its working!

Well its now working!

I'll try to post all the steps next time in the installation to compliment the good work done Posted by Leo Hsu and Regina Obe

Transform OSM data to epsg: 4326 known as wgs84

2010-12-20T09:13:00.000+01:00

The data is already in my Postgresql PostGIS set up using the osm2pgsql.exe to load it. You can of course specify other EPSG codes upon loading, but it seems to "not work" all the time so I prefer to go with the defaults then transform my data. Spatialreference.org is your best bet for more info.

This is the SQL I used to create a new column to store the OSM data in WGS84 or EPSG 4326. Be sure to change the bits you need for your data.

 --Transforming to Another spatial reference system  
 --first we create a new column to hold the new geometry in the new projection  
 SELECT AddGeometryColumn( 'planet_osm_line', 'geom_wgs84', 4326, 'LINESTRING', 2);  
 -- now we populate the new column with the transformed data using:  
 -- transform(geometrycol, new EPSG)  
 UPDATE planet_osm_line SET geom_wgs84 = transform(way, 4326);  
 --Transforming to Another spatial reference system  
 --first we create a new column to hold the new geometry in the new projection  
 SELECT AddGeometryColumn( 'planet_osm_point', 'geom_wgs84', 4326, 'POINT', 2);  
 -- now we populate the new column with the transformed data using:  
 -- transform(geometrycol, new EPSG)  
 UPDATE planet_osm_point SET geom_wgs84 = transform(way, 4326);  
 --Transforming to Another spatial reference system  
 --first we create a new column to hold the new geometry in the new projection  
 SELECT AddGeometryColumn( 'planet_osm_polygon', 'geom_wgs84', 4326, 'POLYGON', 2);  
 -- now we populate the new column with the transformed data using:  
 -- transform(geometrycol, new EPSG)  
 UPDATE planet_osm_POLYGON SET geom_wgs84 = transform(way, 4326);

OSM data to shapefile with specific OSM Tags

2010-12-10T14:09:00.001+01:00

Well I had a unique problem or I should say not so unique problem in preparing some OpenStreetMap (OSM) data for import into PostGIS and then export to Shapefiles for my students.

Job at hand:
Generate some shapefiles based on specific OSM data tags more specifically wheelchair access tags.

Tools:

osm2pgsql.exe
python 3.1 custom script see below
pgsql2shp.exe this is installed with PostGIS located in the /bin directory

Prerequisites:

Postgresql 8.4.4 with PostGIS 1.5.2
Download and unzip the osm2pgsql.exe for import
Windows 7 Pro (this is not a prereq, just letting you know what I used)

Procedure:

Download OSM data as .osm for your region using the web tab "Export" on the osm website (see screenshot) or visit GeoFabrik or Cloudmade for shapefiles/OSM data.
Prepare data for osm2pgsql.exe by

Edit the import .style file to limit what is actually going into PostGIS so only our needed fields come across. See the wiki page for details
Edit the .osm file to replace all the Tag names so they conform to support the shapefile limitations. I used a custom PYTHON 3.1 script shown below to do this dirty work.

Quick example of what is not supported by shapefiles:

field names larger than 10 characters
field names with ":", ";" " "

I wanted to for example use this osm tag "wheelchair:access" as my field header but it would only use "wheelchair:" if I don't modify the .osm data (xml data) Not good so the job is to rename this to something that is able to be used in a shapefile like "wch_access".

Here is the python code that I used to do the data text search and replace of multiple words in preparing the .osm file for import. Just copy the code below and save it as myfilename.py and be sure to change the location of your .osm file and the name of the new output file.

#http://gomputor.wordpress.com/2008/09/27/search-replace-multiple-words-or-characters-with-python/

 import sys, os  
 def replace_all(text, dic):  
   for i, j in dic.items():  
     text = text.replace(i, j)  
   return text  
 # create a dictionary / hash to store the values to replace  
 # keys are the current words to replace and values are the new replacement words  
 #so the first word is replaced with the second word  
 dic = {  
 'wheelchair:':'wch_',  
 'wheelchair':'wch',  
 'surface':'surf',  
 'smoothness':'smth',  
 'inclination':'incl',  
 'capacity:disabled':'cap_dis',  
 'footway:left:':'fw_L_',  
 'footway:left.':'fw_L_',  
 'footway:right:':'fw_R_',  
 'footway:right.':'fw_R_',  
 'opening_hours':'open_hrs',  
 'addr:':'addr_',  
 'housenumber':'housnum',  
 'guided_tour':'guidtour'  
 }  
 # Here we open the osm file we want to update and prepare so that the shapefile we produce is happy  
 osmfile = open('map.osm', encoding='utf-8')  
 filein = osmfile.read()  
 # now create the new file that we will use for conversion  
 newfile = open('map2.osm', 'w', encoding='utf-8')  
 # write the output into the new file  
 newfile.write(replace_all(filein, dic))  
 #close the 2 open files  
 newfile.close()  
 osmfile.close()

Finally import the .osm file into PostGIS using osm2pgsql.exe
Immediately EXPORT to shapefile with pgsql2shp.exe

wheeoowee it should now be working...this all could be automated into one singel python script but I just haven't had the time to do it but when I do I'll post it up on www.openstreetmap.org and here on my blog.

later spatial geeks

ArcGIS 9.3 FME import shapefile into Postgresql PostGIS

2010-12-06T09:18:00.002+01:00

Here is the task that I am tackling or I should say finished tackling.

Import some Shapefiles into an already existing Postgresql / PostGIS database schema called "demo" (not public schema) with already existing tables for each shapefile that have predefined fields but are lacking any data what so ever. Basically I’m importing only 2 fields one is the geometry column aka the actual geometry of the data in the shapefile and one field with attribute data.

Prerequisits:

Postgresql 8.4 with PostGIS 1.4 or 1.5 installed on local machine (I'm using Win 7 Pro)
ArcGIS 9.3 with FME Interoperability Extension enabled

Lets get started:

First firing up my trusty ArcCatalog 9.3 I created a new “Spatial ETL Tool” to handle the work (Right Click an existing Toolbox select create new.create new Spatial ETL ).
Then following the instructions filling in the source files and specifying the Postgresql PostGIS database on my localhost as the destination all things good so far.
Take the defaults and hit run.

oops some not so expected results happend as I trusted the defaults blindly. So what actually went wrong

wrong schema namely ‘public’ ( I wanted my schema ‘demo’)
wrong SRID is defined (my local srid 31258 is what I wanted I got 900914)
new field is created namely gid (not wanted since I already have ‘id’ defined as the primary key with its appropriate sequence).

To deal with the errors or I should say misconfigurations I made were easily handled by the following three points:

1. Error 1 Solution. we need to go to Postgresql and change the default search path value to my database as defined in the PG connection string in FME

SQL: ALTER USER myusername SET search_path =’$user’,'demo’,'public’;
SQL: ALTER DATABASE mydbname SET search_path = demo, public;

see this helpful link http://groups.google.com/group/fmetalk/browse_thread/thread/cd0b14509351e61f for more info.

2. Error 2 Solution. is to set the Geometry Column SRID to 31258 not “Inherit_FROM_WRITER”

3. Error 3 Solution. clicking each feature in my FME model and changing the Feature Type Properties to the following settings here in this screenshot.

Notice that “Create with OIDs” NO is what prevents the creation of the ‘gid’ column that I didn’t want.

Run the model again and woola it worked. The shapefiles were uploaded into the correct Postgresql schema, the data had the correct SRID defined and no more silly GID column added to my predefined table structure.
What a fun night I had playing with this. I hope this helps you out in your need to transform and play with ETL with ArcGIS and Postgresql-PostGIS.

Cheereo
Michael

How to calculate the length value for a field in QGIS

2010-12-06T09:10:00.000+01:00

Really I should have titled this article. "How to create a shapefile with a field to store length values and then fill the field with length values using Quantum GIS aka. QGIS "

NOTES:
- Using Quantum GIS 1.7 r14693 from the osgeo4w-setup.exe
- OS Windows 7 Professional 32 bit
- example will use EPSG 31258 but you can of course use your own local EPSG code.
- Generally I already have some other data in EPSG 31258 I use as background data so I can see where my new file is in the world. When QGIS starts it has a default location of 0.00 x and 0.00 y at the center of your map window which = no where in particular. You should then define your project coordinate system under Settings > Project Properties > tab coordiate reference systems and set to EPSG 31258.

For more funky spatial reference EPSG fun check out spatialreference.org

Part 1. Creating an empty Shapefile to store our data with lengths.

Open Quantum GIS Go to Layer > New > New Shapefile Layer…
Select the radio box Line
Select your local coordinate system for us here in Klagenfurt, Austria we are going to use EPSG 31258 (ESRI “MGI_Austria_GK_M31″).
Add a new attribute field one called id. (this can later be used as a primary key value)
Name: id
Type: whole number
Length: 10 (default)
Click Add to attributes list
Add a new attribute field one called length. (here we will store our length value)
Name: length
Type: real
Length: 10 (default)
Click Add to attributes list
Click OK Save As menu pops up and now give your shapefile a name and a location to save it on your computer. Enter filename: examplename
Click SAVE

Part 2. Editing our newly created shapefile and then finally populating the length field with values.

Select your newly created shapefile in the Layers by clicking on the name (this will highlight the name in blue on windows 7 pro default settings)
Click the Toggle editing button in the editing toolbar (the one that looks like a pen)
Click the Capture line button and start clicking to draw your line (left click, left click , left click ,left click … Right Click = DONE)
Click the Open Attribute Table button
Click the Open Field calculator button (looks like a calculator)
Select update existing field
select the length field
select the button called Length with a double click (now $length should show up in the field calculator expression box)
Click OK

and wolla! you now have some funky length data in your length field ! only x clicks and you have it.
If you have any feedback please let me know.
Unitl next time