Wednesday, November 11, 2015

How to import Spatialite into PostGIS using ogr2ogr

GOAL: Import a Spatialite table into an existing PostGIS table appending (inserting) new rows using ogr2ogr. The trick is we want to map the fields to import to the correct corresponding PostGIS columns.

 First get the list of Spatialite fields using ogrinfo

 ogrinfo somedb.sqlite sqliteTableName

INFO: Open of `..\sqlite\socgen-db-final.sqlite'
      using driver `SQLite' successful.

Layer name: e00_poly_join
Geometry: Polygon
Feature Count: 60
Extent: (-168613.719162, 5983831.402051) - (-168508.270008, 5983961.187764)
Layer SRS WKT:
PROJCS["WGS 84 / Pseudo-Mercator",
    GEOGCS["WGS 84",
            SPHEROID["WGS 84",6378137,298.257223563,
    EXTENSION["PROJ4","+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0
FID Column = PK_UID
Geometry Column = geom
Text: String (0.0)
spacetype: String (0.0)
building: Integer (0.0)
floor: Integer (0.0)
type_id: Integer (0.0)
tag: String (0.0)
name: String (0.0)

Now you see we have a total of 9 columns
 using the -fieldmap option of ogr2ogr we can only input 7 of those fields.. The FID column and geometry column do NOT count towards the -fieldmap list

-fieldmap 7,4,6,9,10,3,2

This means  7 = field Text,  4 = field spacetype, 6 = building, 9=floor, 10=type_id, 3=tag, 2=name

the value 7 for example represents the column position in your PostgreSQL DB where you want to import INTO using the Spatialite column called "Text" in this example.

Counting the column number in Postgresql is also hard.  You start at 0 NOT including the primary key and excluding the geometry column where ever it is the the column order.

Thursday, August 6, 2015

Python Geospatial Analysis Cookbook coming soon...


My book titled:  Python Geospatial Analysis Cookbook is coming soon
Author: Michael Diener

I am proud of my work and now the last stretch is ahead getting the last corrections, updates, reviews, comments all worked through to make it awesome.

Stay tuned for any updates.


Tuesday, June 30, 2015

Geoserver create a new layer using REST API

I'am using Geoserver since, well a long time now.  This post is going to be a collection of THINGS that you might want to do with Geoserver.  Sometime errors occur when trying to do something with Geoserver with the REST API and those I have posted aswell.

Goal adding a PostGIS table using the Geoserver REST API

curl -v -u yourUserName:PWD -XPOST -H "Content-type: text/xml" -d "<featureType><name>TABLENAME</name><nativeCRS>EPSG:3857</nativeCRS><srs>EPSG:3857</srs><enabled>true</enabled></featureType>" http://localhost:8080/geoserver/rest/workspaces/YOURWORKSPACENAME/datastores/DATASTORENAME/featuretypes

Errors Error occured calculating bounds for someTableName

  • SOLUTION_  permissions on PostGIS tables were incorrect same user as registered in the DB connection must be used when creating a new layer

Saturday, June 6, 2015

How to fix pyshp error reading shapefile

I had a Shapefile I was trying to read using the great pyshp library but it kept shooting out and Invalid Argument Error.

After some web searching it came down to pyshp being very picky about the Shapefile format.  The true cause I still do not know what was wrong.  My Shapefile was created using QGIS, has valid geometries and no problems identifiable in ArcGIS or QGIS.   Due to other experiences using pyshp sometimes if a field value is NULL or empty it causes some error.

The solution:

ogr2ogr  new_shapefile.shp original.shp

the new_shapefile.shp  will work using the pyshp   ShapeRecords()  function.

Possible other problems that pyshp sometimes encounters:

  1. NULL value in field
  2. '' empty in field
  3. .dbf encoding  (Latin1, UTF-8)
Anyway if you know of a cause please let me know.


Saturday, May 30, 2015

GeoDjango < 1.7 return GeoJSON passing model name in request

I thought this might be hard but it really was not.  Django 1.8 introduced GeoJSON serializer so use that if you are starting a new project with Django version 1.8 up.  For those stuck with Django version <=1.7  this is the solution for you.

The problem:
You want to pass a model name to a request and return the GeoJSON for some map application.

  1. pip install django-geojson
  2. add 'djgeojson' to your INSTALLED_APPS in the
  3.   add the URL for example:  here we limit the model name to an alpha numeric characture including underscore or dash that is between 5 and 50 characters long
    • url(r'^geojs/(?P<geomodel_name>[-\w]{5,50})/$',geoview.get_geojson, name="get_geojs"),
  4.  should look something like this:
def get_geojson(request, geomodel_name):
    Pas model name as string
    :param geomodel_name: model name as string
    :return: instance of model


    mymodel = apps.get_model('geo_importer', geomodel_name)
    mygeoj = GeoJSONSerializer().serialize(mymodel.objects.all(), use_natural_keys=True, properties=('name', 'id'), srid=3857)

    return HttpResponse(mygeoj, content_type="application/json")

Friday, May 8, 2015

ogr2ogr Append new Shapefile to existing PostGIS table

Ogr2ogr is simply powerful.  It is like salt in cooking you use it everyday.

The task how to import a Shapefile into a PostGIS table that exists without overwriting the existing table and simply append some new geometries and match the attribute fields from the Shapefile to the PostGIS table fields.

There are lots of things I want to do in this import so lets list off what exactly:

  1. append new geometry to an existing PostGIS table from a source Shapefile
  2. specifiy the source Shapefile projection, because sometimes ogr2ogr guesses wrong
  3. define which attribute fields map to which PostGIS table fields
  4. specify a non default PostgreSQL schema, in my case the schema=geodata
  5. specify the geometry type for output destination as MultiLineString even though my input Shapefile is  a simple Linestring

Here is the monster long command line arguments for OGR2OGR:

ogr2ogr -update -append -fieldmap -1,-1,2 -a_srs EPSG:900913 -nlt MULTILINESTRING -lco "SCHEMA=geodata" -f PostgreSQL "PG:host=localhost port=5432 user=postgres dbname=mydb password=secret" -nln geodata.table_name shapefileName.shp

What it all says broken down.  First we specify -update and -append telling ogr2ogr that we want to add new data to an existing table.

 Next we map the fields of the Shapefile to the fields in the PostGIS table (  Please read the documentation but the quick rundown is that the fieldmap takes a comma separated list which represents each field in your source Shapefile.  The Shapefile in my case has 3 fields.  -1 means skip this field so we skip fields 1 and 2 and field 3 is what we want to import into the position 2 field of our destination PostGIS table counting from 0 as the first, 1 is the second and 2 is the third field.

-a_srs EPSG:900913 tells ogr2ogr specifically to use this projection for our source Shapefile
-nlt MULTILINESTRING   imports our geom as multilinestring
-lco "SCHEMA=geodata"   specifies our user defined PostgreSQL schema where the table will go
-f PostgreSQL is our long connection string to connect to the DB
-nln geodata.table_name specifies explicitly the name of the existing table we want to import into

Lastly comes our ShapefileName.shp  which is in the same folder that we currently are located in when running this command.

Wheew   now that is lot for one line of import but it works :)  yes


Wednesday, February 25, 2015

AutoCAD import x,y,z csv data points

How to import a text file of CSV data with x,y, z  3D points.

I though this might be hard but its really not.  The most work is in preparing the data.

  1. Create a clean CSV text file with only x,y,z values like this with NO headers:


          the first column is the X coordinate, second column is the Y coordinate, and the third column is the Z coordinate, all separated by a comma  ,

          it is important to note that I am using ACAD 2011 German (Deutsch).  This means your coordinates must be with a .  decimal separator NOT a comma, which is normally the case with German coordinates and numbers.

             2. Open ACAD with an empty template
             3. Click create POINT
             4. Copy the text file contents with STRG-C     or CTRL-C on english keyboard
             5. Then PASTE directly into the command line with STRG-V  or CTRL-V
             6. Hit escape
             7. Save your new DWG  with save
              8. DONE

          This was easy but not intuitive on my first attempt :)


          Tuesday, February 10, 2015

          Django GeoDjango deploy on windows server 2012 R2 IIS 8

          The battle is set me vs IIS on Windows Server 2012 R2!!

          Battle Description:  install django 1.7.4, geodjango on IIS 8.5

          Lets start with a BIG thanks to cstoker as  this was the best starting point to my adventure.  Please watch this video first.  Then follow along to install the remaining magic steps to get geodjango working on your windows server with IIS.

          1. Install OSGeo4W

          First you must INSTALL OSGeo4W  and install using custom methods to only select the "libs" gdal and co.  See GeoDjango install instructions here 

          2. Update environment variables

          Then all you need to update are the environment variables .  Like here Screenshots below !

          1.  WSGI_HANDLER                            django.core.wsgi.get_wsgi_application()
          2. GDAL_DATA                                   C:\OSGeo4W64\share\gdal
              PROJ_LIB                                         C:\OSGeo4W64\share\proj
               PATH                                                C:\OSGeo4W64\bin

          Interesting Links:

          1 -
          2 -
          4.  (Django specific)

          Here are some of my own text notes for easy copy paste:

          # add fast CGI Application

          # Full Path:

          # Arguments appname is the root for example the first  /django_proj/django_proj

          # add module mapping
          #request path enter * in the field
          # Module
          select fastcgi from list


          Django Handler

          # Click Request Restrictions
          go and UN-SELECT Mapping invoke handler only if request is mapped to:

          # windows 2012 server using plain jane python global install

          # EnvironmentalVariables Collection Editor
          # here you need to add all of these key : value pairs NOT including the space or :

          KEY                                                    VALUE
          DJANGO_SETTINGS_MODULE    appname.settings
          PYTHONPATH                                 C:\Inetpub\wwwroot\appname_root
          WSGI_HANDLER                            django.core.wsgi.get_wsgi_application()
          GDAL_DATA                                   C:\OSGeo4W64\share\gdal
          PROJ_LIB                                         C:\OSGeo4W64\share\proj
          PATH                                                C:\OSGeo4W64\bin



          Also if you are using bootstrap and the django-admin-bootstrapped you will need to add the font mime type to your web.config  file in the main folder.

           If you want to use the GUI click your site in IIS then click "MIME Types", then on the right click add, followed by entering File name extension to    .woff2   and the MIME type:   set too        font/x-woff2    click ok then done.

          <?xml version="1.0" encoding="UTF-8"?>
                      <add name="Django Handler" path="*" verb="*" modules="FastCgiModule" scriptProcessor="C:\Python27\python.exe|C:\inetpub\webs\utc_django\" resourceType="Unspecified" />
                  <httpErrors errorMode="Detailed" />
                      <mimeMap fileExtension=".woff2" mimeType="font/x-woff2" />

          Deploy Django Script to IIS

          Now once this MOOO fuss is done you can run this script on the server to deploy my code from the local subversion repository to the live IIS inetpub directory.

          BIG NOTE:  once done you NEED to REMOVE the Django Handler on the folder STATIC because after each new deploy this folder inherits the IIS Handler Mappings from the parent !!!!  Meaning your STATIC files do not work !