Exporting from Oracle in UTF8

Use NLS_LANG=ENGLISH_UNITED KINGDOM.UTF8

Identifying failed characters in a postgresql migration from Oracle.

Due to a failure somewhere along the line of a migration from another database to postgresql some of the characters have failed. Since there's a large number of tables and we don't know which columns have characters that have failed here's how we did it;

A pg_dump of the database: pg_dump --data-only --column-inserts -U <user> <db> > dump.txt. Failed characters would show up as '?' so I planned to remove any lines from the text file with a bit of reg exp in notepad++: ^((?!\?).)*$ but the dump file being almost 5Gb was too big for notepad++ to handle. So in steps Powershell and we can do a grep style command: select-string .\dump.txt -pattern "\?" > failed_characters.txt

Bear in mind that this will include valid ? characters but it's start.

And then split the resultant file because it's too big to load into Notepad. [Taken from here]

$linecount=0;$i=0;Get-Content .\BIG_LOG_FILE.txt | %{ Add-Content OUT$i.log "$_";$linecount++; if($linecount -eq 3000){$I++;$linecount=0}}

create new points table with intersecting line id

CREATE TABLE points2 AS
(SELECT point.*, line.lineid as line
FROM point INNER JOIN line
ON ST_Intersects(point.wkb_geometry, line.wkb_geometry));

Allowing json/geojson in IIS7

Taken from Muhammad Bilal Awan's post below but pasted here for my own benefit;


Open IIS Manager
Display properties for the IIS Server
Click MIME Types and then add the JSON extension:
File name extension: .json
MIME type: application/json
Go back to the properties for IIS Server
Click on Handler Mappings
Add a script map
Request path: *.json
Executable: C:\WINDOWS\system32\inetsrv\asp.dll
Name: JSON


And the same applies for geojson of course.

http://www.uipress.com/add-json-handler-support-in-iis-7/#.UPax0mKalYh

postgresql replication, load balancing and SPOFs

here's article for setting up two pgpools - so no single point of failure (thanks to Nozomi Anazai)
http://www.pgpool.net/pgpool-web/contrib_docs/watchdog/en.html

here's an article how to set up pgpool to handle replication and load balancing of a postgresql cluster (thanks to Tatsuo Ishii)
http://pgpool.projects.postgresql.org/contrib_docs/simple_sr_setting/index.html

And here's a great presentation from Tom Fry @ OrdnanceSurvey on their setup (spatial databases this time) using pgPool. No replication duties in this case I think as it's only handling select duties. Here.

Show databases and tables in postgresql

show databases:
select datname from pg_database;
show tables:
\dt

postgres ssh-copy-id: ERROR: No identities found

I'm working on hot_standby with postgresql on centos. One thing I got stuck on being a noob at linux was how to create ssh keys for the postgres account. I was following the 3dtin blog on postgresql hot_standby and got stuck at the following point; 
su - postgres 
ssh-copy-id 
ssh-copy-id: ERROR: No identities found 

Ok, remove that pesky selinux. I believe that's at fault. Set SELINUX in /etc/sysconfig/selinux to disabled. 
su - postgres; pwd should be /var/lib/pgsql 
mkdir .ssh 
ssh-keygen (hit defaults) 
ssh-copy-id -i ~./ssh/id_rsa.pub 
check with ssh So if you do that on the pg_master and drop the key on the pg_slave, you should then repeat the other way (as the 3dtin blog says).

Cluster your PostGIS

I've been asked to look at clustering work's PostGIS needs. We've got some OSM which is only used for Mapnik to read and render from so I'm going to ignore that for now. I'm more interested in setting up a PostGIS cluster for user-created content.

I've found a great PDF presentation from Steve Bennett at the Ordnance Survery. Also a great, simple note on clustering Postgresql from Satoshi Nagayasu. I'm in the process of knocking up a simple test cluster so I'll report back.

Eggie5's run through of streaming replication looks a brief but solid guide to replication.

Jyro's tutorial here on 3DTin is wonderful. It pushed this fat-arsed noob all the way through so I've got a master/standby setup running. Something to play with and attempt to break - that's the point right? Tutorial here.

Loading PostGIS with ESRI Shapefiles

Note to self:
Use shp2pgsql to create CREATE/INSERT sql;
shp2pgsql -c -D -s 4326 -I <shp file> <table name> > <file output name>.sql
and then follow it up with the lovely;
psql -d <database name> -U <user name> -f <file output name>.sql
This way I've got all that lovely SQL to store somewhere if I need it.

