Welcome to Planet PostGIS

May 21, 2017

Boston GIS (Regina Obe, Leo Hsu)

PostGIS 2.4.0, Code Sprints and other extensions to try with PostgreSQL 10 beta1

So PostgreSQL 10beta1 came out recently as Holly mentioned. When first mention of beta hits, things start getting serious for me. I have to make sure that PostGIS compiles against said distribution to make sure eager testers aren't held back.

As with other releases, PostGIS didn't compile against the new PostgreSQL version without some nurturing. We've still got one regress failure, but at least PostGIS 2.4 now compiles cleanly against PostgreSQL 10beta1. I'm hoping that we can release PostGIS 2.4.0 just in time for PostgreSQL 10 planned release in September so I don't have to backport PostgreSQL 10 patches I made to lower PostGIS versions.

For PostGIS 2.4 the main focus will be cleaning up the parallel work so that all aggregate functions can enjoy use of parallel optimization. This is even more important with PostgreSQL 10 now that more kinds of queries can benefit from parallelization work. I'm also hoping to focus more energy on the raster side of things.

Continue reading "PostGIS 2.4.0, Code Sprints and other extensions to try with PostgreSQL 10 beta1"

by Regina Obe (nospam@example.com) at May 21, 2017 07:37 AM

April 24, 2017

BigSQL Holly

ETL (Extract, Transform, Load) PostGIS Data with ogr2ogr

In an earlier post, I showed you how to use the shp2pgsql tool to import shapefiles into PostGIS. But what if you need to import other geodata formats (e.g. GeoJSON, MapInfo, KML, CSV, etc.)?

ogr2ogr – ETL for PostGIS


Lucky for you, the BigSQL PostGIS distribution comes bundled with GDAL and the ogr2ogr (or as my geogeek friends like to call it “ogre to ogre”) command line tool.

From gdal.org/ogr2gr:

This program can be used to convert simple features data between file formats performing various operations during the process such as spatial or attribute selections, reducing the set of attributes, setting the output coordinate system or even reprojecting the features during translation.

In other words, ogr2ogr is your go to ETL tool for importing and exporting PostGIS data.

Exercise: Intro to ogr2ogr

In this exercise, we will import and export GeoJSON files to and from our postgis_sample database using ogr2ogr commands.

Exercise Prerequisites

The ogr2ogr command line tool is packaged with BigSQL’s PostgreSQL installers. If you have not installed the BigSQL PostgreSQL distribution and setup your PostGIS database, start here.


