problem exporting data with ogr2ogr

i wanted to export a geojson file from my table „GeoData4326“ (with quotes) postgis postgres database with ogr2ogr

ogr2ogr -f „GeoJSON“ export.json PG:“host=localhost dbname=mydb user=myuser password=12341234 „\“GeoData4326\““

i got the following error:

ERROR 1: No field definitions found for ‚“GeoData4326″‚, is it a table?
FAILURE: Couldn’t fetch requested layer ‚“GeoData4326″‚!

my workaround is: create a view to this table without quotes on the name:

create or replace view GeoData4326 as SELECT * FROM „GeoData4326“;

now the export should work.

Advertisements

sqlite on raspberrypi

not a big deal with that
install the sqlite3
sudo apt-get install sqlite3

now you can read db-sqlite-files. here are some example and useful SELECT-statements

# open db
sqlite3
# list all tables
SELECT name FROM sqlite_master WHERE type=’table‘;
# list columns
SELECT sql FROM sqlite_master WHERE tbl_name = ‚xxx‘ AND type = ‚table‘;
# convert datetime
select strftime(‚%Y-%m-%d‘, published_at / 1000, ‚unixepoch‘), title from posts order by published_at;
# exit
.exit

postgresql with OSM on raspberryPI

follow these instruction (german) to install all you need for your own osm-server

install postgresql

first update

  sudo apt-get update
then install the package; about 21MB additional space will be used
  sudo apt-get install postgresql
after that the server should report
  [ ok ] Starting PostgreSQL 9.1 database server: main.
here are some steps to configure the server
install tools for osm
install imposm git postgis and postgresql-9.1-postgis
  sudo apt-get install postgis imposm git postgresql-9.1-postgisnow create a source directory and download osm data
  mkdir ~/src
  cd ~/src && wget http://download.geofabrik.de/europe/slovenia-latest.osm.pbfdownload the following tool, needed later
cd ~/src && git clone https://github.com/mapbox/osm-bright.git

 

run the following scritp as postgres-user, so first create a sh-file
  set -xe
  createuser –no-superuser –no-createrole –createdb osm
  createdb -E UTF8 -O osm osm
  createlang plpgsql osm
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
  psql -d osm -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
  psql -d osm -f /usr/share/python-imposm/900913.sql
  echo „ALTER TABLE geometry_columns OWNER TO osm; ALTER TABLE spatial_ref_sys OWNER TO osm;“ |psql -d osm
  echo „ALTER USER osm WITH PASSWORD ‚osm‘;“ |psql -d osm
  set +x
then login as postgres
  sudo su postgres
  sh ./create_db.sh
  exit

now add the following row to your postgre config and restart postgre
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
  host osm osm 127.0.0.1/32 md5
  sudo /etc/init.d/postgresql restart

to import your first data use (more infos here)
imposm –-read –-write –-optimize -h localhost -p 5432 -d osm -m ~/src/osm-bright/imposm-mapping.py ~/src/slovenia-latest.osm.pbf
this will need quite a time – in my case
  imposm took 2 h 40m 44s

in the mean time you can check your database for new created tables after the message ## dropping/creating tables

in psql you can view your tables with (\connect osm;)

get the version of postgis

SELECT PostGIS_full_version();

postgis_full_version

——————————————————————————————————-

POSTGIS=“1.5.3″ GEOS=“3.3.3-CAPI-1.7.4″ PROJ=“Rel. 4.7.1, 23 September 2009″ LIBXML=“2.8.0″ USE_STATS

(1 row)

  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;

      table_name
————————–
 spatial_ref_sys
 geometry_columns
 geography_columns
 osm_new_mainroads
 osm_new_buildings
 osm_new_amenities
 osm_new_aeroways
 osm_new_motorways
 osm_new_transport_points
 osm_new_landusages
 osm_new_waterways
 osm_new_railways
 osm_new_waterareas
 osm_new_places
 osm_new_admin
 osm_new_barrierways
 osm_new_minorroads
 osm_new_barrierpoints
 osm_new_roads

get column names from a table
  SELECT column_name FROM information_schema.columns WHERE table_name=’osm_new_roads‘;

 column_name
————-
 class
 type
 access
 z_order
 layer
 oneway
 tunnel
 ref
 bridge
 name
 geometry
 osm_id
 id

and easy to find different types
  SELECT type, COUNT(*)  FROM osm_new_roads GROUP BY type ORDER BY type;

      type      | count