How to get an up-to-date OSM in PostGIS

How to build an OSM database that is up to date. 

Rather than doing a quarterly manual job of downloading a 14Gb+ file, decompressing it (250Gb+) and inserting it into a database it will be a lot easier to download daily (50Mb) OSM change files (.osc) and apply the changes to the existing database. 

The instructions below is a modified version of Martin van Exel's tutorial here - https://docs.google.com/document/pub?id=1paaYsOakgJEYP380R70s4SGYq8ME3ASl-mweVi1DlQ4 

# get osmosis
su cd /opt 
wget http://dev.openstreetmap.org/~bretth/osmosis-build/osmosis-latest.tgz 
tar zxvf osmosis-latest.tgz
rm -rf osmosis-latest.tgz
# in my case osmosis-0.41 

# now pop into PostgreSQL and build a suitable database & user (could be tidier)
adduser osm 
passwd osm 
# and some badly chosen password: osm, osm
su - postgres psql 
createdb osm
createlang plpgsql osm
CREATE USER osm WITH PASSWORD 'osm';
#CREATE DATABASE osm;
GRANT ALL PRIVILEGES ON DATABASE osm to osm;
GRANT ALL PRIVILEGES ON DATABASE osm to postgres;
\q
psql -d osm -U osm -f /usr/pgsql-9.1/share/contrib/postgis-2.0/postgis.sql 
psql -d osm -U osm f /usr/pgsql-9.1/share/contrib/postgis-2.0/spatial_ref_sys.sql
psql -U osm -d osm
CREATE EXTENSION hstore;
\q
psql -U osm -d osm -f /home/<user name>/osm/osmosis-0.41/script/pgsimple_schema_0.6.sql

# make some directories and get some data
su mkdir /tmp/osm 
cd tmp/osm 
mkdir planet
mkdir planet/replication

# now get some OSM data - either get the whole planet.. or get a sample from http://download.geofabrik.de/osm/ 
cd /tmp/osm/planet/
wget http://planet.openstreetmap.org/planet-latest.osm.bz2

# and thump into the database (using the VERY IMPORTANT --slim option, -C is how much memory we're setting aside for this: 4Gb in this case) /opt/osm2pgsql/osm2pgsql -S default.style --slim -d osm -C 4000 /tmp/osm/planet/planet-latest.osm.bz2 

# create the configuration.txt and download.lock 
osmosis --rrii workingDirectory=/tmp/osm/planet/replication 

# create your state.txt file, visit http://toolserver.org/~mazder/replicate-sequences/ and enter details. To check the datetime required examine the contents of the bz2 file. 

bunzip2 -c planet-latest.osm.bz2 | head

Now at this point I've been running osmosis to grab the change file and osm2pgsql to throw it at the database but you can apparently run osmosis on it's own;

# Osmosis on it's own version
/opt/osmosis-0.41/bin/osmosis --rri workingDirectory=/tmp/osm/planet/replication --sc --wpc user="osm" database="osm" password="osm"

# Or the two command route - grab a change file from the queue of necessary changes to perform
/opt/osmosis-0.41/bin/osmosis --rri workingDirectory=/tmp/osm/planet/replication --simplify-change --write-xml-change /tmp/osm/planet/replication/changes.osc.gz

# And append the changes to the database
/opt/osm2pgsql/osm2pgsql --append -S /opt/osm2pgsql/default.style -d osm -U postgres --slim /tmp/osm/planet/replication/changes.osc.gz 

# You can put either set of commands into a script and cron it up, here's the script for the osm2pgsql route

#!/bin/sh
n=`ps -ef | grep -v grep | grep /opt/osmosis-0.41/ | wc -l`
m=`ps -ef | grep -v grep | grep /opt/osm2pgsql/ | wc -l`
let i=n+m
if [ $i -gt 0 ]; then
echo osmosis or osm2pgsql running
else
echo not running
/opt/osmosis-0.41/bin/osmosis --rri workingDirectory=/tmp/osm/planet/replication --simplify-change --write-xml-change /tmp/osm/planet/replication/changes.osc.gz
/opt/osm2pgsql/osm2pgsql --append -S /opt/osm2pgsql/default.style -d osm -U postgres --slim /tmp/osm/planet/replication/changes.osc.gz 
fi

