Tuesday, December 28, 2010

Open spatial software and others

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.
All of these are either free for personal use or open source so you can download them now and get started.

cheers for now

Thursday, December 23, 2010

Postgresql pgagent Windows 7 32bit trouble starting service

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.
  1. 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:
      1. wrong username
      2. wrong password
      3. user has no access privaleges to the db
      4. 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!
  2. 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

Monday, December 20, 2010

Transform OSM data to epsg: 4326 known as wgs84

 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);  

Friday, December 10, 2010

OSM data to shapefile with specific OSM Tags

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.

  1. osm2pgsql.exe
  2. python 3.1 custom script see below
  3. pgsql2shp.exe  this is installed with PostGIS located in the /bin  directory
  • 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)
  1. 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.
  2. Prepare data for osm2pgsql.exe by 
    1. 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
    2. 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:
  1. field names larger than 10 characters
  2. 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.

 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 = {  
 # 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  

  1. Finally import the .osm file into PostGIS using osm2pgsql.exe
  2. 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

Monday, December 6, 2010

ArcGIS 9.3 FME import shapefile into Postgresql PostGIS

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.

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

Lets get started: 
  1. 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 ). 
  2. 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. 
  3. Take the defaults and hit run.
oops some not so expected results happend as I trusted the defaults blindly. So what actually went wrong
  1. wrong schema namely ‘public’ ( I wanted my schema ‘demo’)
  2. wrong SRID is defined (my local srid 31258 is what I wanted I got 900914)
  3. 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.


      How to calculate the length value for a field in QGIS

      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 "
      - 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.
      1. Open Quantum GIS Go to Layer > New > New Shapefile Layer…
      2. Select the radio box   Line
      3. Select your local coordinate system for us here in Klagenfurt, Austria we are going to use EPSG 31258 (ESRI “MGI_Austria_GK_M31″).
      4. 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)
      5. Click   Add to attributes list
      6. Add a new attribute field one called length.  (here we will store our length value)
        Name:  length
        Type: real
        Length: 10  (default)
      7. Click Add to attributes list
      8. 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
      9. Click SAVE

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

      1. 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)
      2. Click the Toggle editing button in the editing toolbar (the one that looks like a pen)
      3. Click the Capture line button and start clicking to draw your line  (left click, left click , left click ,left click … Right Click = DONE)
      4. Click the Open Attribute Table button
      5. Click the Open Field calculator button (looks like a calculator)
      6. Select update existing field
      7. select the length field
      8. select the button called Length with a double click  (now $length  should show up in the field calculator expression box)
      9. 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