Category Archives: Maps

Batch Exporting to PostGIS from Oracle

Posted on by
Reply

The process in the previous post loaded one layer at a time.  This batches the process.

Some preparation of a text file with a list of all Oracle table names to be extracted and loaded into PostGIS is required.

rem This reads a list of table names from txt file and passes them to ogr2ogr which then exports from Oracle into PostGIS
for /F %%f in (C:\path\to\your\textfile\OracleTables.txt) do ogr2ogr -a_srs EPSG:27700 -overwrite -f "PostgreSQL" -nln yourschema.%%f PG:"host=localhost user=youruser password=yourpassword dbname=yourdatabase" OCI:"oracleuser/oraclepassword@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraclehost)(PORT = 1521)))(CONNECT_DATA = (SID =oraclesid))):oracleschema.%%f"
rem
pause

Exporting Data from Oracle to PostGIS

Posted on by
Reply

Traditionally exporting from Oracle to PostGIS involved an intermediate step Oracle to Shapefile and then Shapefile to PostGIS. But this means that field names longer than 10 characters in Oracle get truncated in the conversion to Shapefile. And using the QGIS or PgAdminIII shp2pgsql gui has no easy way to set the schema or SRID other than manual intervention. I guess it could be run from the command line though…

Using Safe’s FME you can go directly from Oracle to PostGIS. But there is no way to define the destination schema in the PostGIS database. The PostGIS writer expects there to be a schema with the same name as the Oracle schema in order to complete the extraction and translation. See https://groups.google.com/forum/#!topic/fmetalk/zQsUUJNR5h8

So, open-source here we come. ogr2ogr can read and write to a number of different formats including Oracle (this uses the proprietary OCI.dll). On Windows install OSGeo4W with GDAL/OGR. Run the setup.exe and “Advanced Install” to install the gdal-oracle10g package. See http://stackoverflow.com/questions/9789686/migrate-spatial-data-from-oracle-to-postgresql

The command to issue at the command line is (all on one line):

ogr2ogr -a_srs <srs> -overwrite -f "PostgreSQL" -nln <postgis schema>.<postgis table> PG:"host=<postgis host> user=<postgis user> password=<postgis password> dbname=<postgis database>" OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"

<srs> = in the EPSG format e.g EPSG:27700

This uses ogr2ogr to connect to an Oracle database, pull a table from a schema and write it to a defined schema in PostGIS database.

Nice!

Sources:

Open-source GIS cluster

Posted on by
Reply

This is the challenge for this week.

PostgreSQL + PostGIS + MapServer + MapProxy + Quantum GIS on Windows 7 32-bit

The aim is to create a scalable mapping solution based on open-source software that is easy to use and maintain, is fast and responsive and that can provide data in a number of different formats. Windows installers are available for most of the applications I wanted to use which made life a bit easier. Doing the same thing on Linux might be even easier. The corporate proxy and firewall certainly made things more complicated than they should have been.

mapproxy Open source GIS cluster Continue reading →

Inver-Inver 2012

Posted on by
Reply

The Steeple Church is raising funds for the Church redevelopment project.  Part of the plan is to help raise £10000 by walking and cycling across Scotland from West to East in four days.  You can donate through the following JustGiving account.

  • Friday : Inverie – Roy Bridge : walk 16.5 miles(to west end of Loch Arkaig) and cycle 25 miles
  • Saturday : Roy Bridge – Glen Feshie Hostel : cycle 52 miles
  • Sunday : Glen Feshie Hostel – Braemar : walk 19 miles (to Linn of Dee) and cycle 7 miles
  • Sunday : Braemar – Inverbervie : cycle 58 miles

The actual route taken is more direct than that shown on the map and does a better job of joining the dots (The route shown is approximate).  The support vehicles will probably have to travel the route shown in blue.

Embedding maps

Posted on by
Reply

In an effort to make more useful information available to local residents without being overly technical, part of what I have been working on is making simple maps to embed in a website.  These return some (hopefully) useful information.  There is a move away from monolithic web GIS applications that do everything to making spatial information available everywhere across a website.  These simple maps can be embedded on any page with the website.  Each map will only display information relevant to the content on the page.

Waste collection dates (custom complex map viewer)

My Location report (default complex map viewer) Continue reading →

2nd Anniversary

Aside

Reply

Aclogo col 2nd AnniversaryWell, whaddayaknow?  Two years at Angus Council have passed in a flash.  Still making maps and making sure people know where they are.  I have help now too which makes taking over the world so much easier.

Unconnected black hole

Posted on by
Reply

According to a recent survey by the BBC, Newtyle is a veritable void of connectivity. Using Android devices to record mobile signal strength data was collected over a three week period. The resulting coverage map can be seen on the BBC site – http://www.bbc.co.uk/news/technology-14582499. If you put in a Newtyle postcode (PH12 8TU will do) you can see that the village is just about the only black spot on the map.  Do bear in mind that less than 50,000 people from across the UK took part in the survey and not many of them came from Newtyle.

mobile coverage 300x256 Unconnected black hole

mobile coverage map

Incommunicado

Posted on by
Reply

We’ve moved into our new house and we are in the process of updating contat details, change account information and waiting for a telephone and broadband internet connection.  While there are plenty of other things to be getting on with, you realise how dependent on instant communication you become when the only mean of communcation is putting pen to paper or actually going to see the person you wish to talk to.

I had a look at the cell network coverage for Newtyle and it appears to sit right in the eye of the 2G/3G storm.  There is zero coverage anywhere within the bounds of our property and we are lucky if we get two bars of signal outside the front gate.  The answer does appear to be blowing in the wind sometimes as the signal can vary wildly.

Here are some maps from the major providers:

orange coverage 3g 150x150 Incommunicado

Orange 3G coverage

t mobile 3g 150x150 Incommunicado

T-Mobile 3G coverage

t mobile 2g 150x150 Incommunicado

T-Mobile 2G coverage

orange coverage voice 150x150 Incommunicado

Orange 2G coverage

02 coverage 150x150 Incommunicado

O2 2G coverage

vodafone coverage 150x150 Incommunicado

Vodafone 2G/3G coverage

L’alp d’huez

Posted on by
Reply

IMG00031 20100710 1209 150x150 Lalp dhuezLuh Alp Doo Hwez.  Kevin just cycled up here.  Twenty one bends all the way up to 1860m from a start at 740m.  He brought his road bike over from Cape Town a couple of months ago and has been training on the London flats for some of the steepest climbing around.  All you really need to go up is heart, lungs and legs.  Coming down, I would think you would need balls too.

alp d huez2 300x197 Lalp dhuez

L'Alp d'Huez from the bottom

alp d huez 300x197 Lalp dhuez

L'Alp d'Huez from the top