Importing data from a CSV file into a Postgres table

Simple CSV file import

You have a CSV file called “data.csv”. It has a header line, and is delimited using “;”. You want to import it into Postgres and a table called “your_table”:

Create the database table. Set column-types so the string fields in the CSV file, can be cast to values in columns.

CREATE TABLE your_table
(
  -- Your columns
);

Continue reading “Importing data from a CSV file into a Postgres table”

How to load an ESRI Shapefile into a PostGIS DB

Assuming a shapefile called myshapefile.shp, a table mytable in schema xyz, in a PostGIS enabled database called mydb on localhost. The table is owned by user dbuser who has password “secret”.

Using shp2pgsql

shp2pgsql myshapefile -I xyz.mytable > statements.sql
psql -d mydb -h localhost -U dbuser -f statements.sql

This tip and many more can be read in Making Maps Fast.

Using ogr2ogr

This is even easier with ogr2ogr:

ogr2ogr -f "PostgreSQL" PG:"host=localhost user=dbuser dbname=mydb password=secret" -lco SCHEMA=xyz myshapefile.shp

Hello world plugin for Nagios in Python

Nagios looks at 1) return codes and 2) output to stdout. This is the hello world of Nagios plugins, written in Python:

check_helloworld.py:

#!/usr/bin/env python
 
# optparse is Python 2.3 - 2.6, deprecated in 2.7
# For 2.7+ use http://docs.python.org/library/argparse.html#module-argparse
from optparse import OptionParser
 
# CONSTANTS FOR RETURN CODES UNDERSTOOD BY NAGIOS
# Exit statuses recognized by Nagios
UNKNOWN = -1
OK = 0
WARNING = 1
CRITICAL = 2
 
# TEMPLATE FOR READING PARAMETERS FROM COMMANDLINE
parser = OptionParser()
parser.add_option("-m", "--message", dest="message", 
   default='Hello world', help="A message to print after OK - ")
(options, args) = parser.parse_args()
 
# RETURN OUTPUT TO NAGIOS
# USING THE EXAMPLE -m PARAMETER PARSED FROM COMMANDLINE
print 'OK - %s' % (options.message)
raise SystemExit, OK

Continue reading “Hello world plugin for Nagios in Python”

Using CORS instead of JSONP to make cross site requests

Introduction to CORS

CORS (Cross Origin Resource Sharing) is a mechanism specifies by W3C (draft), for allowing browsers to make cross origin requests for resources on other domains under certain conditions. It’s related to JSONP because it solves a similar problem, namely loading data from one domain, into a web application running on a different domain. A difference is that CORS supports the full palette of HTTP verbs, not just GET.

See also: http://en.wikipedia.org/wiki/Cross-Origin_Resource_Sharing

Continue reading “Using CORS instead of JSONP to make cross site requests”

How to group by date in Oracle database

I keep forgetting how to do this.

How to group on a date column (date_col in the example) in Oracle:

SELECT COUNT(*) FROM TABLE GROUP ON TO_CHAR(date_col, 'DD-MON-YYYY HH:MI:SS')

For other format strings than

'DD-MON-YYYY HH:MI:SS'

see this page.