# Now you're probably running mod_tile and mapnik to render and serve up OSM tiles so we need a method to tell mod_tile to re-render all tiles that have updates. We can amend the osm2pgsql call with -e options to produce a list of tiles that need to be expired and thus re-rendered. [See OSM Tile Expire Methods].
# change the osm2pgsql command to read
/opt/osm2pgsql/osm2pgsql --append -S /opt/osm2pgsql/default.style -d osm -U postgres --slim /tmp/osm/planet/replication/changes.osc.gz -e15 -o expire.list

# We now expire all tiles listed in the expire.list. I haven't included the install and setup of Mapnik or mod_tile so i'm going to use some dummy locations for them. I'm expiring tiles from zoom level 6 and downwards
cat expire.list | /opt/mapnik/mod_tile/render_expired --map=osm --min-zoom=6 --touch-from=7 >/dev/null

# The OSM Tile Expire Methods guide also recommends a daily re-render of the lower zoom levels.

Installing PostGIS 2.0 & GeoServer on Centos 6 64bit

Installing PostGIS 2.0 and GeoServer on Centos 6 64bit

as root;
su
exclude=postgres* in base and update sections of /etc/yum.repos.d/CentOS-Base.repo
curl -O http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
rpm -ivh pgdg-centos91-9.1-4.noarch.rpm
curl -O http://mirror.bytemark.co.uk/fedora/epel/6/x86_64/epel-release-6-7.noarch.rpm
rpm -ivh epel-release-6-7.noarch.rpm
yum install postgresql91-server postgis2_91
yum install postgresql91-contrib.x86_64

service postgresql-9.1 initdb

# edit postgres' conf
vi /var/lib/pgsql/9.1/data/postgresql.conf
add listen_addresses = '*'

# allow some connection, make sure the last one matches your local network if you're doing local network stuff...
vi /var/lib/pgsql/9.1/data/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host all all 192.168.1.0/24 md5

# kick postgres
service postgresql-9.1 start

# run on startup
chkconfig postgresql-9.1 on

# change the password for the postgres account
passwd postgres

su postgres
psql
ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres';

# extensions time..
CREATE EXTENSION adminpack;

# spatialise your db
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

\q
exit

# allow incoming connections on 5432 (postgresql standard port)
back to root;
add the following to /etc/sysconfig/iptables above any REJECT rule
-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

service iptables restart

# back to postgres' bash
sudo -i -u postgres
createdb template_postgis
createlang plpgsql template_postgis
psql -d template_postgis -f /usr/pgsql-9.1/share/contrib/postgis-2.0/postgis.sql
psql -d template_postgis -f /usr/pgsql-9.1/share/contrib/postgis-2.0/spatial_ref_sys.sql
psql -d template_postgis -f /usr/pgsql-9.1/share/contrib/postgis-2.0/postgis_comments.sql
psql
CREATE DATABASE my_spatial_db TEMPLATE template_postgis;
\q
exit

# back to root
# Installing Geoserver
# we need java, java -version says no java
yum install yum-priorities
rpm -Uvh http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm
rpm -Uvh http://mirrors.dotsrc.org/jpackage/6.0/generic/free/RPMS/jpackage-utils-5.0.0-7.jpp6.noarch.rpm
Next we will install Java and Tomcat 6:
yum -y install java
yum -y install tomcat6 tomcat6-webapps tomcat6-admin-webapps
vi /etc/tomcat6/tomcat-users.xml
add a user with roles of admin,manager
chkconfig tomcat6 on
# now to geoserver
cd /home/<user>
wget http://downloads.sourceforge.net/geoserver/geoserver-2.1.4-war.zip
unzip geoserver-2.1.4-war.zip
mv geoserver.war /usr/share/tomcat6/webapps
# start up tomcat6
service tomcat6 startexit
# test geoserver at
http://localhost:8080/geoserver/web/

# and you'll probably want access across your network
iptables -A INPUT -p tcp --dport 8080 -j ACCEPT
/sbin/service iptables save
iptables -F

Changing an icon in a treepanel extjs 3

It took me a while to find a solution - probably a slow head day but here's how;
In this case a node represents a web mapping service. If the WMS is alive it should return a list of layers which are used to create a series of child nodes. So if no children change the icon to something relevant.

the css file;
.failedwms-icon {
background: url('resources/deadwms.gif') no-repeat;
height: 16px;
width: 16px;
}

