osm2po library does a great job in creating the data required by pgRouting to calculate theshortest paths. But I need the shape (Geometry - LineString 4326) to be extracted when osm2po generates the sql file for postgresql out of the .osm file.
Is there anyway to do this? Or maybe external library?
I want the shape to be Geometry(LineString,4326) to fit into my database.
osm2po, the latest version supports this. With the version osm2po 4.8.8 I have successfully extracted the road links and their shapes and managed to import this to postgresql!
There is no option to change the default output format. You'll have to modify the code (pgRoutingWriter.java) and create your own plugin. The basic steps for writing a osm2po-plugin are explained here
package de.cm.osm2po.converter;
import static de.cm.osm2po.misc.Utils.DF;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Date;
import de.cm.osm2po.Config;
import de.cm.osm2po.Version;
import de.cm.osm2po.logging.Log;
import de.cm.osm2po.misc.WkbUtils;
import de.cm.osm2po.model.Node;
import de.cm.osm2po.model.SegmentedWay;
import de.cm.osm2po.model.WaySegment;
import de.cm.osm2po.primitives.InStream;
import de.cm.osm2po.primitives.InStreamDisk;
import de.cm.osm2po.primitives.VarString;
import de.cm.osm2po.primitives.VarTypeDesk;
public class PgRoutingWriter implements PostProcessor {
private static byte[] KOMMA = ",".getBytes();
private static byte[] SEMIKOLON = ";".getBytes();
private static byte[] NEWLINE = "\n".getBytes();
private static final char SINGLEQUOTE = '\'';
private static final char SINGLESLASH = '\\';
private static final byte[] DOUBLEQUOTE = "\'\'".getBytes();
private static final byte[] DOUBLESLASH = "\\\\".getBytes();
private static final byte[] SQLNULL = "null".getBytes();
private boolean asMultilineString;
@Override
public void run(Config config, int index) throws Exception {
File dir = config.getWorkDir();
String prefix = config.getPrefix();
Log log = config.getLog();
String key = "postp." + index + ".writeMultiLineStrings";
this.asMultilineString = Boolean.valueOf(
config.getProps().getProperty(key, "false"));
String tableName = (prefix + "_2po_4pgr").toLowerCase(); // Postgres-Problem
String fileName = prefix + "_2po_4pgr.sql";
File waysInFile = new File(dir, Segmenter.SEGMENTS_FILENAME);
if (!waysInFile.exists()) {
log.error("File not found : " + waysInFile);
return;
}
InStream inStream = new InStreamDisk(waysInFile);
OutputStream os = null;
File sqlOutFile = null;
if (config.isPipeOut()) {
os = System.out;
log.info("Writing results to stdout");
} else {
sqlOutFile = new File(dir, fileName);
os = new BufferedOutputStream(
new FileOutputStream(sqlOutFile), 0x10000 /*64k*/);
log.info("Creating sql file " + sqlOutFile.toString());
}
byte varType = inStream.readByte();
if (varType != VarTypeDesk.typeIdOf(SegmentedWay.class))
throw new RuntimeException("Unexpected VarType " + varType);
String lineformat = this.asMultilineString ? "MULTILINESTRING" : "LINESTRING";
os.write((""
+ "-- Created by : " + Version.getName() + "\n"
+ "-- Version : " + Version.getVersion() + "\n"
+ "-- Author (c) : Carsten Moeller - info@osm2po.de\n"
+ "-- Date : " + new Date().toString()
+ "\n\n"
+ "DROP TABLE IF EXISTS " + tableName + ";"
+ "\n"
+ "-- SELECT DropGeometryTable('" + tableName + "');"
+ "\n\n"
+ "CREATE TABLE " + tableName + "("
+ "id integer, osm_id bigint, "
+ "osm_name character varying, osm_meta character varying, "
+ "osm_source_id bigint, osm_target_id bigint, "
+ "clazz integer, flags integer, "
+ "source integer, target integer, "
+ "km double precision, kmh integer, "
+ "cost double precision, reverse_cost double precision, "
+ "x1 double precision, y1 double precision, "
+ "x2 double precision, y2 double precision"
+ ");\n"
+ "SELECT AddGeometryColumn('" + tableName + "', "
+ "'geom_way', 4326, '" + lineformat + "', 2);\n")
.getBytes());
byte[] INSERT = ("\nINSERT INTO " + tableName + " VALUES ").getBytes();
SegmentedWay way = new SegmentedWay();
long n = 0, g = 0;
while (!inStream.isEof()) {
way.readFromStream(inStream);
long osm_id = way.getId();
byte clazz = way.getClazz();
int flags = way.getFlags();
byte[] nameBytes = this.toUTF8QuotedSql(way.getName());
byte[] metaBytes = this.toUTF8QuotedSql(way.getMeta());
int kmh = way.getKmh(); if (kmh <= 0) kmh = 1;
boolean isOneWay = way.isOneWay();
for (int i = 0; i < way.getSegments().length; i++) {
WaySegment waySegment = way.getSegments()[i];
int id = waySegment.getId();
int source = waySegment.getSourceId();
int target = waySegment.getTargetId();
Node n1 = waySegment.getNodes()[0];
Node n2 = waySegment.getNodes()[waySegment.getNodes().length -1];
long osmSourceId = n1.getId();
long osmTargetId = n2.getId();
float km = (float) waySegment.calcLengthKm();
float cost = km / kmh;
float reverse_cost = isOneWay ? 1000000f : cost;
String geom_way = WkbUtils.latLons2LineWkbHex(
waySegment.getNodes(), this.asMultilineString);
// Gruppenbildung macht SQL erwiesenermassen schneller und kuerzer.
if (g == 25) {
os.write(SEMIKOLON);
os.write(NEWLINE);
g = 0;
}
if (++g == 1) os.write(INSERT); else os.write(KOMMA);
os.write(NEWLINE);
os.write(("(" + id + ", " + osm_id + ", ").getBytes());
os.write(nameBytes);
os.write(", ".getBytes());
os.write(metaBytes);
os.write((", "
+ osmSourceId + ", " + osmTargetId + ", "
+ clazz + ", " + flags + ", "
+ source + ", " + target + ", "
+ km + ", " + kmh + ", " + cost + ", " + reverse_cost + ", "
+ n1.getLon() + ", " + n1.getLat() + ", "
+ n2.getLon() + ", " + n2.getLat() + ", "
+ "'" + geom_way + "'" + ")")
.getBytes());
if (++n % 50000 == 0) log.progress(DF(n) + " Segments written.");
}
}
// Rest schreiben.
os.write(SEMIKOLON);
os.write(NEWLINE);
log.info(DF(n) + " Segments written.");
os.write(("\n"
+ "ALTER TABLE " + tableName + " ADD CONSTRAINT pkey_" + tableName + " PRIMARY KEY(id);\n"
+ "CREATE INDEX idx_" + tableName + "_source ON " + tableName + "(source);\n"
+ "CREATE INDEX idx_" + tableName + "_target ON " + tableName + "(target);\n"
+ "-- CREATE INDEX idx_" + tableName + "_osm_source_id ON " + tableName + "(osm_source_id);\n"
+ "-- CREATE INDEX idx_" + tableName + "_osm_target_id ON " + tableName + "(osm_target_id);\n"
+ "-- CREATE INDEX idx_" + tableName + "_geom_way ON " + tableName
+ " USING GIST (geom_way GIST_GEOMETRY_OPS);\n")
.getBytes());
inStream.close();
if (sqlOutFile != null) {
os.close();
log.info("commandline template:\n"
+ "psql -U [username] -d [dbname] -q -f \""
+ sqlOutFile.getAbsolutePath() + "\"");
}
}
private byte[] toUTF8QuotedSql(VarString strToEscape) {
if (null == strToEscape || strToEscape.length() == 0)
return SQLNULL;
return strToEscape // Caution! No Copy()
.replaceAsciiByte(SINGLEQUOTE, DOUBLEQUOTE)
.replaceAsciiByte(SINGLESLASH, DOUBLESLASH)
.quote(SINGLEQUOTE).getUTF8Bytes();
}
}
IMHO the question is more that you just want to have the shape of OSM boundary of Germany, right?
This you can get in multiple different ways:
This you can convert using OGR/QGIS/... to shapefile and then import to your database.
