Geo Tricks – SKIPPERKONGEN

Useful tools

prj2epsg.org, convert .proj files to epsg codes; just paste in the file contents
openstreetmap-wkt-playground, visualise WKT geometries on an OpenStreetMap background

org2ogr tricks

To PostgreSQL

From OpenStreetMap PBF format:

# database called "osm"
ogr2ogr -f PostgreSQL "PG:dbname=osm" planet.osm.pbf

From GeoJSON:

ogr2ogr -f "PostgreSQL" \
PG:"host=localhost user=xxx password=xxx dbname=xxx" \
input.geojson

Import into specific PostgreSQL schema (default is public):

ogr2ogr -f PostgreSQL \
PG:"host=localhost user=xxx dbname=xxx" \
-lco SCHEMA=xxx \
input.geojson

From PostgreSQL

To GeoJSON:

ogr2ogr -f "GeoJSON" \
output.geojson \
PG:"host=localhost user=xxx password=xxx dbname=xxx" \
-sql "select * from input_table where foo='bar'"

From CSV

For conversion from CSV, first create a VRT file (e.g. called "input.vrt"):

<OGRVRTDataSource>
    <OGRVRTLayer name="input">
        <SrcDataSource>input.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Longitude" y="Latitude"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

CSV to GeoJSON:

ogr2ogr -f GeoJSON output.geojson input.vrt

Reprojection

Reproject shapefile from Lat/Long to "Google Mercator":

ogr2ogr output.shp -s_srs 'EPSG:4326 '-t_srs 'EPSG:3857' input.shp

Public data recipes

Example: Import Danish public data into CartoDB

1. Download zip with shapefiles of administrative borders for Denmark (it's in EPSG:25832)

2. Unzip it

unzip DAGI500.zip

Let's focus on the municipalities...

3. Reproject KOMMUNE.shp to EPSG:4326 using ogr2ogr:

ogr2ogr kommune4326.shp -t_srs "EPSG:4326" KOMMUNE.shp

4. Now we can import it using the web interface for CartoDB.

Reading data w. CartoDB SQL API

http://skipperkongen.cartodb.com/api/v2/sql?q=SELECT st_astext(the_geom) FROM kommune4326 (notice, no key needed because it is a public table)

Writing data w. CartoDB SQL API

http://skipperkongen.cartodb.com/api/v2/sql?q=INSERT INTO observations(the_geom,obs_id,ord) VALUES (ST_GeomFromText('POINT(12.59935%2055.66445)',4326),'2','0')&api_key=API_KEY

Other

Very good post on using GIS with Python, Shapely, and Fiona.

Other OGR2OGR Cheat sheets:

5 thoughts on “Geo Tricks”

muthulakshmi
October 15, 2013 at 12:16 pm

can we give only local path or can i give input file path from some other filesystem like hdfs???

Log in to Reply
1. kostas
  October 15, 2013 at 1:05 pm
  
  I haven’t tried it, but it should work on any filesystem that is mounted.
  
  Log in to Reply
muthulakshmi
October 16, 2013 at 3:13 am

Thank u kostas :)

do u have any idea about how to implement HDFS filesystem in ogr2orgr??
Is there any java api provided for this ogr2ogr? if it is so,kindly send me the reference link?

Log in to Reply
1. kostas
  October 16, 2013 at 8:06 am
  
  I’m sorry, but I don’t know what you mean by you first question (implementing HDFS in ogr2ogr). Could you elaborate?
  
  Regarding your second question, there are Java bindings for the ogr2ogr library, meaning you can call GDAL (of which OGR is a module) from Java. Again, I haven’t tried it.
  
  http://gdal.org/java/
  
  Log in to Reply
2. Bentayeb
  August 10, 2014 at 8:03 am
  
  aahhh vielen dank!ich bin astlouber unterstfctzer/mapper/fan von OSM aber nicht ste4ndig im wiki/forum/mailingliste unterwegs.perfekt wenn es eine zentrale stelle gibt, an der man sich OSM news reinpfeifen kann!danke nochma,ben
  
  Log in to Reply