in your js code;
'load': function(node){
// if a node receives a response and no layer child nodes are created we want to change the icon
if (node.childNodes) {
if (node.childNodes)
{
if (node.childNodes.length == 0)
{
node.attributes.iconCls = 'failedwms-icon';
node.getUI().iconNode.className = node.attributes.iconCls;
}
}
}
}

compiling OpenLayers

Grab it from their github: https://github.com/openlayers/openlayers/zipball/master

Drop closure-compiler.jar & compiler.jar into openlayers\tools
Go to \build and ...

build.py -c closure full OpenLayers.js for regular compressed OpenLayers (700kb odd)
buildUncompressed.py full OpenLayers-uncompressed.js for phat Openlayers (2.5Mb odd)

Popularity of WMS InfoFormats

I'm doing some development work on a web map that brings wms layers from a host of UK public wms providers. Part of the work is to provide an info tool and we're using OpenLayers getFeatureInfo control to do this.

The problem with working with so many external wms providers is the lack of consistency in info formats supported. A quick summary (below) shows that html and plain text are the most supported with ogc xml a reasonable third. So we'll be concentrating our support for these formats and hopefully, if time and money permits, to extend support to others. Not good findings for ogc gml as it comes last with only 11% support.

100% support: html & plain
89% support: ogc xml
67% support: xml
19% support: esri raw xml & esri feature info xml
11% support: ogc gml

RSS feed of Google Calendar Events

Use the following;

https://www.google.com/calendar/feeds/[your account]/public/basic?orderby=starttime&sortorder=ascending&max-results=5&futureevents=true

Now to put it on XBMC. The GF will be impressed.

GIS Technologies - DotSpatial

I'm going to hopefully run through the majority of the technologies on show at FOSS4G this year. I probably won't be going myself but at least by September I'll have first-hand experience with the big names.

Okay, the first one I chose was DotSpatial. I think i'll bunch up the OSM stuff into one long session - that's why I've gone and skipped over it.

So DotSpatial. I've only had a half a day but there's plenty of clear tutorials over at DotSpatial Docs to work through. I tried out a few and it's normally stuff for a dot Net Spatial library - map controls and ease of loading/editing vector and raster files.

Loading WMS into a map contol - no simply single layer class yet but a workaround was posted by Ted - here

Coming from working with ArcObjects it's looks a godsend of common sense. I'm not sure if I'll yet see a job post looking for DotSpatial as a requirement but you never know.

GIS Technologies this year at FOSS4G

This is a boiled down list of the technologies to be discussed at FOSS4G this year. I've been using most of the OSGeo GeoStack a fair while but I knew there was a lot out there I wasn't getting exposure to. Simmering down the tech list from the FOSS4G schedule has given me an insight into what I should be looking at. And really, I wasn't expecting it to be so darn large.

Sorry there's no links. Some entries are missing from the schedule due to their particular focus but I've tried to keep it complete as best possible from a developer's point of view.