—————-+——-
 bridleway      |     1
 cycleway       |    38
 footway        |   855
 living_street  |    27
 motorway       |   171
 motorway_link  |   247
 path           |   318
 pedestrian     |   139
 primary        |   283
 primary_link   |    26
 rail           |   128
 residential    |  2683
 road           |     5
 secondary      |   581
 secondary_link |    12
 service        |   593
 steps          |    60
 tertiary       |   888
 tertiary_link  |     2
 track          |  1820
 trunk          |    47
 trunk_link     |    47
 unclassified   |  1397
(23 rows)

after the import finished turn your *new* tables to operational tables – WARNING old data will be lost!
  imposm –deploy-production-tables -d osm

lets create tiles

go to the folder ~/src/osm-bright and rename
  mv configure.py.sample configure.py

edit the config file
config[„importer“] = „imposm“
config[„postgis“][„host“]     = „localhost“
config[„postgis“][„port“]     = „5432“
config[„postgis“][„dbname“]   = „osm“
config[„postgis“][„user“]     = „osm“
config[„postgis“][„password“] = „osm“
download additional files to ~/src/osm-bright (coastline has ~310MB, shoreline ~45MB, 10m-land 5MB)

  wget http://tilemill-data.s3.amazonaws.com/osm/coastline-good.zip
wget http://tilemill-data.s3.amazonaws.com/osm/shoreline_300.zip
wget http://mapbox-geodata.s3.amazonaws.com/natural-earth-1.3.0/physical/10m-land.zip

create this dir
  mkdir ~/Documents/MapBox/project

psql

get server version
  select version();
PostgreSQL 9.1.14 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Debian 4.6.3-14+rpi1) 4.6.3, 32-bit
  CREATE DATABASE mydb;
response: CREATE DATABASE
drop a database
  DROP DATABASE mydb;
  CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
  );
as you see, squences, keys and uniques will be generated automatically
drop table also dropps all autogenerated objects
get tablenames
  SELECT table_name FROM information_schema.tables WHERE table_schema = ‚public‘;useful documentation to access postgres from external device (eg qgis…)
important commands/files for postgre
config file
  sudo nano /etc/postgresql/9.1/main/pg_hba.conf
restart server
  sudo /etc/init.d/postgresql restart
enter server with psql
  psql -U postgres
exit with CTRL-D
show data align after the query
  -S

manual installation of postGIS

download from http://postgis.net/source
sudo apt-get install libpq-dev libproj-dev postgresql-server-dev-9.1
install geos and gdal – see http://northwestspatial.com/wp/?p=340
download source
./configure
sudo make install
test it with
geos-config –version
gdal-config –version
then update apt-get
sudo ldconfig
download source for postGIS and

./configure –with-topology –with-raster
sudo make install
restart postgresql
sudo service postgresql restart

use this documentation to install postGIS to postgresql
http://postgis.net/docs/manual-2.1/postgis_installation.html#install_short_version

raspberry pi as cd ripper

it’s very easy! i used this tutorial
install abcde (A Better CD Encoder), flac (for the „free lossless audio codec„) and eject to eject to cd.


  sudo apt-get install abcde flac eject
test the eject program to open your cd/dvd-drive

  eject

now copy the original config-file from abcde to a temp-folder and replace the original with the following commands. for your system you have just to modify the path where your flac-files should be stored (edit OUTPUTDIR=/media/usb/cd-rip).


CDDBMETHOD=musicbrainz
CDDBPROTO=6
HELLOINFO=“`whoami`@`hostname`“
NOSUBMIT=y
FLACENCODERSYNTAX=default
CDROMREADERSYNTAX=cdparanoia
PADTRACKS=y
INTERACTIVE=n
LAME=lame
FLAC=flac
ID3=id3
ID3V2=id3v2
CDPARANOIA=cdparanoia
EJECT=eject
METAFLAC=metaflac
CDSPEED=eject
LAMEOPTS=‘–preset extreme‘
FLACOPTS=“-f –best“
ACTIONS=cddb,read,encode,tag,move,clean
CDROM=/dev/sr0
OUTPUTDIR=/media/usb/cd-rip
WAVOUTPUTDIR=/tmp
OUTPUTTYPE=flac
OUTPUTFORMAT=’${ARTISTFILE}/${ALBUMFILE}/${TRACKNUM}.${TRACKFILE}‘
VAOUTPUTFORMAT=’Various/${ALBUMFILE}/${TRACKNUM}.${ARTISTFILE}-${TRACKFILE}‘
ONETRACKOUTPUTFORMAT=$OUTPUTFORMAT
VAONETRACKOUTPUTFORMAT=$VAOUTPUTFORMAT
LOWDISK=n
PLAYLISTFORMAT=’${ARTISTFILE}/${ALBUMFILE}.${OUTPUT}.m3u‘
VAPLAYLISTFORMAT=’${ARTISTFILE}/${ALBUMFILE}.${OUTPUT}.m3u‘
EJECTCD=y

