borderextract is an Osmosis-plugin used to extract different levels of administrative borders as polygons from an Openstreetmap-dump or database.
Openstreetmap contains detailed administrative borders, like country, state or village borders. These borders are represented by ways, and held together by relations tagged as admin-borders. borderextract can combine these border-pieces to closed polygons. This enables reverse-geocoding, thus one can easily query for the administrative area(s) a particular coordinate is in.We use borderextract for our public transport nearby-search website Arrlee: https://www.arrlee.eu
Version 1.4 (latest)
- For Openstreetmap API > 0.39
- Border name language: One is now able to specify preferred languages for border names.
- Handle inconsistent border roles, like found in the state border of New Zealand.
- Handle cascading boundary relations, like found in the state border of Germany.
- Added support for poly-file export
- Flipped latitude/longitude in point creation in the mysql export.
- Removed dependency to “Java API for KML” which also fixes the ClassNotFoundException in osmosis
- Initial Release
Download borderextract from here: borderextract-1.4.jar
You will also need this library:
- Google Guava 10.0.1: guava-10.0.1.jar
The borderextract source code is available on github (borderextract) We use Maven for the build. Check also the generated Maven project site.
If you did not install Osmosis so far, follow these instructions: Osmosis/Installation
If you are upgrading from a previous version delete the borderextract-xx.jar from the
<your path>/osmosis-0.41/lib/default directory.
After the successful setup, you need to copy all of the jars into the Osmosis lib directory:
cp borderextract-1.4.jar guava-10.0.1.jar <your path>/osmosis-0.41/lib/default
Since borderextract is just a plugin for Osmosis, please check the Osmosis Documentation for more information.
borderextract extracts all complete borders which are passed through the plugin and have the desired administrative level(s). “Complete borders” are borders which are fully contained in the data. For example, a country dump for Germany contains the complete german borders and because germany is not an island it shares these borders with the neighboring countries, such as France, Switzerland or Austria. But the foreign countries borders aren’t fully contained in the Germany-dump, for obvious reasons. When borderextract encounters such an incomplete border it outputs an error and a stack trace (will be changed in the future). Example:
Dec 29, 2011 10:42:13 AM com.generalbytes.osmosis.borderextract.KMLBorderListener writeKMLForRelation SEVERE: Skipping border of "Alsace" due to error com.generalbytes.osmosis.borderextract.IncompleteBorderException: Can not find way 58741643 for relation 8636 (Alsace) at com.generalbytes.osmosis.borderextract.BaseBorderListener.sortContainedWays(BaseBorderListener.java:210) at com.generalbytes.osmosis.borderextract.BaseBorderListener.getPolygon(BaseBorderListener.java:141) at com.generalbytes.osmosis.borderextract.KMLBorderListener.writeKMLForRelation(KMLBorderListener.java:94) at com.generalbytes.osmosis.borderextract.KMLBorderListener.complete(KMLBorderListener.java:69) at com.generalbytes.osmosis.borderextract.BorderExtractTask.complete(BorderExtractTask.java:175) at crosby.binary.osmosis.OsmosisBinaryParser.complete(OsmosisBinaryParser.java:34) at crosby.binary.file.BlockInputStream.process(BlockInputStream.java:37) at crosby.binary.osmosis.OsmosisReader.run(OsmosisReader.java:37) at java.lang.Thread.run(Thread.java:662)
You can safely ignore this exception – Osmosis and the borderextract plugin will continue work.
Plugin usage and parameters
|Option||Description||Valid values||Default values|
|type||The output file type.
"kml" produces a zip file containing each found border as a separate KML-File.
"mysql" creates a single SQL-File suitable for direct import into the MySQL-Database. Also read the MySQL-section for more information.
"poly" produces a zip file containing a polygon file for each found border. A polygon file can be used together with the Area Filtering Tasks (–bounding-polygon) to extract only certain areas from an OSM dump.
|kml, mysql, poly||kml|
|file||The name of the output file.||borderextract.[zip|sql]|
|levels||The administrative level(s) to export. See the Tag:boundary=administrative documentation for more information on level-number to border-type mapping.
The levels can be given as comma-separated list of level numbers from 0 to 11 including.
|lang||Desired language for border names. Multiple languages can be specified separated by comma. Language names are according to ISO 639-1, see also Multilingual names.
borderextract will start looking for a name in the first given language and – if not found – continue with the next one. If no name in a desired language is found, it uses the default (mostly local) name.
|Default border name, no matter what language.|
Of course you can combine borderextract with other Osmosis-plugins. But please note that borderextract only produces the output files and can not pass data to subsequent osmosis plugins. Trying to do so will result in an osmosis error. So make sure, borderextract is always the last plugin on an Osmosis-pipe.
First you need an Openstreetmap dump. You can download a recent one from Geofabrik or from Cloudmade.
To extract all Swiss canton borders (admin-level = 4) to KML from a Switzerland-dump use Osmosis as follows:
<your path>/osmosis-0.41/bin/osmosis --read-pbf file=switzerland.osm.pbf --be levels=4 type=kml
Up to version 5.6 MySQL had only rudimentary support for spatial queries. You can insert the SQL-file produced by borderextract into a pre-5.6 database, but querying for points contained in the polygons is limited to the MBR (minimum bounding rectangle).
This has changed since version 5.6.1. Now a new group of spatial analysis functions is available, which takes the real polygon shape into respect and not just their MBR. They all start with ST_. See this documentation: Functions That Test Spatial Relationships Between Geometries.
To import the SQL-file, you first need to create the appropriate Borders table which later will contain the border-polygons:
CREATE TABLE Borders ( Name VARCHAR(255), Admin_Level TINYINT NOT NULL, Border GEOMETRY NOT NULL, SPATIAL INDEX(Border) ) ENGINE=MyISAM;
Then import the SQL-file
cat borderextract.sql | mysql -u -p
This query will select all borders the given point is in:
mysql> SELECT Name,Admin_Level FROM Borders WHERE ST_Within(GeomFromText('POINT(8.306473 47.473767)'), Border); +--------+-------------+ | Name | Admin_Level | +--------+-------------+ | Aargau | 4 | +--------+-------------+ 1 row in set (0.00 sec)
Extracting all european state borders from a europe.osm.pbf (~7.3 GB of file data) takes roughly 35 minutes on a Core i7 2600 and uses 57 GB of temporary disk space during processing. When the dump-size increases, the time needed to extract the borders grows. So you might be better up splitting huge files along the borders into several small ones and processing them in a serial order.