OSM - OpenStreetMap
JOSM - an editor for OpenStreetMap in Java
OSM + PostGIS + QGIS - Data analysis
DotSpatial - Mono/.Net Framework C# GIS library
PostGIS
MapFish - web framework, companion to OpenLayers, GeoExt, GeoAlchemy, Shapely
FormAlchemy / GeoFormAlchemy
Geomajas - a client/server framework, uses GWT
Spatial Data Infrastructure/GeoNetwork/GeoCat Bridge creates metadata
GeoMoose - client framework for MapServer, good on data organisation, data maintenance
pgRouting - routing extension to PostGIS
Geoserver
OpenLayers
Common JavaScript libraries - jQuery UI, ExtJS, Sencha Touch
GeoKettle - ETL tool, GeoMonrian (Spatial OLAP server) and GeoBIExt - all @ spatialytics.org
OGC WFS-T, TinyOWS
GeoExt
MapServer
QGIS
Mapnik, Node.js
i2maps - geocomputational platform, spatial/temporal data (built around OpenLayers and GeoDjango)
GeoNode
NodeJS framework, Polymaps framework - both for back-end
Inkscape - SLD production
Zoo-Project and WPSs (Web Processing Services)
Amazon Web Services - autoscaling, load balancing cluster
Spatial Wiki - Ubuntu, MediaWiki, Apache, Geoserver, OpenLayers, PostGIS
NSDI, GIS in urban planning
Flot plotting library, spatio-temporal modelling - surfaces
HydroDesktop, free GIS for hydrological modeling
Tilecaching, secured WMS, MapProxy
Imposm - imports OSM data into PostGIS
gvSIG Mini - production of PoI data from OSM, map viewer for Android
Flex, OpenScales, NetCDF, AXIIS, Degrafa, SOS, AIS - for Oceanic applications
Mod-geocache - caching server
MapMint - SDI manager
MapQuery, gRaphael (SVG chart library)
TileMill with Mapnik and Carto (map styling language)
Glob3 - 3D framework, suitable for iOD and Android devices
Google Fusion tables > kml for google maps
GDAL
GeoTools - Java GIS library
MapWindow - MS Windows GIS with .Net API
GXP - Javascript library connects to OpenLayers, Ext JS, GeoExt using a JSON configuration.
Tinyows, GeoPrisma, Zoo WPS - part of the MapServer project stack
GeoScript - extends python, javascript, scala, groovy
CartoSet - web map framework, built upon Ruby on Rails framework ‘CartoDB’.
GeoShield - security access-control to OGC services
MapGuide OpenSource Server & Web Extensions
OGC Web Processing Service (WPS), 52 North, Geoserver
OpenAerialMap
Mobile mapping: BeeGIS (uDig ext) and Geopaparazzi
Partitioning Around Medoids clustering algorithm with Grass & R
GeoWebCache
Cartagen Knitter - rectifying aerial imagery
Opticks - open source image processing software
OpenLayers Mobile
Esri Geoportal Server
Web of Things demonstration - SenseBox
WebGL Earth
WCS 2.0
MassMov2D - landslide modelling
libPC & libLAS - point cloud processing
PyWPS
mapnik2geotools
FalconView
Cellular Automata QGIS plugin
GeoCouch, CouchDB
HydroLiDAR
jai-tools
Geomondrian
GeoServer Plugin for the NoSQL Mongo Database
PostGIS Raster support/extension
JTS Topology Suite
HTML5/WebGL
OGC SOS
GeoCommons
GeoExt vs MapQuery vs Legato
Postal Address Geo-Coder (or PAGC)
AcidMaps
JGrass/uDig
Stado
HSLayers - a Openlayers/ExtJS based mapping framework
Neo4j Spatial
MapBender 3
GeoAPI 3.0
GeoCat Bridge - publishing data from ArcGIS to GeoNetwork/Geoserver/MapServer

and Oracle, FME and ArcGIS Server!

Reproject shapefile for google maps

This is always a bind in whatever GIS package I seem to use - probably because I forget to concentrate over my projection details. However the easiest way to do it is to use fwtools. Download the pack from fwtools.maptools.org and move your data (in this case a shapefile) to the fwtools directory to make it easier.

Load up the FWTools Shell and type the following to convert a shapefile from OS National Grid to Google Maps EPSG:900913

ogr2ogr –f “ESRI Shapefile” –t_srs EPSG:900913 output.shp input.shp

Load into geoserver and test in a google maps page and you're done.

How to remove apps from Facebook

this might change but the current method of removing apps, as of Feb. 2011, is..

1- Account (top right hand corner)
2- Privacy Settings
3- Applications and Websites – edit your settings (bottom left corner
4- Applications you use – edit settings
5- click x to remove whatever application wanted to remove

Seeing XP machine from Windows 7 machine

I renamed one of my XP machines on my home network and ever since the Windows 7 box has failed to see it.. An unidentified error occurs in windows explorer when trying to access the XP machine (as it is listed) but a different situation arises in command line;

In command line
net view
returns the XP machine along with all other machines.. good.
net view \\xpmachine
fails with system error 53 message.

I've tried checking netbios is enabled, firewalls off, ip6 turned off, and a heap load of other things that i've googled on.

Weirdly NET USE M: \\xpmachine\myfolder /user:myUserName myPassword works and I get a mapped network drive. Once done new view \\xpmachine works.

But I don't want mapped network drives. I want to access drives by \\xpmachine\myfolder. But I've found that if i enter \\xpmachine into the address bar of windows 7 explorer window it will prompt for user and password. And once done it seems to have stuck and net view \\xpmachine returns the available resources.

-----------------------------------------

Well it happened again. I can't believe that what I wrote last time actually worked. If it did then lucky me. This time it took me a while to work out that the clock on both machines must be in sync (to some degree I guess) for windows 7 to be happy. I was doing some work that required me to set the year back to 2010 (#cough) so correcting that back to normal fixed it.

Honestly, you think network diagnostics were explain that's the reason why the shared folder access fails.