now try to encode some tracks of your cd, eg track 1, 2, 4
  sudo abcde 1-2 4

to convert to mp3 also install lame codec and id3 and id3c2
  sudo apt-get install lame id3 id3v2

now start to rip to flac and mp3 in one step (eg only track 1, 2, 4)
  sudo abcde -o „flac“,“mp3″ 1-2 4

next step is to automate the ripping task

create a new file

  sudo nano /usr/local/sbin/audio-cd-rip.sh

Now the few lines of code
#!/bin/bash
LOGFILE=/var/log/cdrip.log
echo „$(date)“ >> $LOGFILE

(
# Wait for lock on /var/lock/.audio-cd-rip.lock (fd 200) for two hours
flock -x -w 7200 200 || exit 1

abcde 2>&1 >> $LOGFILE
rc=$?
if [[ $rc != 0 ]] ; then
eject
exit $rc
fi
) 200>/var/lock/.audio-cd-rip.lock
Mark the script executable.
  sudo chmod +x /usr/local/sbin/audio-cd-rip.sh
Now we create a udev rule that runs this script. A udev rule lets the udev service call our script it the conditions of the rule match.
  sudo nano /etc/udev/rules.d/99-cd-audio-processing.rules
Insert one single line:
SUBSYSTEM==“block“, KERNEL==“sr0″, ACTION==“change“, RUN+=“/usr/local/sbin/audio-cd-rip.sh &“
This says, that the script shall run when a CD with at least one audio track is inserted in block device sr0 (which is usually your first CDROM/DVD/WHATEVER-Drive). The script will run in background (you see the ‘&’?). Now reload udev rules
  sudo udevadm control –reload

due to bugs in the OS you have to make the following work-around to „active“ the cd-drive

  sudo crontab -eAdd this line at the end
  */1 * * * * /bin/touch /dev/sr0

but the cd-drive always opens and closes – so that’s not a good workaround 😦

gnuplot on raspberry pi

install gnuplot-x11 needs about 25MB
sudo apt-get install gnuplot-x11

first little test on the command line
create a command file command.gpl containing
set terminal png size 400,300
set output ‚output.png‘
plot [-10:10] sin(x),cos(x)

now create the graphic with the following command
gnuplot command.gpl

to use a data file (default with blank as column seperator), just add the file name to the plot command
plot „data.dat“

links
http://www.raspberrypi-spy.co.uk/2014/04/how-to-use-gnuplot-to-graph-data-on-the-raspberry-pi/?utm_content=buffer9ef78&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer
http://www.gnuplot.info/docs_4.0/gpcard.pdf

Serial Data Connection on Raspberry Pi with GARMIN GPS 12

GARMIN GPS 12

the idea is to read and write data from the GARMIN GPS 12 via raspberry pi. the GPS 12 has 4 pins at the back: POWER, GROUND, DATA IN and DATA OUT. powering the GPS-unit as follows:

Raspberry pi wiring diagram
wire POWER to 5V and GROUND to GND
GARMIN GPS 12 unit view

Next step will be to establish a data connection… create your own cable https://www.syz.com/gps/gpsinfo.pdf http://www.savagehomeautomation.com/projects/raspberry-pi-rs232-serial-interface-options-revisit.html http://pi4j.com/example/serial.html http://elinux.org/RPi_Serial_Connection

https://www.raspberrypi.org/forums/viewtopic.php?t=11228&p=129224 http://pi4j.com/example/serial.html

i gave it up to try it with the old GPS 12. now i try to get GPS-data with the newer GPSMAP 64s.
on the gps-unit go into your settings and set interface to „Garmin Spanner“ – so you can choose after usb-connection if you want storage-mode, yes or no.
after plugging usb to raspi, i see it with „Garmin International GPS (various models)“ after the lsusb-command.

after that i installed gpsd gpsd-clients python-gps
sudo apt-get install gpsd gpsd-clients python-gps
start the daemon (important is the -n switch!)
sudo gpsd /dev/ttyUSB0 -F /var/run/gpsd.sock -n
and connect to the gps-unit
now get gps-data with
gpsmon /dev/ttyUSB0

kill the daemon
sudo killall gpsd

do not run gpsmon and gpsd simultaniously

infos from here and here

https://www.raspberrypi.org/forums/viewtopic.php?t=48345&p=382294