GeoJSON is an extension of JSON open standard format and designed for representing simple geographical features, along with their non-spatial attributes.


        "type": "Feature",
        "geometry": {
            "type": "Point",
            "coordinates": [125.6, 10.1]
        "properties": {
            "name": "Dinagat Islands"

Getting to know ogr2ogr Command Line

Open your command line utility and enter the this help command to see ogr2ogr parameters and usage:

ogr2ogr --long-usage

You should see the following parameter and under it a list of all the data formats your installation of ogr2ogr supports:

-f format_name

Make sure that the these two formats are included in the list:

-f "GeoJSON"
-f "PostgreSQL"

Import GeoJSON to PostGIS

For this exercise, I downloaded the NYC Wi-Fi Hotspot Locations GeoJSON file available at the New York City Open Data site.

To download this data click here: nyc_hotspots.geojson

To import a GeoJSON file into your PostGIS database, use the following command:

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.json" -nln new_table_name

If you want to append the new data to already existing records, use the -append flag:

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=postgres" "source_data.geojson" -nln destination_table -append

So, for our exercise run the following commands:

cd <location of nyc_hotspots.geojson>
ogr2ogr -f "PostgreSQL" PG:"dbname=postgis_sample user=postgres" "nyc_hotspots.geojson" -nln nyc_hotspots

Now, run the following sql query on your postgis_sample database to see the table’s attributes:

select * from public.nyc_hotspots;

Export from PostGIS to GeoJSON

To generate a geojson file from one of your PostGIS tables, run the following command.

ogr2ogr -f "GeoJson" out.geojson PG:"host=localhost dbname=postgis_sample user=postgres password=<your_password>" \  -sql "SELECT * from nyc_hotspots"

*Note: Although you can use ogr2ogr to import and export shapefile format to and from PostGIS, it is recommended you use the shp2pgsql tool I discussed in an earlier post.

by Holly Orr at April 24, 2017 12:37 PM

April 05, 2017

Boston GIS (Regina Obe, Leo Hsu)

Slides from PGConfUS 2017 PostGIS talk

At PGConfUS 2017 last week, we presented a talk: Top 10 Problems Solved by PostGIS. The slides for the talk in HTML format and PDF format. The pgRouting examples at the end of the talk seemed to be the most popular.

We'll be giving a pgRouting workshop at FOSS4G Boston 2017 Aug 14-19th where we'll go into much more depth about topics like drive time analysis and Vehicle Route schedule planning (VRP). We also hope to give a talk at FOSS4G 2017 on PostGIS spatial tricks.

by Regina Obe (nospam@example.com) at April 05, 2017 07:23 AM

Boston GIS (Regina Obe, Leo Hsu)

Google Cloud SQL for PostgreSQL supports PostGIS

Google recently put in beta Google Cloud SQL for PostgreSQL which targets PostgreSQL 9.6 and seems like they plan to stay in line with PostgreSQL offerings similar to the Amazon PostgreSQL RDS and Aurora offerings. I was curious to see what kind of extensions they support. In Google cloud blog article Cloud SQL for PostgreSQL for your mobile and Geospatial applications in Google Cloud PostGIS is explicitly mentioned as supported. Presumably because Descartes is one of their case studies which I can only guess probably uses PostGIS based on the quote from Tim Kelton, Descartes co-founder:

"Our individual teams, who are building micro services, can quickly provision a database on Cloud SQL. They don't need to bother compiling Geos, Proj4, GDAL, and Lib2xml to leverage PostGIS. And when PostGIS isn’t needed, our teams use PostgreSQL without extensions or MySQL, also supported by Cloud SQL."

Excerpted from their article:
Geospatial support: Easily enable the popular PostGIS extension for geospatial objects in Postgres.

It's unclear from the article what other extensions they support though. Maybe I'll have to try it.

by Regina Obe (nospam@example.com) at April 05, 2017 06:18 AM

March 26, 2017

Boston GIS (Regina Obe, Leo Hsu)

PGConfUS 2017 Getting Stuff done in PostGIS

A reminder, PGConfUS 2017 conference is just days away, and we'll be giving a training March 28th 2017, Jersey City, NJ at 1 PM. If you are coming, keep an eye on this page PGConf 2017 US Getting Stuff done with PostGIS materials.

If you haven't signed up already, there are still spots, make sure to buy your tickets at http://pgconf.us/conferences/2017#registration.

Continue reading "PGConfUS 2017 Getting Stuff done in PostGIS"

by Regina Obe (nospam@example.com) at March 26, 2017 04:42 AM

March 23, 2017

BigSQL Holly

Importing Shapefiles into PostGIS with shp2pgsql

Geojson may be catching up, but by and far the most popular data format for sharing geospatial data is (and has been since the early 1990’s) the shapefile. Although a shapefile is a simple way to share data that can be easily consumed by almost any GIS system, it comes with many limitations and is not suitable for your enterprise data needs.

My personal least favorite limitation is that shapefiles truncate column names greater than 10 characters – ouch!

Luckily, the PostGIS community has bundled with PostGIS distributions a great tool that simplifies the task of importing shapefiles into your PostgreSQL database: shp2pgsql.

Shapefile Definitions:

Vector Data: Points, lines, or polygons that are used to represent real-world features on a map. Used for data that has discrete boundaries like administrative boundaries (e.g. country borders), physical features (e.g. lakes or streams) or infrastructure (e.g. streets). Attributes in a table are associated with each vector feature. vector_small

Shapefile: The most popular geospatial vector data format. First developed by ESRI. Because of it’s open specification it is now predominantly used to share GIS data between open source and proprietary software. A shapefile actually consists of 3 mandatory files:

  • .shp — shape format; the feature geometry itself
  • .shx — shape index format; a positional index of the feature geometry to allow seeking forwards and backwards quickly
  • .dbf — attribute format; columnar attributes for each shape, in dBase IV format

Exercise: Shapefiles to PostGIS with shp2pgsql

Prerequisite: Before you begin, create a spatial database by following the instructions laid out in our previous post, PostGIS – How to create a Spatial Database with PGC Command Line.

Bundled with your BigSQL PostGIS distribution is a tool called shp2pgsql. In this exercise, we will download a shapefile, find the correct SRID code, and use the command line tool to upload it to our postgis_sample database.

Download the data

Over the past 10 years, most U.S. municipalities have enacted open data policies. This means you can download lots of geospatial data from government web sites. Most geospatial data is provided as a shapefile or geojson file.

For this exercise, I downloaded NYC borough boundaries shapefile available at the New York City Open Data site.

To download this data click here: nybb_17a

After downloading, unzip the files and put it on your drive somewhere you will remember.

Navigate to shp2pgsql

Before you begin, make sure shp2pgsql is installed in your bin directory:

cd <directory of installation>/pg96/bin 

shp2pgsql syntax

Syntax for running command line:

shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -U postgres -d <DBNAME>


< SRID > – The Spatial Reference System Identifier (SRID or ESPG) is a unique value that represents the data’s coordinate system.

Findng the SRID/ESPG code:

All geospatial data has a coordinate system it is projected in. The topic of projections is beyond the scope of this posting, but you can learn more here.

Navigate to the shapefile’s .prj file which contains information about the shapefile’s coordinate system.

  cd <location of unzipped nybb_17a directory> 

Option 1:
Upload the .prj file to prj2epsg.org

Option 2:
Open the .prj file in your favorite editor and put the values in the first parameter, PROJCS, into the following sql statement that you will run against the postgis_example database:

nybb.prj parameter:


SQL query:

    SELECT srid, srtext,proj4text FROM spatial_ref_sys 
    WHERE srtext ILIKE '%New_York_Long_Island%' 
    AND proj4text ILIKE '%nad83%' AND proj4text ILIKE '%ft%';  


The SRID for the nybb data is 2263.

<PATH/TO/SHAPEFILE> – The full path to the shapefile:

<location of unzipped nybb_17a directory>  

< SCHEMA > — Target schema where the new table will be created:

This is optional. 

< DBTABLE > — New database table to be created:


< DATABASE > —Target database where the table will be created:


For a complete listing of the shp2pgslq command line parameters go here.

Putting it all together

So, putting this all together for this exercise you would run the following command:

Note: before running the command make sure psql has been started.

cd <location of unzipped nybb_17a directory>
shp2pgsql -I -s 2263 nybb.shp nybb | psql -U postgres -d postgis_sample

Now, run the following sql query on your postgis_sample database:

select * from public.nybb;

And you will see the nybb table’s attributes:


by Holly Orr at March 23, 2017 01:51 PM

February 21, 2017

Stephen Mather

Taking Slices from LiDAR data: Part IX

Part 9 of N… , see e.g. my previous post on the topic.

We’ve been working to reduce the effect of overlapping samples on statistics we run on LiDAR data, and to do so, we’ve been using PDAL’s filters.sample approach. One catch: this handles the horizontal sampling problem well, but we might want to intentionally retain samples from high locations — after all, I want to see the trees for the forest and vice versa. So, it might behoove us to sample within each of our desired height classes to retain as much vertical information as possible.

by smathermather at February 21, 2017 03:26 AM

February 19, 2017

Stephen Mather

Taking Slices from LiDAR data: Part VIII

Part 8 of N… , see e.g. my previous post on the topic.

I didn’t think my explanation of sampling problems with LiDAR data in my previous post was adequate. Here are a couple more figures for clarification.

We can take this dataset over trees, water, fences, and buildings that is heavily sampled in some areas and sparsely sampled in others and use PDAL’s filters.sample (Poisson dart-throwing) to create an evenly sampled version of the dataset.

Figure showing overlap of LiDAR scanlines Figure showing data resampled for eveness
Figure showing overlap of LiDAR scanlines

Figure showing overlap of LiDAR scanlines

Figure showing data resampled for eveness

Figure showing data resampled for evenness

An extra special thanks to the PDAL team for not only building such cool software, but being so responsive to questions!

by smathermather at February 19, 2017 01:04 AM

February 16, 2017

Stephen Mather

Taking Slices from LiDAR data: Part VII

Part 7 of N… , see e.g. my previous post on the topic.

More work on taking LiDAR slices. This time, the blog post is all about data preparation. LiDAR data, in its raw form, often has scan line effects when we look at density of points.


This can affect statistics we are running, as our sampling effort is not even. To ameliorate this affect a bit, we can decimate our point cloud before doing further work with it. In PDAL, we have three choices for decimation: filters.decimation, which samples every Nth point from the point cloud; filters.voxelgrid, which does volumetric pixel based resampling; and filters.sample or “Poisson sampling via ‘Dart Throwing'”.

filters.decimation won’t help us with the above problem. Voxelgrid sampling could help, but it’s very regular, so I reject this on beauty grounds alone. This leaves filters.sample.

The nice thing about both the voxelgrid and the poisson sampling is that they retain much of the shape of the point cloud while down sampling the data:



We will execute the poisson sampling in PDAL. As many things in PDAL are best done with a (json) pipeline file, we construct a pipeline file describing the filtering we want to do, and then call that from the command line:

We can slice our data up similar to previous posts, and then look at the point density per slice. R-code for doing this forthcoming (thanks to Chris Tracey at Western Pennsylvania Conservancy and the LidR project), but below is a graphic as a teaser. For the record, we will probably pursue a fully PDAL solution in the end, but really interesting results in the interim:


More to come. Stay tuned.

by smathermather at February 16, 2017 04:46 AM

February 10, 2017

BigSQL Holly

PostGIS – How to create a Spatial Database with PGC Command Line

postgis_plus_bigsqlWe are excited to announce that BigSQL distributions of PostGIS now come bundled with several popular community spatial extensions:

This is the only community distribution to offer PostGIS with these extensions across ALL platforms (Mac, Linux, and Windows)! So, for all you GeoGeeks using Linux / Mac – spatially enabling your PostgreSQL database with PostGIS functionality just got a lot easier.

In addition, we’ve included a sample script to help guide you through creating your first PostGIS database.

In the upcoming weeks, we will offer guidance for both GUI and command line work flows (see Are you a Yoda or a Diego?) and blogging about what you can do with spatially enabled data.

So, let’s get started…

What is PostGIS?

PostGIS is powerful spatial database extender for PostgreSQL. It adds support for geographic objects allowing location queries to be run in SQL. For a full list of supported features look here: http://postgis.net/features

How to install the BigSQL distribution

If you have installed the BigSQL distribution, skip to the next section, “Getting started with BigSQL PGCLI.”

Getting started with BigSQL PGCLI

PGCLI (Pretty Good Command Line Interface) is a package and update manager, similar to yum or apt-get, specifically for PostgreSQL and related community projects. PGC enables you to easily find and install packages, configure multiple versions of PostgreSQL, update existing applications, and control services by starting and stopping from the command line.

You can run pgc commands with the following call:

 cd <directory of installation>
 ./pgc (Linux and Mac)
 pgc (Windows)

For the purpose of this tutorial, we will be using the Linux/Mac syntax: ./pgc

To learn more about PGC, go to the BigSQL PGCLI tutorial.

Update components to latest versions and install PostGIS

  1. Open a terminal and navigate to the installation directory.

  2. On windows it could be C:\Postgres, on Linux and Mac it could be any directory based on the installation method. You will be able to locate “pgc” bash script OR “pgc.bat” file in this directory.

     cd <directory of installation>
  3. Confirm that pgc has been installed by running a list command:

     ls (Linux/Mac)
     dir (Windows)


     bam2        data        logs        pgadmin3    pgc
     conf        hub         pg96        pgbadger    uninstall
  4. Run the pgc status command to show your current Postgres server status.

    In this example Postgres 9.6 and the BigSQL Manager are running on the default ports:

     ./pgc status
        pg96 running on port 5432
        bam2 running on port 8050  

    If your Postgres instance is not running, start it with the following pgc command:

     ./pgc start
  5. Run the pgc list command to show what Postgres version(s) and components you currently have installed and update to latest versions:

     ./pgc list
     ./pgc update
  6. Install PostGIS:

     ./pgc install postgis23-pg96
  7. Run list command again to see that PostGIS is now installed:

     ./pgc list
      Extensions     postgis23-pg96        2.3.2-1            2017-02-09   Installed      

Create a PostGIS sample database

We have provided you with a sql script that will create a Postgis enabled database and includes the following extensions:

Before you begin, navigate to directory that contains the sql script you will run to create a PostGIS database and check to make sure the file create_postgis_sample_db.sql downloaded with your install. You can also take a look at what is in the script using your favorite editor tool:

 cd <directory of installation>/pg96/share/doc/postgresql/extension 

 vi create_postgis_sample_db.sql

Launch psql and run the script

Run the script using the psql terminal.

Linux / OSX:

 cd <directory of installation>/pg96
 source pg96.env
 psql -U postgres -d template1 -f share/doc/postgresql/extension/create_postgis_sample_db.sql


 cd <directory of installation>\pg96\bin
 psql -U postgres -d template1 -f ..\share\doc\postgresql\extension\create_postgis_sample_db.sql

Check to see that the database and extensions were successfully created:

 psql (Mac / OX)
 psql -U postgres -d template1 (Windows)
 postgres=# \connect postgis_sample;
 You are now connected to database "postgis_sample" as user "postgres".

                 List of installed extensions
                 Name             | Version |   Schema               
     address_standardizer_data_us | 2.3.2   | public     
     fuzzystrmatch                | 1.1     | public     
     ogr_fdw                      | 1.0     | public     
     plpgsql                      | 1.0     | pg_catalog 
     postgis                      | 2.3.2   | public     
     postgis_tiger_geocoder       | 2.3.2   | tiger      
     postgis_topology             | 2.3.2   | topology   
    (8 rows)

Exit psql:


Install pgadmin3 if it is not already installed:

 cd <directory of installation>  
 ./pgc install pgadmin3

Launch pgadmin3 (the GUI should popup when you run this command):

 ./pgc start pgadmin3    

Output should look something like this:


Get me outta here! (CLI edition):

You can always uninstall your BigSQL Postgres distribution at any time with the following commands:

 cd <directory of installation>
 ./pgc stop
 cd uninstall
 open uninstall.app

Finally, cd to location of your PostgreSQL directory and delete the PostgreSQL directory and it’s contents:

 rm -r <directory of installation> (Linux or Mac)
 rmdir <directory of installation> /s (Windows)

by Holly Orr at February 10, 2017 02:18 PM

February 08, 2017

BigSQL Holly

Are you a Yoda or a Diego?

In the geospatial world there are usually 2 types of people supporting spatially enabled databases:

diego 1. GIS analysts  (scientist, statisticians, analysts, etc) who find themselves as the de facto DBA (among other things). “I don’t have time for this! I’m saving this sea turtle. Just give me a GUI!”

yoda 2. DBAs who find themselves supporting these strange spatially enabled databases. “Real men only use command line! And what the hell is GIS?”

arcinfo Of course, some of you old school GISers may really love to geek out on the CLI (ArcI/Info anyone?).

But GIS has come a long way since 1982. For one thing, the learning curve for running GIS analysis is becoming less intimidating due to new SaaS geospatial companies like our friends at Carto. For another, open source (FREE!) server and desktop software has caught up with (and in some cases surpassed) its proprietary counterparts (see out post “Why are you still paying for your GIS“).

At BigSQL, we want you to know we love you for who you are. And in that spirit, just in time for the release of PostgreSQL 9.6.2 and PostGIS 2.3.2, we are introducing tools to help you build and monitor your spatially enabled databases that will satisfy whichever tribe you belong to: the GUIs or the CLIs.

In our next post we will help Tribe Yoda (aka CLIs) leverage the PGCLI: Pretty Good Command Line Interface to make installing, updating, configuring, and monitoring PostgreSQL/PostGIS simple and FREE!

by Holly Orr at February 08, 2017 08:25 PM

January 31, 2017

PostGIS Development

PostGIS 2.3.2 Released

The PostGIS development team is pleased to announce the release of PostGIS 2.3.2 As befits a patch release, the focus is on bugs and breakages. Best served with PostgreSQL 9.6.1+ and pgRouting 2.3.2.

Continue Reading by clicking title hyperlink ..

by Regina Obe at January 31, 2017 12:00 AM

January 30, 2017

PostGIS Development

PostGIS 2.2.5 Released

The PostGIS development team is pleased to announce the release of PostGIS 2.2.5 As befits a patch release, the focus is on bugs and breakages.

Continue Reading by clicking title hyperlink ..

by Regina Obe at January 30, 2017 12:00 AM

January 23, 2017


Meilleurs vœux 2017 ! Best wishes for 2017 !

(english below)

Meilleurs voeux 2017

Toute l'équipe d'Oslandia vous présente ses meilleurs voeux pour cette nouvelle année pleine de découvertes, de richesses, de vitalité et d'Open Source.

L'année 2016 fût rythmée par nos participations

  • aux diverses conférences : FOSS4G 2016, PG Session #8, Rencontre QGIS
  • Code Sprints : OSGeo "C-tribe", QGIS Server

Nous avons contribué aux différents outils dans les domaines

  • de la 3D : py3DTiles, iTowns, building server, pgmorton, SFCGAL/PostGIS
  • des transports : Tempus
  • de la gestion des réseaux d'eau : QWAT
  • des web services : mod_pep

et de QGIS : QGIS Server multithreadé, meshlayer, fonction de loupe sur carte, gestion des diagrammes, etc..

L'année 2016 a donc été riche et nous souhaitons faire encore mieux pour 2017. Nous allons continuer à vous accompagner sur tout besoin en SIG Open Source, 3D et données spatiales. Et ça démarre fort avec le recrutement de nouveaux collaborateurs et des projets innovants!

A très bientôt,

L'équipe Oslandia

Pour toute information ou demande : infos@oslandia.com

Happy new year 2017

The whole team at Oslandia sends you its best wishes for this new year, full of innovation, prosperity, energy and Open Source.

2016 was marked with our involvement in

  • conferences: FOSS4G 2016, PG Session #8, QGIS Meeting
  • Code Sprints: OSGeo "C-tribe", QGIS Server

We have contributed tools in numerous fields, e.g.

  • 3D : py3DTiles, iTowns, building server, pgmorton, SFCGAL/PostGIS
  • transportation: Tempus
  • water network management: QWAT
  • web services: mod_pep

and QGIS: QGIS Server multithreading, meshlayer, magnifying glass on map features, diagrams management, etc..

2016 was full of promises and we are committed to doing even better in 2017. We will continue to collaborate with you in Open Source GIS, 3D and spatial data. New collaborators and innovative projects: this new year starts with exciting perspectives !

Stay tuned!

Oslandia Team

For any information : infos@oslandia.com

by Vincent Picavet at January 23, 2017 11:00 AM

December 11, 2016

Paul Ramsey

PostgreSQL "Compatible" Aurora

PostgreSQL "Compatible" Aurora

While I know full well that Amazon’s marketing department doesn’t need my help, I cannot resist flagging this new development from the elves in Santa’s AWS workshop:

Today we are launching a preview of Amazon Aurora PostgreSQL-Compatible Edition. It offers … high durability, high availability, and the ability to quickly create and deploy read replicas. Here are some of the things you will love about it:

Performance – Aurora delivers up to 2x the performance of PostgreSQL running in traditional environments.

Compatibility – Aurora is fully compatible with the open source version of PostgreSQL (version 9.6.1). On the stored procedure side, we are planning to support Perl, pgSQL, Tcl, and JavaScript (via the V8 JavaScript engine). We are also planning to support all of the PostgreSQL features and extensions that are supported in Amazon RDS for PostgreSQL.

Cloud Native – Aurora takes full advantage of the fact that it is running within AWS.

The language Amazon uses around Aurora is really wierd – they talk about “MySQL compatibility” and “PostgreSQL compatibility”. At an extreme, one might interpret that to mean that Aurora is a net-new database providing wire- and function-level compatibility to the target databases. However, in the PostgreSQL case, the fact that they are additionally supporting PostGIS, the server-side languages, really the whole database environment, hints strongly that most of the code is actually PostgreSQL code.

There is not a lot of reference material about what’s going on behind the scenes, but this talk from re:Invent shows that most of the action is in the storage layer. For MySQL, since storage back-ends are pluggable, it’s possible that AWS has added their own back-end. Alternately, they may be running a hacked up version of the InnoDB engine.

For PostgreSQL, with only one storage back-end, it’s pretty much a foregone conclusion that AWS have taken a fork and added some secret sauce to it. However, the fact that they are tracking the community version almost exactly (they currently offer 9.6.1) indicates that maybe their fork isn’t particularly invasive.

I’d want to wait a while before trusting a production system of record to Aurora PgSQL, but the idea of the cloud native PostgreSQL, with full PostGIS support, excites me to no end. RDS is already very very convenient, so RDS-with-better-performance and integration is just icing on the cake for me.

I, for one, welcome our new cloud database overlords.

December 11, 2016 04:09 PM

December 09, 2016

Boundless Geo

LocationTech Tour 2016

If you caught our update on FOSS4G 2016 you know we enjoy taking part in community events and open source advocacy. The LocationTech Tour is the second big open source GIS mapping conference & community event we take part in; …

The post LocationTech Tour 2016 appeared first on Boundless Geo.

by Jody Garnett at December 09, 2016 05:13 PM

Boundless Geo

LocationTech Tour 2016

If you caught our update on FOSS4G 2016 you know we enjoy taking part in community events and open source advocacy. The LocationTech Tour is the second big open source GIS mapping conference & community event we take part in; …

The post LocationTech Tour 2016 appeared first on Boundless Geo.

by Jody Garnett at December 09, 2016 05:13 PM

November 28, 2016

PostGIS Development

PostGIS 2.3.1 Released

The PostGIS development team is pleased to announce the release of PostGIS 2.3.1. Best served with pgRouting 2.3.1 and PostgreSQL 9.6.1.

As befits a patch release, the focus is on bugs and breakages.

Continue Reading by clicking title hyperlink ..

by Regina Obe at November 28, 2016 12:00 AM

November 26, 2016

PostGIS Development

PostGIS 2.2.4 Released

The PostGIS development team is pleased to announce the release of PostGIS 2.2.4 As befits a patch release, the focus is on bugs and breakages.

Continue Reading by clicking title hyperlink ..

by Regina Obe at November 26, 2016 12:00 AM

November 23, 2016

Postgres OnLine Journal (Leo Hsu, Regina Obe)

ODBC FDW now supports 9.5 and 9.6

A while ago when Foreign Data Wrappers in PostgreSQL was a fairly new thing, we talked about the ODBC_FDW foreign data wrapper. Since then, people have been asking us how to get the ODBC FDW to work on newer PostgreSQL. Sadly the ODBC_FDW was stuck in time not having updated to newer FDW API standards. Our recommendation was just to use OGR_FDW, which many distributions both Linux and Windows have compiled OGR_FDW with ODBC support. True that OGR_FDW is coined as a spatial data wrapper, but the reality is spatial data rarely lives apart from regular attribute data so a good spatial vector driver supports both vector data and bread and butter data types. OGR_FDW is still our go to for working with spreadsheets and folders of CSV files.

Recently the fine folks at Carto patched the ODBC FDW to work with PostgreSQL 9.5. I do hope they accept my modest patch to make it work with PostgreSQL 9.6 as well. So now 2 FDWs to choose from for connecting to ODBC datasources. Which one is better? The answer as most always is IT DEPENDS.

Continue reading "ODBC FDW now supports 9.5 and 9.6"

by Leo Hsu and Regina Obe (nospam@example.com) at November 23, 2016 05:39 AM

November 08, 2016



py3dtiles is a Python module aiming to manage the 3DTiles format.

For now, only the Point Cloud specification is supported but some development are expected soon to extend the coverage of the library to other parts of the specification!

The module is still in development but an early version is already available on Pypi here.

Who's using py3dtiles?

py3dtiles is currently used by LOPoCS, a server streaming Point Cloud from Postgis, to send data to Cesium.

Open Source

Everything is on github : https://github.com/Oslandia/py3dtiles


Please let us know if you are interested in development, training or consulting around these pieces of technology.

Do not hesitate to contact us at infos@oslandia.com.

by Paul Blottiere at November 08, 2016 09:00 AM

November 03, 2016


LOPoCS : Stream your Point Cloud from Postgis

LOPOCS is a point cloud server written in Python, allowing to load Point Cloud from Postgis thanks to the pgpointcloud extension.

Point Cloud are loaded in database thanks to PDAL and patches are compressed in LAZ. To stream hundreds of millions of points with LOD in an effective way, points are ordered with the Midoc algorithm and a revert Morton code is used when too many patches are involved!

The Potree viewer is currently able to display points coming from LOPoCS through a simple HTTP protocol.

Open Source

Some development has been in made in various projects:

Everything is on github : https://github.com/LI3DS/


LI³DS is an OpenSource project by Oslandia and IGN for 3D scanning and data management. LOPoCS is the result of a great collaboration, in particular with Rémi Cura and Mathieu Brédif.

And plenty of new people in perspective!


Please let us know if you are interested in development, training or consulting around these pieces of technology.

Do not hesitate to contact us at infos@oslandia.com.

by Paul Blottiere at November 03, 2016 09:00 AM

November 02, 2016


Rencontres QGIS utilisateurs les 1er et 2 décembre 2016 à Montpellier

Cette année encore, l'OSGEO-fr et Montpellier SupAgro organisent deux jours de rencontres dédiées aux utilisateurs de QGIS.

Chez Oslandia nous sommes fiers d'être parmi les mécènes de cet évènement cette fois encore.

Le thème des conférences est cette année :

QGIS, un outil adapté à mon métier?

Nous serons présents pour vous rencontrer et parler de QGIS et des applications métier de gestion de l'eau à destination des collectivités (Eau potable, Assainissement).

L'annonce de l'évènement

L'appel à contribution

Les inscriptions ouvrent cette semaine, réservez vos créneaux !

by Régis Haubourg at November 02, 2016 10:00 AM

October 17, 2016

BigSQL Holly

Two great things that taste great together: PostGIS and PostgreSQL

In my past life as a geospatial DBA, I had to navigate users and managers who belonged to different database teams (PostgreSQL, MySQL, SQL Server, Oracle, etc). When I was lucky enough to work in a shop that supported open source solutions, I was often asked by members of team MySQL, Why should we use PostgreSQL as our geospatial database when MySQL has spatial data types?” 

The answer: PostGIS.

Sure, MySQL has the ability to run spatial analysis (with some prodding). But PostgreSQL + PostGIS wins in:

  • functionality
  • performance
  • adoption by 3rd party solutions (QGIS, ArcGIS Server, GeoServer…)
  • So if you like to do things the free and easy way, go with PostgreSQL and PostGIS.

    by Holly Orr at October 17, 2016 02:59 PM

    October 10, 2016

    UpStats blog (Stefan Petrea)

    Off the streets, Land subdivision in PostGIS - Part 2


    In a previous post, we've seen how PostGIS and Openstreetmap can be used to leverage geographical data.

    A common task in GIS(in particular land use planning) is land subdivision. This involves taking different shapes, polygons usually, and cutting them into smaller polygons.

    This post will focus on describing an algorithm for partitioning a land polygon into parcels of a given area. Parcels will be cut off from the initial polygon until no more parcels can be formed.

    This is part2 of a series:

    Algorithm description

    We have a polygon P and the nearest road R. We get the bounding box B for P and all our searches for cutting points/lines will be confined to B. We compute the extreme points on the bounding box and we label them with the cardinal directions they represent.

    Our goal is to cut a corner C (also called subdivision) from P such that it contains the nearest boundary point to a road. The cut will be done using two lines, one horizontal and one vertical. We want C to be of a given area A.

    Now in order to find the corner to cut, we look at the extreme points and check which one of them is closest to a road.

    We'll use sweeping-lines to find the parcel we need. Any sweeping-lines mentioned will be moving away from the corner (in other words, away from the closest road point).

    In what follows, we assume the north-west corner needs to be cut.

    We place an inset (a horizontal line) that will be located sqrt(A) to the south (relative to the north edge). The inset is positioned there because we anticipate the target area to be in the form of a square.

    If the area above the inset (the one we aim for) is larger than our target, we split the polygon, take the upper half and use another sweeping line that goes from west to east, to find another cutting line that allows us to find a parcel with the required area.

    If the area above the inset is insufficient (below the target area), we search for a better position for it, using binary search, along the north-south direction.

    Additional details: The way the cut search works, using the inset, is such that we avoid getting thin horizontal strips when our initial polygon is a square/rectangle (and it is expected to be a square in the vast majority of cases).

    Details about corner cases (other than NW which was covered above):

    • NE corner: horizontal goes north->south and vertical goes east->west
    • SE corner: horizontal goes south->north and vertical goes east->west
    • SW corner: horizontal goes south->north and vertical goes west->east

    So the sweep lines always move away from the corner.

    After the parcel with target area was found, it will be cut off from the polygon, the original polygon in the GIS database will be updated and the new parcel will be inserted (separate from the original polygon that we split).


    OSM was used to extract test data. Specifically, in the run below, data for the Herastrau Park in Bucharest was used. In OSM, the park is represented as a series of polygons, and one of the polygons was partitioned in parcels, each measuring 8000 square meters.

    On the left side you can see the actual partition. On the right side, the same partition is displayed, except we also have some of the objects used throughout development, including bounding boxes, and extreme boundary points.

    And to visualize how the algorithm works, here's an animation of how it partitions the polygon, parcel by parcel:


    • While working on this algorithm implementation, at one point, it was required to find the extreme boundary points and assign them cardinal orientation labels. A first approach to determining the cardinal direction of the boundary points was to order them by ST_Azimuth relative to the centroid of the polygon (as the circle center passed to ST_Azimuth). This is a pitfall because the clock-wise order doesn't guarantee in any way membership to the N,E,S,W edges of the bounding box. It's perfectly possible that two such extreme points belong to the north edge, and one belongs to the west edge, and one to the south edge.
    • As it turns out, splitting the polygon multiple times, and using ST_Union to glue the remaining parts back together will create MULTIPOLYGON structures and which ST_ExteriorRing is not compatible with. So a convex hull was used in order to get one single polygon and compute the bounding box for it. This problem will surface after repeatedly cutting the input polygon.
    • Due to lack of knowledge about QGIS, some logic for an SVG-based 1 visualization was written in order to check the position of the cuts and the shape of the polygon after the cuts were made. Although QGIS has virtual layer feature, that was quite hard to use.
    • Early on, there was a need to visualize some objects from OSM in order to assess if they are fit for test data. It was easier to create a VIEW of those objects in PostgreSQL and then visualize that in QGIS (it was a bit hard to deal with all the menus in QGIS)
    • It would've been nice to have some automatically generated test polygons for each position of the road relative to the polygon. And some polygons that would be thiner near the top and wider near the bottom. However, the polygons extracted from OSM were used instead.
    • To get the extreme points of the polygon, the first approach used was to intersect exterior ring of the polygon with the exterior ring of the polygon's envelope (envelope meaning bounding box). This proved to be complicated and would definitely add complexity if one of the polygon's edges were axis-parallel as the result of the intersection would've been a line, and not a point. So the second approach was much simpler, just decomposing the polygon into its vertices (using ST_DumpPoints) and then picking the northmost, eastmost, westmost and southmost vertices. While this works, it only works for POLYGON and MULTIPOLYGON but it's not expected to work for CURVEPOLYGON.


    While implementing the logic for each corner-cut, it became more obvious that the logic for one corner would be enough, and the entire drawing could be rotated, the cut made, and then the resulting pieces rotated back. This could have decreased the size of the implementation.

    No strict constraints are made on the shape of the parcels. Most of them are square, but some diverge from that shape quite a lot.

    Some polygons may have U-shapes and if the closest-point on the boundary is one of the two tips of the U shape, the parcel that gets cut could turn out to be one made up of disconnected pieces.

    Another gap that was not covered by the initial spec and is not currently handled is the row-excess which can form at the end of a row (in the example it will be at the end of the row). This row-excess is another factor that can generate thin parcels because once formed, the next split will use this excess and add a very thin portion beneath it to form the new parcel. This is not very helpful, but it's a known issue.

    Someone pointed out that there should also be access paths/roads but that wasn't part of the problem statement. Another aspect that was not taken into consideration was surface inclination, it's assumed that subdivided area is flat.


    In this post we've seen how PostGIS can be leveraged to build a land subdivision algorithm. The implementation for this algorithm is available on Github under MIT license.



    Satoshi Koda has a great blog post about this, and that was very useful reading while writing the SVG visualization for this project. His new blog is hosted here.

    October 10, 2016 09:00 PM

    October 06, 2016

    PostGIS Development

    PostGIS 2.2.3 Released

    The PostGIS development team is pleased to announce the release of PostGIS 2.2.3 As befits a patch release, the focus is on bugs and breakages.

    Continue Reading by clicking title hyperlink ..

    by Regina Obe at October 06, 2016 12:00 AM

    October 04, 2016

    BigSQL Holly

    Why are you still paying for your GIS when you can get it for free?

    If you haven’t switched to open source solutions (FREE!), then you have probably fallen for some common misconceptions/myths:

    MYTH: Open source tools are buggy.

    Does software have bugs? Of course. It was made by humans! But open source has the benefit of a strong community that can crowdsource fixes because the code base is not proprietary (open source!) And, by the way, If you have been using the most popular proprietary GIS software (wink,wink) for more than 5 years, you know all about bugs.

    MYTH: Free GIS software has a limited toolbox for analysis.

    Well this depends. Let’s refer to the 80/20 rule here. When applied to GIS, 80% of your analysis can be processed with 20% of the tools / algorithms available for a spatially enabled database. If you are a bada$$ spatial stats expert and love your proprietary tools, then by all means stick with that expensive stack (also take a look at PySAL). But if you are like most of us (roughly 80%), you can accomplish your analysis with the FREE stack.

    MYTH: Open source tools are impossible to install and administer.

    Granted, this has been true in the past. But the open source community has made great strides in creating tools that don’t require you to have an engineering degree to stand-up a fully functioning GIS stack. And, because the community is large (and committed), you can find a ton of tutorials and documentation on the web.

    MYTH: PostGIS is fine for hobbyists, but it can’t support corporate needs.

    Actually, more and more companies and government agencies are turning to PostgreSQL/PostGIS for their geospatial needs: Uber, FourSquare, NOAA, and Carto just to name a few.

    In upcoming posts we will show you how to install new open source (FREE!) tools from BigSQL (powered by OpenSCG) to help you build your open source GIS stack.

    Just think of all the cool stuff you can buy from the Public Lab with the money you will save…

    by Holly Orr at October 04, 2016 02:26 PM

    September 26, 2016

    PostGIS Development

    PostGIS 2.3.0 Released

    The PostGIS development team is pleased to announce the release of PostGIS 2.3.0.
    This is the first version to utilize the parallel support functionality introduced in PostgreSQL 9.6. As such, if you are using PostgreSQL 9.6, we strongly encourage you to use this version.

    Parallel support will make many queries using PostGIS relationship operators and functions faster. In order to take advantage of parallel query support, make sure to set max_parallel_workers_per_gather to something greater than 0 as noted in max_parallel_workers_per_gather PostgreSQL runtime configs

    Best served with [PostgreSQL 9.6+] which is due out this week and pgRouting 2.3.0 which also just got released.

    Packages from maintainers will be out in the coming days and weeks.

    Continue Reading by clicking title hyperlink ..

    by Regina Obe at September 26, 2016 12:00 AM

    September 21, 2016


    Back from FOSS4G 2016 - part 2

    FOSS4G, the main international OSGeo event, takes place once per year and gather all folks involved in opensource geospatial technologies at large.

    This year, the conference took place in Bonn, Germany, and gathered around 900/1000 people. This edition was really good, as always for this conference. A lot of social events allowed direct talks to passionate people from all over the world, and the presentations were diverse and of high quality.

    Oslandia at FOSS4G

    Oslandia is a full player in the OpenSource GIS field, and we participated this year with various interventions.

    QGIS 3: plans, wishes and challenges

    QGIS is a very powerful GIS environment. More and more features have kept coming in the 2.0 branch, thanks to a growing number of users, developers and funders. But it is occasionally time to look up and envision the future to make sure this growth of energy is used at its full potential, especially to make sure new features are not added to a base that will become hard to maintain or evolve. Discussions and active work have already been done about how to transition away from the obsolescence of Python 2 and Qt 4. Some API breaks will have to occur and this is an opportunity to include major changes, both for users and for developers. This talk will present some of the changes that are planned or wished for the 3.0 version of QGIS and will detail challenges that remain to see them exist, from a technical, organisational or economical point of view.

    Slides: qgis_3.pdf.

    Using PostGIS in a real advanced way !

    A lot of people use PostGIS as a basic GIS toolbox, but very few use it in a real advanced way.

    To progress towards full PostGIS power, we can first make use of advanced native PostGIS functions. Using some extensions related to PostGIS, such as SFCGAL (for 3D data management), PostGIS Raster, PgPointCloud or even the latest pgsql-postal (for address normalization)...

    Then we can mix PostGIS functions with advanced standardized SQL features provided by PostgreSQL 9.x itself (CTE, Window functions, FDW, join and aggregate pushdowns…).

    Even better, use PostgreSQL bindings for data analysis languages such as R or Python to create your own dedicated function set, and integrate them into your SQL queries.

    Slides are available on github.

    iTowns, a new framework for 3D web visualization

    We present iTowns, a web framework developed in Javascript / WebGL for 3D geospatial data visualization, with capabilities for precise measurement directly in the browser. The first use case of iTowns is Street-view data type visualization : immersive images, but also terrestrial LIDAR Point Cloud data. But iTowns now supports much more data types :

    • Oriented images
    • Panoramic images
    • Point Clouds
    • 3D textured models
    • WFS vector data

    iTowns OpenSource is the descendant of the initial iTowns software developed at MATIS research laboratory of the French National Mapping Agency. iTowns OpenSource version 1.0 has been released in February 2016.

    The framework allows to : - Visualize projected images on a mesh ( cube, 3D model) - Visualize panoramic images - Display depth panoramic images - Display extruded building ( from WFS, other sources ) - Navigate in 3D (click & go) - Display Point Clouds - Visualize textured 3D models ( B3D, 3DS) - Use a simple API

    We detail iTowns features with videos. The data showcased was acquired by IGN's Stereopolis car. Aside from presenting the software, its present state and the future 2.0 version, we also explain the project history, which is an interesting case of technology transfer from research to industry.


    OpenSource tools for water network management

    This presentation details some OpenSource tools dedicated to water network management, be it for water distribution or wastewater networks.

    The qWAT project is a specific tool based on QGIS and PostGIS. it aims at managing water distribution networks. The data model is part of the project and covers most use cases for this kind of assets. The qWAT project is strongly linked to QGIS, and tries to contribute to the core of QGIS so as to mutualize developments and features among other QGIS-based applications.

    Similarly, the QGEP project is dedicated to wastewater networks. We also present a use case for an implementation of a wastewater information system in France, based on QGIS and PostGIS.

    Furthermore, we show how PostGIS-based projects allow to do network and graph analysis, so as to extract meaningful information for decision-taking and planning.

    QGIS-Epanet and QGIS-SWMM are two QGIS Processing extensions integrating simulation features on water distribution and wastewater networks. They let the user run simulations to analyze the network, dimensioning, and identify specific issues.

    These set of tools show that OpenSource GIS now tend to fulfill use cases for specific fields of application, and water management is among them.


    Want to see more videos? The whole conference is available here.


    by Audrey Malherbe at September 21, 2016 04:00 PM

    September 20, 2016


    Back from FOSS4G 2016 - part 1

    FOSS4G, the main international OSGeo event, takes place once per year and gather all folks involved in opensource geospatial technologies at large.

    This year, the conference took place in Bonn, Germany, and gathered around 900/1000 people. This edition was really good, as always for this conference. A lot of social events allowed direct talks to passionate people from all over the world, and the presentations were diverse and of high quality.

    Oslandia at FOSS4G

    Oslandia is a full player in the OpenSource GIS field, and we participated this year with various interventions.

    Workshop on Point Cloud data

    This workshop features the use of large amounts of Point Cloud data. We talked about databases and teached how to use PostgreSQL, PostGIS, PgPointCloud and PDAL at their best to manage your Point Clouds data. Workshop steps include :

    • Presentation of the components and their principles
    • Getting the components ready
    • Loading the point cloud data inside the database
    • Data manipulation with PDAL
    • Querying the point cloud data in the database
    • Mixing point cloud data with other data type ( 2D, 3D)
    • Performance issues & indexing
    • Visualizing your data

    Workshop materials are available on github.

    Workshop on 3D data

    This workshop was an introduction to 3D geospatial infrastructure. It leads you to serving 3D data from a spatial database to a 3D web visualization client. The Open Source components used in this workshop : PostGIS, to store and manipulate 3D data ( buildings ) building-server, a web server streaming 3D data and iTowns, the 3D web visualization framework.

    This video was recorded during the workshop.

    Workshop materials are available github :.

    A post to follow will give you some informations about our presentations.

    by Audrey Malherbe at September 20, 2016 04:00 PM

    September 19, 2016

    PostGIS Development

    PostGIS 2.3.0rc1 Released

    PostGIS 2.3.0rc1 is feature complete, so we’re looking for testing and feedback! Best served with PostgreSQL 9.6rc1 and pgRouting 2.3.0-rc1

    Please give this release candidate a try and report back any issues you encounter. New things since 2.3.0beta1 release

    Please report bugs that you find in this release.

    Important / Breaking Changes

    • 3466, Casting from box3d to geometry now returns a 3D geometry (Julien Rouhaud of Dalibo)

    • 3604, pgcommon/Makefile.in orders CFLAGS incorrectly leading to wrong liblwgeom.h (Greg Troxel)

    • 3396, ST_EstimatedExtent, now throws WARNING instead of ERROR (Regina Obe)

      New Features and Performance Enhancements

    • Add support for custom TOC in postgis_restore.pl (Christoph Moench-Tegeder)

    • Add support for negative indexing in STPointN and STSetPoint (Rémi Cura)
    • Numerous new function additions and enhancements: New Functions and Enhancements

    • 3549, Support PgSQL 9.6 parallel query mode, as far as possible (Paul Ramsey, Regina Obe)

    • 3557, Geometry function costs based on query stats (Paul Norman)
    • 3591, Add support for BRIN indexes (Giuseppe Broccolo of 2nd Quadrant, Julien Rouhaud and Ronan Dunklau of Dalibo)
    • 3496, Make postgis non-relocateable (for extension install), schema qualify calls in functions (Regina Obe) Should resolve once and for all for extensions #3494, #3486, #3076

    • 3547, Update tiger geocoder to support TIGER 2016 and use http or ftp (Regina Obe)

    See the full list of changes in the news file and please report bugs that you find in the release. Binary packages will appear in repositories over the coming weeks as packagers roll out builds.

    View all closed tickets for 2.3.0.

    by Regina Obe at September 19, 2016 12:00 AM

    September 18, 2016

    Anita Graser (Underdark)

    Movement data in GIS: issues & ideas

    Since I’ve started working, transport and movement data have been at the core of many of my projects. The spatial nature of movement data makes it interesting for GIScience but typical GIS tools are not a particularly good match.

    Dealing with the temporal dynamics of geographic processes is one of the grand challenges for Geographic Information Science. Geographic Information Systems (GIS) and related spatial analysis methods are quite adept at handling spatial dimensions of patterns and processes, but the temporal and coupled space-time attributes of phenomena are difficult to represent and examine with contemporary GIS. (Dr. Paul M. Torrens, Center for Urban Science + Progress, New York University)

    It’s still a hot topic right now, as the variety of related publications and events illustrates. For example, just this month, there is an Animove two-week professional training course (18–30 September 2016, Max-Planck Institute for Ornithology, Lake Konstanz) as well as the GIScience 2016 Workshop on Analysis of Movement Data (27 September 2016, Montreal, Canada).

    Space-time cubes and animations are classics when it comes to visualizing movement data in GIS. They can be used for some visual analysis but have their limitations, particularly when it comes to working with and trying to understand lots of data. Visualization and analysis of spatio-temporal data in GIS is further complicated by the fact that the temporal information is not standardized in most GIS data formats. (Some notable exceptions of formats that do support time by design are GPX and NetCDF but those aren’t really first-class citizens in current desktop GIS.)

    Most commonly, movement data is modeled as points (x,y, and optionally z) with a timestamp, object or tracker id, and potential additional info, such as speed, status, heading, and so on. With this data model, even simple questions like “Find all tracks that start in area A and end in area B” can become a real pain in “vanilla” desktop GIS. Even if the points come with a sequence number, which makes it easy to identify the start point, getting the end point is tricky without some custom code or queries. That’s why I have been storing the points in databases in order to at least have the powers of SQL to deal with the data. Even so, most queries were still painfully complex and performance unsatisfactory.

    So I reached out to the Twitterverse asking for pointers towards moving objects database extensions for PostGIS and @bitnerd, @pwramsey, @hruske, and others replied. Amongst other useful tips, they pointed me towards the new temporal support, which ships with PostGIS 2.2. It includes the following neat functions:

    • ST_IsValidTrajectory — Returns true if the geometry is a valid trajectory.
    • ST_ClosestPointOfApproach — Returns the measure at which points interpolated along two lines are closest.
    • ST_DistanceCPA — Returns the distance between closest points of approach in two trajectories.
    • ST_CPAWithin — Returns true if the trajectories’ closest points of approach are within the specified distance.

    Instead of  points, these functions expect trajectories that are stored as LinestringM (or LinestringZM) where M is the time dimension. This approach makes many analyses considerably easier to handle. For example, clustering trajectory start and end locations and identifying the most common connections:


    (data credits: GeoLife project)

    Overall, it’s an interesting and promising approach but there are still some open questions I’ll have to look into, such as: Is there an efficient way to store additional info for each location along the trajectory (e.g. instantaneous speed or other status)? How well do desktop GIS play with LinestringM data and what’s the overhead of dealing with it?

    This is the first part of a series of posts, read more:

    by underdark at September 18, 2016 03:11 PM

    September 11, 2016

    Jorge Arévalo

    Querying raster data stored in your Carto account

    During the years, people keep asking me the same question: can you store (and query) raster data in your Carto (former CartoDB) account? How?

    Well, the answer is yes. And this is more a PostGIS Raster issue than a Carto issue. Let’s go.

    Disclaimer: This code was written couple of years ago. It may be outdated, but still works.

    Store raster data

    First thing you need to do is easy: just drop your georeferenced raster file into the Dataset page of your account. The raster will be imported, and you’ll see something like this

    Do you see that foto_pnoa table with gray shading name? That means you cannot click on it to see the table’s data. But don’t worry: your data is safe in the table. Don’t trust me? Just enter another table and run this query (of course, using your own table’s name)

    select st_summarystats(the_raster_webmercator, 1) as stats from foto_pnoa

    You should see the stats of your raster data now.

    View your raster data over a map

    Now, to the fun part. We’ll code a bit of JavaScript to put that data over a Carto map. We’ll even allow some calculation with the data (average raster value within a region). So, go for it.

    The HTML

    Pretty simple and straightforward. This is the relevant part of the HTML. But don’t worry, I’ll provide a link to the complete example at the end of the post.

    <div class="header">
    <h1>PostGIS Raster test</h1>
    <h2>Draw a figure and click on it to see the avg raster value</h2>
    <div id="map"></div>

    The JS

    We’ll create a Leaflet map, using:

    Relevant parts of the code here (again, you’ll get the complete code later)

    Create the map and the controls

    We create a Leaflet map and the draw controls

    // Create map
    var map = new L.Map('map', {
    zoomControl: true,
    drawnControl: true,
    center: [37.383333, -5.983333],
    zoom: 11
    // Add CartoDB basemaps
    L.tileLayer('http://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}.png', {
    attribution: '<a href="http://cartodb.com">CartoDB</a> © 2014',
    maxZoom: 18
    // Add drawn controls
    var drawnItems = new L.FeatureGroup();
    var drawControl = new L.Control.Draw({
    position: 'bottomleft',
    draw: {
    polyline: false,// Turns off this drawing tool
    marker: false,
    polygon: false,
    rectangle: {
    shapeOptions: {
    color: '#a63b55'
    showArea: true
    circle: {
    shapeOptions: {
    color: '#662d91'
    showArea: true
    edit: {
    featureGroup: drawnItems

    What can I do with those controls? Let’s see

    Handle draw actions

    Whenever we draw a figure:

    1. Using the figure’s coords, we build a PostGIS geometry by calling ST_MakeBox2D, if we drawn a rectangle, or ST_Buffer, if we drawn a circle.
    2. Run a query to check the average raster value within that geometry and show the value

    Check it out in the next snippet

    map.on('draw:created', function (e) {
    var type = e.layerType,
    layer = e.layer;
    var pol_pgis = null;
    switch(type) {
    // Create a Rectangle geometry in PostGIS
    case 'rectangle':
    var coords = layer.getLatLngs();
    var southWest = L.latLng(coords[1].lat, coords[1].lng);
    var northEast = L.latLng(coords[3].lat, coords[3].lng);
    var pol_pgis = "st_transform(ST_SetSRID(ST_MakeBox2D(ST_Point(" +
    coords[1].lng + ", " + coords[1].lat + "),ST_Point(" +
    coords[3].lng + "," + coords[3].lat + ")),4326), 3857)";
    // Create a circle geometry in PostGIS
    case 'circle':
    var center = layer.getLatLng();
    var pol_pgis = "st_transform(geometry(st_buffer(geography(st_setsrid(st_point(" +
    center.lng + ", " + center.lat + "), 4326)), " + layer.getRadius() + ")),3857)";
    case 'polygon':
    if (pol_pgis) {
    q = "SELECT avg((stats).mean) as m from (select st_summarystats(the_raster_webmercator, 1) as stats from foto_pnoa where st_intersects(the_raster_webmercator, " + pol_pgis +")) as foo";
    console.log("QUERY: " + q);
    var sql = new cartodb.SQL({user: 'libregis'});
    .done(function(data) {
    if (data.rows && data.rows.length > 0)
    layer.bindPopup("Average raster value inside the " + type + ": " + data.rows[0].m);
    layer.bindPopup("Could not get avg value!");
    .error(function(errors) {
    layer.bindPopup("Could not get avg value!");
    else {
    layer.bindPopup("Could not get avg value!");

    Show the raster tiles

    The final touch. We put the raster tiles over the map using the Maps API, using AJAX (old fashioned way, I know…)

    var config = {
    "version": "1.3.1",
    "layers": [
    "type": "cartodb",
    "options": {
    "sql": "select * from foto_pnoa",
    "cartocss": "#foto_pnoa {raster-opacity: 0.5;}",
    "cartocss_version": "2.3.0",
    "geom_column": "the_raster_webmercator",
    "geom_type": "raster"
    var request = new XMLHttpRequest();
    request.open('POST', currentEndpoint(), true);
    request.setRequestHeader('Content-Type', 'application/json; charset=UTF-8');
    request.onload = function() {
    if (this.status >= 200 && this.status < 400){             var layergroup = JSON.parse(this.response);             var tilesEndpoint = currentEndpoint() + '/' + layergroup.layergroupid + '/{z}/{x}/{y}.png';             var protocol = 'https:' == document.location.protocol ? 'https' : 'http';             if (layergroup.cdn_url && layergroup.cdn_url[protocol]) {                 var domain = layergroup.cdn_url[protocol];                 if ('http' === protocol) {                     domain = '{s}.' + domain;                 }                 tilesEndpoint = protocol + '://' + domain + '/' + currentUser() + '/api/v1/map/' + layergroup.layergroupid + '/{z}/{x}/{y}.png';             }             rasterLayer = L.tileLayer(tilesEndpoint, {                 maxZoom: 18             }).addTo(map);         } else {             throw 'Error calling server: Error ' + this.status + ' -> ' + this.response;

    That’s it. You can check the final result in the next codepen

    You can grab the code here


    The very first version of the code was made by Raul Ochoa

    by Jorge Arévalo at September 11, 2016 11:38 AM

    September 06, 2016

    PostGIS Development

    PostGIS 2.3.0beta1 Released

    PostGIS 2.3 is feature complete, so we’re looking for testing and feedback! Best served with PostgreSQL 9.6.

    Please give this beta a try and report back any issues you encounter.

    Please report bugs that you find in this release.

    ** Important / Breaking Changes **

    • 3466, Casting from box3d to geometry now returns a 3D geometry (Julien Rouhaud of Dalibo)

    • 3604, pgcommon/Makefile.in orders CFLAGS incorrectly leading to wrong liblwgeom.h (Greg Troxel)

      ** New Features and Performance Enhancements **

    • Add support for custom TOC in postgis_restore.pl (Christoph Moench-Tegeder)

    • Add support for negative indexing in STPointN and STSetPoint (Rémi Cura)
    • Numerous new function additions and enhancements: New Functions and Enhancements

    • 3549, Support PgSQL 9.6 parallel query mode, as far as possible (Paul Ramsey, Regina Obe)

    • 3557, Geometry function costs based on query stats (Paul Norman)
    • 3591, Add support for BRIN indexes (Giuseppe Broccolo of 2nd Quadrant, Julien Rouhaud and Ronan Dunklau of Dalibo)
    • 3496, Make postgis non-relocateable (for extension install), schema qualify calls in functions (Regina Obe) Should resolve once and for all for extensions #3494, #3486, #3076

    • 3547, Update tiger geocoder to support TIGER 2016 and use http or ftp (Regina Obe)

    See the full list of changes in the news file and please report bugs that you find in the release. Binary packages will appear in repositories over the coming weeks as packagers roll out builds.

    View all closed tickets for 2.3.0.

    by Regina Obe at September 06, 2016 12:00 AM

    August 25, 2016

    Paul Ramsey

    PgSQL Indexes and "LIKE"

    Do you write queries like this:

    SELECT * FROM users 
    WHERE name LIKE 'G%'

    Are your queries unexpectedly slow in PostgreSQL? Is the index not doing what you expect? Surprise! You’ve just discovered a PostgreSQL quirk.

    TL;DR: If you are running a locale other than “C” (show LC_COLLATE to check) you need to create a special index to support pattern searching with the LIKE operator: CREATE INDEX myindex ON mytable (mytextcolumn text_pattern_ops). Note the specification of the text_pattern_ops operator class after the column name.

    As a beginner SQL student, you might have asked “will the index make my ‘like’ query fast” and been answered “as long as the wildcard character is at the end of the string, it will.”

    PgSQL Indexes and "LIKE"

    That statement is only true in general if your database is initialized using the “C” locale (the North America/English-friendly UNIX default). Running with “C” used to be extremely common, but is less and less so, as modern operating systems automagically choose appropriate regional locales to provide approriate time and formatting for end users.

    For example, I run Mac OSX and I live in British Columbia, an English-speaking chunk of North America. I could use “C” just fine, but when I check my database locale (via my collation), I see this:

    pramsey=# show LC_COLLATE;
    (1 row)

    It’s a good choice, it’s where I live, it supports lots of characters via UTF-8. However, it’s not “C”, so there are some quirks.

    I have a big table of data linked to postal codes, this is what the table looks like:

                  Table "gis.postal_segments"
          Column       |     Type     | Modifiers 
     postal_code       | text         | not null
     segment           | character(4) | 
        "postal_segments_pkey" PRIMARY KEY, btree (postal_code)

    Note the index on the postal code, a standard btree.

    I want to search rows based on a postal code prefix string, so I run:

    SELECT * FROM postal_segments 
    WHERE postal_code LIKE 'V8V1X%';
                                                  QUERY PLAN                                              
     Seq Scan on postal_segments  (cost=0.00..2496.85 rows=10 width=68) (actual time=30.320..34.219 rows=4 loops=1)
       Filter: (postal_code ~~ 'V8V1X%'::text)
       Rows Removed by Filter: 100144
     Planning time: 0.250 ms
     Execution time: 34.263 ms
    (5 rows)

    Ruh roh!

    I have an index on the postal code, so why am I getting a sequence scan?!?! Because my index is no good for doing pattern matching in any collation other than “C”. I need a special index for that, which I create like this.

    CREATE INDEX postal_segments_text_x 
      ON postal_segments (postal_code text_pattern_ops);

    The magic part is at the end, invoking text_pattern_ops as the opclass for this index. Now my query works as expected:

    SELECT * FROM postal_segments 
    WHERE postal_code LIKE 'V8V1X%';
                                                               QUERY PLAN                                                           
     Index Scan using postal_segments_text_x on postal_segments  (cost=0.29..8.31 rows=10 width=68) (actual time=0.067..0.073 rows=4 loops=1)
       Index Cond: ((postal_code ~>=~ 'V8V1X'::text) AND (postal_code ~<~ 'V8V1Y'::text))
       Filter: (postal_code ~~ 'V8V1X%'::text)
     Planning time: 0.532 ms
     Execution time: 0.117 ms
    (5 rows)

    I have gotten so used to PostgreSQL doing exactly the right thing automatically that it took quite a long time to track down this quirk when I ran into it. I hope this page helps others save some time!

    August 25, 2016 09:05 AM

    August 16, 2016


    Oslandia at FOSS4G 2016 in Bonn

    We are happy to announce that Oslandia is a Bronze sponsor at FOSS4G 2016 conference in Bonn (22-26 August). FOSS4G is the annual global event of the Open Source Geospatial Foundation (OSGeo). It's the largest technical geospatial Open Source conference.

    Oslandia is participating with 2 hands-on workshops and 4 presentations covering 3D, postGIS, point cloud and network management. We invite you to visit our booth for more information, technical questions, and to meet members of our team in person.


    Hope to see you there!

    About FOSS4G 2016

    The key topics in 2016 are :

    • Open Data
    • Remote Sensing for Earth Observation
    • Land information
    • Disaster Management

    The theme of the conference is "building bridges". This supports the objective of bringing together attendees across domains and communities. The Open Source idea has a lot in common with the emerging trend to publish more digital assets as Open Data. At the same time the geospatial technology, the core of FOSS4G, is perfectly applicable to making all that great Open Data more accessible. The "Bonn Geo Summer" also brings together the geospatial and remote sensing worlds which have historically existed somewhat in parallel. Land Information in its broadest sense, be it cadastre, tenure and city planning, has a growing need for geospatial technology to help alleviate the pressure on urban areas and make the constant migration pressure into cities and large scale refugee events manageable. The fourth thematic focus lies on emergency management which also heavily relies on geospatial technology and increasingly also on geospatial Open Data.

    by Audrey Malherbe at August 16, 2016 05:00 PM

    August 10, 2016

    Paul Ramsey

    Your Broken PostGIS Upgrade

    Since the Dawn of Time, people have found PostGIS upgrades difficult and confusing, and this is entirely to be expected, because a PostGIS upgrade consists of a number of interlocking parts. Sometimes, they “upgrade” their version of PostGIS and find out they’ve bricked their system. What gives?

    Your Broken PostGIS Upgrade

    What Makes PostGIS Work?

    Before talking about upgrades, it’s important to understand how PostGIS works at all, because that understanding is key to seeing how upgrade scenarios go bad.

    PostGIS is a “run-time loadable library” for PostgreSQL. That means we have a block of C code that is added to a running PostgreSQL database. That C code sits in a “library file” which is named (for the current 2.2 version): postgis-2.2.so.

    Just to add to the confusion: for Windows, the name of the library file is postgis-2.2.dll. For every rule, there must be an exception. For users of Apple OSX, yes, there’s a further exception for you: even though most dynamic libraries on OSX are suffixed .dylib, the PostgreSQL modules on OSX are suffixed .so, just like their Linux counterparts.

    The location of the postgis-2.2.so file will vary from system to system.

    The presence of the postgis-2.2.so alone is not sufficient to “PostGIS enable” a database. PostGIS consists of a large collection of SQL functions in the database.

    The SQL functions are created when you run the CREATE EXTENSION postgis command. Until that time your database knows nothing about the existence or definition of the PostGIS functions.

    Once the extension is installed, you can see the definitions of the PostGIS functions in the system tables.

    The use of dynamic function and type management catalogs is one of the things which makes PostgreSQL so incredibly flexible for extensions like PostGIS

    SELECT * 
      FROM pg_proc 
      WHERE proname = 'st_pointonsurface';
    -[ RECORD 1 ]---+--------------------
    proname         | st_pointonsurface
    pronamespace    | 2200
    proowner        | 10
    prolang         | 13
    procost         | 100
    prorows         | 0
    provariadic     | 0
    protransform    | -
    proisagg        | f
    proiswindow     | f
    prosecdef       | f
    proleakproof    | f
    proisstrict     | t
    proretset       | f
    provolatile     | i
    pronargs        | 1
    pronargdefaults | 0
    prorettype      | 667466
    proargtypes     | 667466
    proallargtypes  | 
    proargmodes     | 
    proargnames     | 
    proargdefaults  | 
    prosrc          | pointonsurface
    probin          | $libdir/postgis-2.2
    proconfig       | 
    proacl          | 

    Lots to see here, but most important bit is the entry for the probin column: $libdir/postgis-2.2. This function (like all the other PostGIS functions) is bound to a particular version of the PostGIS C library.

    Those of you thinking forward can now begin to see where upgrades could potentially go wrong.

    How Things Go Wrong

    Package Managers

    The most common way for things to go wrong is to upgrade the library on the system without upgrading the database.

    So, in Red Hat Linux terms, perhaps running:

    yum upgrade postgresql94-postgis

    This seems straight-forward, but think about what a package manager does during an upgrade:

    • Downloads a new version of the software
    • Removes the old version
    • Copies in the new version

    So, if we had PostGIS 2.1.3 installed, and the latest version is 2.2.2, what has happend?

    • The postgis-2.1.so file has been removed
    • The postgis-2.2.so file has been added
    • So, the pg_proc entries in every PostGIS-enabled database now point to a library file that does not exist

    Fortunately this mismatch between the pg_proc entries and the system state is usually solved during the very next step of the upgrade. But it’s a manual step, and if the DBA and system administrator are different people with different schedules, it might not happen.

    Your next step should be to go and update the SQL function definitions by running an extension update on all your databases:

    ALTER EXTENSION postgis UPDATE TO '2.2.2';

    If you don’t, you’ll find that none of the PostGIS functions work. That, in fact, you cannot even dump your database. The very act of outputting a representation of the geometry data is something that requires the PostGIS C library file, and until you run ALTER EXTENSION the database doesn’t know where the new library file is.


    Since the use of CREATE EXTENSION postgis (available since PostgreSQL 9.1+ and PostGIS 2.0+) became commonplace, migrations now almost always “just work”, which is excellent news.

    • When you dump a modern PostGIS-enabled database, that was created using the CREATE EXTENSION postgis command, the dump file just includes a CREATE EXTENSION postgis command of its own at the top.
    • When you load the dump file into a new version of PostgreSQL even with a new version of PostGIS, the extension is created and the data magically loads.

    However, there are still some old databases around that were created before the PostgreSQL extension system was invented, and when you dump them you get not only the data, but all the “custom” function and type definitions, including the defintions for PostGIS. A function definition looks like this:

    CREATE OR REPLACE FUNCTION ST_PointOnSurface(geometry)
        RETURNS geometry
        AS '$libdir/postgis-2.2', 'pointonsurface'

    And look what is hiding inside of it: a reference to a particular version of the PostGIS library! So you cannot simply dump your old PostGIS 1.5 database on PostgreSQL 8.4 and load it into a fresh PostGIS 2.2 database on PostgreSQL 9.5: the function definitions won’t reference the right library file.

    The best bet for a really old database that was created without the extension mechanism is to use the “hard upgrade” process. The hard upgrade works by:

    • Taking a special “custom-format” back-up that includes an object catalog;
    • Filtering the back-up to clean out all the PostGIS-specific function and object definitions; and then
    • Loading the “cleaned” back-up into a new database with the desired version of PostGIS already installed (using CREATE EXTENSION postgis this time, so you never have to hard upgrade again).


    In the case of upgrades that change out the underlying library and other situations that result in a mismatch between the SQL definitions in the database and the state of the system, there are a couple hacks that provide short-term fixes for emergencies:

    • Symlink the library name the database is looking for to the library name you have. So if your database wants postgis-2.1.so and all you have is postgis-2.2.so, you can ln -s postgis-2.2.so postgis-2.1.so and your database will “work” again.
    • Update the PostgreSQL catalog definitions for the functions. As a super-user, you can do all kinds of dangerous things, and one of them is to just UPDATE pg_proc SET probin = '$libdir/postgigs-2.2' WHERE probin ~ 'postgis-2.1'

    Both hacks “work” because the PostGIS project doesn’t change underlying function names often, and inter-version changes mostly involve adding functions to the C library, not removing old ones.

    However, there’s no guarantee that an underlying function name hasn’t change between versions, it’s just unlikely. In the worst case, the function name hasn’t changed, but the parameters have, so it’s now possible that calling the function will crash your database.

    All this to say: linking and SQL catalogue hacks should be used temporarily only until you can properly upgrade your database using a hard upgrade.

    August 10, 2016 04:05 PM

    August 02, 2016

    Boston GIS (Regina Obe, Leo Hsu)

    GeoHipster Interview with Regina Obe

    GeoHipster interview with me came out today. Covers how I stumbled into database programming and my work on PostGIS, PostgreSQL and pgRouting. Interview with Regina Obe

    by Regina Obe (nospam@example.com) at August 02, 2016 03:45 AM

    July 28, 2016

    Bill Dollins

    Personal Geospatial Workflows, July 2016 Edition

    It’s hard to believe, but I last touched upon this topic over two years ago, when my family and I were living in our between-houses rental. One of the goals I had when building our current house was to create a space where I could more effectively work from home. To that end, I have a dedicated office that I’ve been working toward optimizing for my technical work.

    One advantage of a dedicated space, which I did not anticipate ate the time, is compartmentalization. One of the dangers with working at home is the blurring of the boundary between work time and personal/family time. In our old house, I definitely felt that as I was working from the dining room table. Now, I can more effectively shut the door and step away. I’m not perfect at doing that, yet, but I am getting better.


    As a consultant doing federal work, I don’t get to work off-site all the time. I’ve been fortunate, however, to have worked a few projects over the past couple of years that have allowed it, so I’ve taken advantage of it as much as possible.

    In the past two years, the nature of my work has shifted dramatically. I am still doing my core federal work but I have diversified the smaller projects that I work on an independent basis. This has been primarily to keep my technical skills as diverse as possible. I still do a lot of .Net work for federal customers but, on the other hand, I am also doing a fair amount of geospatial web development these days, centered around a core stack of PostGIS, Node, and Leaflet. Other tools get tossed in based on requirements, but those core technologies are common.

    As a result, I found myself doing development in a number of different environments with a variety of tools. (Due to customer preferences, for example, I have projects that use Visual Studio 2010, 2012, and 2015.) While PostgreSQL is my mainstay, I also work in SQL Server (differing versions there, too).

    I had two machines, one company-provided and one personal, to attack these various configurations. Both were severely under-powered for what I was doing, so I recently purchased a new system to unite all of my project work in one place, and allow myself to make use of virtualization in order to segment off the various development and deployment configurations I support.

    I knew I wanted an Ubuntu Linux host machine, primarily because it’s what I prefer on a daily basis. I opted to buy a pre-built system from System76 versus building one myself. I’ve done that a few times, but simply don’t have the time for it anymore and was attracted to the convenience of having someone else do it for me.

    I customized the System76 Wild Dog Pro system, expanding the RAM to 64GB, the system drive to a 250GB M.2 SSD, and adding a 4TB hard drive for additional storage. This configuration, especially the RAM, allows me a lot of flexibility for configuring and running project VMs in VirtualBox, while not taxing the performance of the host machine.

    My plan is to keep the host system relatively clean. I have installed the following directly to the host:

    • PostgreSQL/PostGIS/pgAdmin III
    • QGIS
    • GDAL/OGR
    • Chrome
    • Chrome Remote Desktop (more on that later)

    I use PostGIS so commonly that I decided to install it directly onto the host and allow project VMs to access it over the network. This setup actually emulates a typical deployment pattern pretty well, in addition to giving me just one installation to manage. I have set up a tablespace to direct the data storage to the 4TB hard drive.

    That’s it. Everything else will live in a VM. As a result, I’m storing a lot of ISO images of operating systems and application installers, but they live on a USB 3 external drive. I’m currently in the process of creating my baseline VMs, including the one that will be my primary ArcGIS machine. (The image above shows that I’ve already build a Windows 10 baseline.)  I have also imaged my current development laptop to a VM using the VMWare Infrastucture Client. That will help with the transition of my development environment into this new setup.

    You probably noticed that I purchased a desktop workstation, but that I am currently using a laptop. I have always been a laptop guy because I liked the mobility and was willing to sacrifice some power for it. Two big changes for me are that I don’t move around as much and that I have had a need for more power. I had been thinking of making the switch for a couple of years and finally decided to make the jump.

    What about the mobility? I still need it occasionally, which is why Chrome Remote Desktop is in the mix. I’ve tried it a few times and it seems to work well. In my opinion, it works much better than VNC, giving me a Windows RDP type of experience. I’ll be putting it to the test very soon as I attempt to work remotely from 1,100 miles away. I don’t have the world’s greatest ISP, and I don’t have a UPS yet, so this could fail spectacularly, but I’ll give it a go.

    I expect it to take a couple of weeks to get my feet back under me after this transition, but I’ll post an update soon.

    by Bill Dollins at July 28, 2016 05:02 PM

    July 05, 2016

    UpStats blog (Stefan Petrea)

    Geolocation using multiple services

    UPDATE: A chinese version of this post is available here.


    In a previous post I wrote about PostGIS and ways of querying geographical data.

    This post will focus on building a system that queries free geolocation services 1 and aggregates their results.


    In summary, we're making requests to different web services (or APIs), we're doing reverse geocoding on the results and then we'll aggregate them.

    Comparing geonames and openstreetmap

    To relate to the previous post, here are some differences between geonames and openstreetmap:

    criterion OSM geonames
    size 50 GB compressed 309 MB compressed
    entities 3.25 billion 11 million
    has administrative area data yes yes
    has lat/long city data yes yes
    has neighbourhood/district data yes yes
    has region/area polygonal areas yes no
    has intracity-level metadata yes no
    has terrain metadata yes no

    They are meant for different purposes. Geonames is meant for city/administrative area/country data and can be used for geocoding. Openstreetmap has much more detailed data (one could probably extract the geonames data using openstreetmap) and can be used for geocoding, route planning and more .

    Asynchronous requests to geolocation services

    We're using the gevent library to make asynchronous requests to the geolocation services.

    import gevent
    import gevent.greenlet
    from gevent import monkey; gevent.monkey.patch_all()
            ['geoplugin'    , 'http://www.geoplugin.net/json.gp?ip={ip}' ],
            ['ip-api'       , 'http://ip-api.com/json/{ip}'              ],
            ['nekudo'       , 'https://geoip.nekudo.com/api/{ip}'        ],
            ['geoiplookup'  , 'http://api.geoiplookup.net/?query={ip}'   ],
    # fetch url in asynchronous mode (makes use of gevent)
    def fetch_url_async(url, tag, timeout=2.0):
        data = None
            opener = urllib2.build_opener(urllib2.HTTPSHandler())
            opener.addheaders = [('User-agent', 'Mozilla/')]
            data = urllib2.urlopen(url,timeout=timeout).read()
        except Exception, e:
        return [tag, data]
    # expects req_data to be in this format: [ ['tag', url], ['tag', url], .. ]
    def fetch_multiple_urls_async(req_data):
        # start the threads (greenlets)
        threads_ = []
        for u in req_data:
            (tag, url) = u
            new_thread = gevent.spawn(fetch_url_async, url, tag)
        # wait for threads to finish
        # retrieve threads return values
        results = []
        for t in threads_:
            results.append(t.get(block=True, timeout=5.0))
        return results
    def process_service_answers(location_data):
        # 1) extract lat/long data from responses
        # 2) reverse geocoding using geonames
        # 3) aggregate location data
        #    (for example, one way of doing this would
        #     be to choose the location that most services
        #     agree on)
    def geolocate_ip(ip):
        urls = []
        for grp in geoip_service_urls:
            tag, url = grp
            urls.append([tag, url.format(ip=ip)])
        results = fetch_multiple_urls_async(urls)
        answer = process_service_answers(results)
        return answer

    City name ambiguity

    Cities with the same name within the same country

    There are many cities with the same name within a country, in different states/administrative regions. There's also cities with the same name in different countries.

    For example, according to Geonames, there are 24 cities named Clinton in the US (in 23 different states, with two cities named Clinton in the same state of Michigan).

    WITH duplicate_data AS (
        array_agg(ROW(country_code, region_code)) AS dupes
        FROM city_region_data
        WHERE country_code = 'US'
        GROUP BY city_name, country_code
        ORDER BY COUNT(ROW(country_code, region_code)) DESC
    ARRAY_LENGTH(dupes, 1) AS duplicity,
    ( CASE WHEN ARRAY_LENGTH(dupes,1) > 9 
      THEN CONCAT(SUBSTRING(ARRAY_TO_STRING(dupes,','), 1, 50), '...')
      ELSE ARRAY_TO_STRING(dupes,',') END
    ) AS sample
    FROM duplicate_data
    LIMIT 5;
    city_name duplicity sample
    Clinton 24 (US,NY),(US,AR),(US,NC),(US,MA),(US,MD),(US,OH),(U…
    Franklin 19 (US,ME),(US,MA),(US,NC),(US,TX),(US,NC),(US,LA),(U…
    Springfield 19 (US,MN),(US,KY),(US,SD),(US,MI),(US,VA),(US,IL),(U…
    Madison 18 (US,CT),(US,MN),(US,NJ),(US,ME),(US,SD),(US,FL),(U…
    Greenville 18 (US,NC),(US,SC),(US,MS),(US,KY),(US,RI),(US,ME),(U…

    Cities with the same name in the same country and region

    Worldwide, even in the same region of a country, there can be multiple cities with the exact same name.

    Take for example Georgetown, in Indiana. Geonames says there are 3 towns with that name in Indiana. Wikipedia says there are even more:

    WITH duplicate_data AS (
        array_agg(ROW(country_code, region_code)) AS dupes
        FROM city_region_data
        WHERE country_code = 'US'
        GROUP BY city_name, region_code, country_code
        ORDER BY COUNT(ROW(country_code, region_code)) DESC
    ARRAY_LENGTH(dupes, 1) AS duplicity,
    ( CASE WHEN ARRAY_LENGTH(dupes,1) > 9 
      THEN CONCAT(SUBSTRING(ARRAY_TO_STRING(dupes,','), 1, 50), '...')
      ELSE ARRAY_TO_STRING(dupes,',') END
    ) AS sample
    FROM duplicate_data
    LIMIT 4;
    city_name duplicity sample
    Plantation 3 (US,FL),(US,FL),(US,FL)
    Georgetown 3 (US,IN),(US,IN),(US,IN)
    Robinwood 3 (US,MD),(US,MD),(US,MD)
    Prospect Park 2 (US,NJ),(US,NJ)

    Reverse geocoding

    Both (city_name, country_code) and (city_name, country_code, region_name) tuples have failed as candidates to uniquely identify a location.

    We would have the option of using zip codes or postal codes except we can't use those since most geolocation services don't offer that.

    But most geolocation services do offer longitude and latitude, and we can use those to eliminate ambiguity.

    Geometric data types in PostgreSQL

    I looked further into the PostgreSQL docs and found that it also has geometric data types and functions for 2D geometry. Out of the box you can model points, boxes, paths, polygons, circles, you can store them and query them.

    PostgreSQL has some additional extensions in the contrib directory. They are available out of the box with most Postgres installs.

    In this situation we're interested in the cube and earthdistance extensions 2. The cube extension allows you to model n-dimensional vectors, and the earthdistance extension uses 3-cubes to store vectors and represent points on the surface of the Earth.

    We'll be using the following:

    • the earth_distance function is available, and it allows you to compute the great-circle distance between two points
    • the earth_box function to check if a point is within a certain distance of a reference point
    • a gist expression index on the expression ll_to_earth(lat, long) to make fast spatial queries and find nearby points

    Designing a view for city & region data

    Geonames data was imported into 3 tables:

    Then we create a view that pulls everything together 3. We now have population data, city/region/country data, and lat/long data, all in one place.

    CREATE OR REPLACE VIEW city_region_data AS ( 
            b.country AS country_code,
            b.asciiname AS city_name,
            a.name AS region_name,
            b.latitude AS city_lat,
            b.longitude AS city_long,
            c.name    AS country_name
        FROM geo_admin1 a
        JOIN (
            SELECT *, (country || '.' || admin1) AS country_region, admin1 AS region_code
            FROM geo_geoname
            WHERE fclass = 'P'
        ) b ON a.code = b.country_region
        JOIN geo_countryinfo c ON b.country = c.iso_alpha2

    Designing a nearby-city query and function

    In the most nested SELECT, we're only keeping the cities in a 23km radius around the reference point, then we're applying a country filter and city pattern filter (these two filters are optional), and we're only getting the closest 50 results to the reference point.

    Next, we're reordering by population because geonames sometimes has districts and neighbourhoods around bigger cities 4, and it does not mark them in a specific way, so we just want to select the larger city and not a district (for example let's say the geolocation service returned a lat/long that would resolve to one district of a larger metropolitan area. In my case, I'd like to resolve this to the larger city it's associated with)

    We're also creating a gist index (the @> operator will make use of the gist index) which we're using to find points within a radius of a reference point.

    This function takes a point (using latitude and longitude) and returns the city, region and country that is associated with that point.

    CREATE INDEX geo_geoname_latlong_idx ON geo_geoname USING gist(ll_to_earth(latitude,longitude));
    CREATE OR REPLACE FUNCTION geo_find_nearest_city_and_region(
        latitude double precision,
        longitude double precision,
        filter_countries_arr varchar[],
        filter_city_pattern  varchar,
        country_code varchar,
        city_name varchar,
        region_name varchar,
        region_code varchar,
        population bigint,
        _lat double precision,
        _long double precision,
        country_name varchar,
        distance numeric
        ) AS $$
        SELECT *
        FROM (
            FROM (
                       ll_to_earth(c.city_lat, c.city_long),
                       ll_to_earth(latitude, longitude)
                      )::numeric, 3) AS distance_
                FROM city_region_data c
                WHERE earth_box(ll_to_earth(latitude, longitude), 23000) @> ll_to_earth(c.city_lat, c.city_long) AND
                      (filter_countries_arr IS NULL OR c.country_code=ANY(filter_countries_arr)) AND
                      (filter_city_pattern  IS NULL OR c.city_name LIKE filter_city_pattern)
                ORDER BY distance_ ASC
                LIMIT 50
            ) d
            ORDER BY population DESC
        ) e
        LIMIT 1;
    LANGUAGE plpgsql;


    We've started from the design of a system that would query multiple geoip services, would gather the data and would then aggregate it to get a more reliable result.

    We first looked at some ways of uniquely identifying locations.

    We've then picked a way that would eliminate ambiguity in identifying them. In the second half, we've looked at different ways of structuring, storing and querying geographical data in PostgreSQL.

    Then we've built a view and a function to find cities near a reference point which allowed us to do reverse geocoding.



    By using multiple services (and assuming they use different data sources internally) after aggregation, we can have a more reliable answer than if we were using just one.

    Another advantage here is that we're using free services, no setup is required, we don't have to take care of updates, since these services are maintained by their owners.

    However, querying all these web services will be slower than querying a local geoip data structure. But, city/country/region geolocation databases are available, some examples include geoip2 from maxmind, ip2location or db-ip.


    There's a nice post here using the earthdistance module to compute distances to nearby or far away pubs.


    Geonames has geonameIds as well, which are geonames-specific ids we can use to accurately refer to locations.


    geonames does not have polygonal data about cities/neighbourhoods or metadata about the type of urban area (like openstreetmap does) so you can't query all city polygons (not districts/neighbourhoods) that contain that point.

    July 05, 2016 09:00 PM

    June 18, 2016

    Stephen Mather

    Using PostGIS for Hydrologic Modeling (reblog)

    The Problem We have to filter out the roads and ditches without removing streams that cross roads or follow them closely. I’m going to use PostGIS to find the intersection of the streams lines data with a buffered roads polygon. If the intersected line is less than 50% of the length of the stream line, […]

    via Filtering Roads from Extracted Streams Data — GeoKota

    by smathermather at June 18, 2016 01:57 AM

    June 04, 2016

    Boston GIS (Regina Obe, Leo Hsu)

    FOSS4GNA 2016: pgRouting - A Crash course video is out

    Leo's pgRouting : a Crash Course video made it thru great. Better than mine. Leo doesn't believe in slides, so this is all live demo stuff. The data he used in the video is part of our code/data download for pgRouting: A Practical Guide.

    Continue reading "FOSS4GNA 2016: pgRouting - A Crash course video is out"

    by Regina Obe (nospam@example.com) at June 04, 2016 07:05 AM

    May 31, 2016

    Boston GIS (Regina Obe, Leo Hsu)

    FOSS4GNA 2016 PostGIS Spatial Tricks video is out

    The videos for FOSS4G NA 2016 have started coming out. Recently Andrea Ross posted PostGIS Spatial Tricks talk video. I'm happy to say it looks pretty good and I didn't suck as badly as I worried I would. Thank you very much Andrea. Some talks unfortunately did not come thru. I'm hoping Leo's pgRouting : a Crash Course video made it thru okay as well, and will post that later if it does.

    Only small little nit-picks is the first 2-5 minutes or so didn't make it thru and the blue colors on the slides got a little drowned out, but here are the slides if you need full resolution.

    Continue reading "FOSS4GNA 2016 PostGIS Spatial Tricks video is out"

    by Regina Obe (nospam@example.com) at May 31, 2016 08:36 PM

    May 30, 2016

    Stephen Mather

    Using foreign data wrapper to use PostGIS with SQLServer

    Here was the problem that needed solved last week (we have a few similar problems in upcoming projects, so this was an exciting thing to try): we needed to use PostGIS to access data in a SQLServer database. The SQLServer database backs the web site in question, the underlying content management system, etc., so no– removing SQLServer isn’t really an option at this stage. Obviously PostGIS is a requirement too… .

    Before I go further, I used tds_fdw as the foreign data wrapper. The limitations here are as follows: it is a read-only connection, and only works with Sybase and SQLServer, as it uses tabular data stream protocol for communicating between client and server. This is not as generic a solution as we can use. Next time I’ll try ogr_fdw which is more generic as it can connect with other databases and other data types. Another advantage to ogr_fdw is we can use IMPORT FOREIGN SCHEMA. Regina Obe and Leo Hsu warn though to limit this to 150 tables or so for performance reasons.

    With the limitations listed above, this is how we built the thang:

    DROP SERVER beach_fdw CASCADE;
    -- Create the server connection with FOREIGN DATA WRAPPER
    CREATE SERVER beach_fdw
    OPTIONS (servername 'name_or_ip', port '1433', database 'non-spatial-database', tds_version '7.1', msg_handler 'notice');
    -- We map the postgres user to the user that can read the table we're interested in
    SERVER beach_fdw
    OPTIONS (username 'user', password 'password');
    -- Create the actual foreign table connection
    CREATE FOREIGN TABLE beach_closures (
    AutoNumber int NOT NULL,
    Title varchar NOT NULL,
    StartDate timestamp without time zone NOT NULL,
    WaterQuality varchar NOT NULL,
    Latitude varchar NOT NULL,
    Longitude varchar NOT NULL,
    BeachStatus varchar NOT NULL,
    ClosureColor varchar NOT NULL)
    SERVER beach_fdw
    OPTIONS (schema_name 'schema_name', table_name 'vw_CMPBeachClosures');
    -- Now we create a spatial view using our longitude and latitude
    CREATE VIEW v_beach_closures AS
    AutoNumber, Title, StartDate, WaterQuality, Latitude,
    Longitude, BeachStatus, ClosureColor, ST_SetSRID(ST_MakePoint(Longitude::numeric, Latitude::numeric), 4326)	AS geom
    FROM beach_closures;

    Voila! A nice little PostGIS enabled view of a SQLServer view or table!

    by smathermather at May 30, 2016 02:34 AM

    May 21, 2016

    Boston GIS (Regina Obe, Leo Hsu)

    pgRouting 2.2.3 released with support for PostgreSQL 9.6beta1

    pgRouting 2.2.3 was released last week. Main change is this version now supports PostgreSQL 9.6. Many thanks to Vicky Vergara for working thru the issues with PostgreSQL 9.6 and getting it to work. Vicky has also been doing a good chunk of the coding (a lot of Boost refactoring and integrating more Boost features), testing, and documentation in pgRouting, osm2pgrouting, and QGIS pgRoutingLayer in general for pgRouting 2.1, 2.2, and upcoming 2.3. We are very indebted to her for her hard work.

    If you are a windows user testing the waters of PostgreSQL 9.6beta1, we have pgRouting 2.2.3 binaries and PostGIS 2.3.0dev binaries at http://postgis.net/windows_downloads.

    Continue reading "pgRouting 2.2.3 released with support for PostgreSQL 9.6beta1"

    by Regina Obe (nospam@example.com) at May 21, 2016 04:24 PM

    May 13, 2016

    Postgres OnLine Journal (Leo Hsu, Regina Obe)

    PLV8 binaries for PostgreSQL 9.6 windows both 32-bit and 64-bit

    To celebrate recent release of PostgreSQL 9.6beta1, we've started to experiment with our favorite extensions. For starters, PLV8 (aka PL/JavaScript) binaries listed below and upcoming PostGIS 2.3.0 and ogr_fdw detailed here

    Continue reading "PLV8 binaries for PostgreSQL 9.6 windows both 32-bit and 64-bit"

    by Leo Hsu and Regina Obe (nospam@example.com) at May 13, 2016 10:56 PM

    Boston GIS (Regina Obe, Leo Hsu)

    PostgreSQL 9.6beta1 out, help test PostGIS 2.3 windows binaries available

    PostgreSQL 9.6beta1 came out yesterday. It is the first version of PostgreSQL that will have parallel query support and PostGIS 2.3 will be the first PostGIS to support parallelism in queries. Although work is not yet committed in PostGIS repo to support this, you can expect to see this soon (currently here - https://github.com/pramsey/postgis/tree/parallel , waiting for someone to you know who you are do something about it.)

    UPDATE: Some parallel support has been committed. More to come. pgRouting 9.6 issues resolved, many thanks to Vicky Vergara. Now pgRouting 2.2.3 windows binaries available for PostgreSQL 9.6beta1. Also recently tested these with BigSQL 9.6beta1 Windows 64-bit (at least the PostGIS ones), and seem to work fine. BigSQL paths are a little different from EDB (so be careful when copying to copy to right folder, zip structure follows the EDB structure. Also make sure not to overwrite files that came packaged with BigSQL, since there is more file overlap since we both use mingw64, but their gcc is newer).

    Because of the newness of the parallelization feature, there are some caveats. As with all big things, we expect there to be a lot of bugs, and the more eyeballs on those and real workloads we've got hammering on them, the sweeter the PostGIS 2.3.0 and PostgreSQL 9.6 release will be.

    Binaries for Windows users

    For windows users, winnie the PostGIS windows buildbot is now building PostGIS for 9.6. Get PostgreSQL 9.6 binaries and installers from PostgreSQL 9.6beta1 for windows.

    Once you have that, just copy the contents of the respective PostGIS 2.3 9.6 binaries listed here - http://postgis.net/windows_downloads/ into your install folder.

    In the extras folder, you'll also find ogr_fdw foreign data wrapper latest development version which we covered extensively in FOSS4GNA2016 PostGIS Spatial Tricks. Talk also covered some new PostGIS 2.3.0 stuff.

    We don't have pgRouting binaries available yet. pgRouting team is working out some compatibility issues with PostgreSQL 9.6. Once those are resolved, we will publish pgRouting binaries as well.

    by Regina Obe (nospam@example.com) at May 13, 2016 09:02 PM

    May 06, 2016

    Bill Dollins

    Revisiting Two Old Friends: ArcGIS and PostGIS

    Back in the dark old days of ArcSDE, when it first started to support PostgreSQL/PostGIS as a back-end data store, I did a series of posts about how to work with it. Of course, working with PostGIS in ArcGIS was a theme of the early days of this blog, through my association with zigGIS. Although it’s been the case for a while, I’m feeling a bit happy today that it’s now as simple as this to work with (vanilla, non-geodatabased) PostGIS in ArcMap. (Post continues below the GIF.)


    You might ask “Why not just work in QGIS?” and you would have a valid question. QGIS is a perfectly fine desktop PostGIS client. As a matter of fact, I went almost two years without a functioning copy of ArcMap and using QGIS as my primary desktop tool (which is why I’m exploring the capabilities of ArcGIS 10.4 now). Sometimes, projects dictate what tools you need to use. The data-level interoperability implied by the support shown above has me thinking about hybrid workflows to allow shops (especially small ones) that have need for final products to end up in an Esri stack to still exercise a measure of choice with regard to tools. It may be time to re-tool that old series of posts for the state of GIS tools circa the middle of this decade.

    by Bill Dollins at May 06, 2016 06:27 PM

    April 21, 2016

    Postgres OnLine Journal (Leo Hsu, Regina Obe)

    PGConfUS 2016 PostGIS slides and tutorial material

    We gave a PostGIS Intro Training and a PostGIS talk at PGConfUS 2016 in Brooklyn, New York and just got back. A number of people asked if we'd make the slides and material available. We have these posted on our presentation page: http://www.postgis.us/presentations and will be putting on the PostgreSQL Wiki as well in due time. There will be a video coming along for the talk, but the training was not recorded.

    We also have two more talks coming up in North Carolina in Early May at FOSS4G NA 2016 - one on PostGIS Spatial Tricks which has more of a GIS specialist focus than the top 10 talk we gave, but there will be some overlap. The other talk is a topic a couple of people asked us in training and after our talk, on routing along constrained paths. If you are attending FOSS4G NA 2016, you won't want to miss our talk pgRouting: A Crash Course which is also the topic of our upcoming book.

    Just like FOSS4G NA 2015, there is a pgDay track which is PostgreSQL specific material, useful to a spatial crowd, but not PostGIS focused.

    Continue reading "PGConfUS 2016 PostGIS slides and tutorial material"

    by Leo Hsu and Regina Obe (nospam@example.com) at April 21, 2016 05:04 PM

    April 19, 2016

    Archaeogeek (Jo Cook)

    PortableGIS 5.6

    I’m pleased to announce the latest release of Portable GIS. This version (v5.6) has the following changes:

    • QGIS 2.14.1 LTR
    • By popular demand: Geoserver 2.8

    You can download the setup exe and the md5 checksum here.

    Older versions are still available but have been archived to avoid confusion.

    As always, please let me know of any problems via the Portable GIS google group.

    Note that I will shortly be publicising a GitLabs repository for the changed files, along with developer and user documentation, to allow people to roll their own versions or contribute to development. This work is nearly complete, so watch this space!

    April 19, 2016 10:20 AM