pnorman

# This osm2pgsql style file is one that will generate no columns from tags
# It is designed as a starting point for you to develop your own, or for 
# use where all OSM tags are in hstore.

# See default.style for documentation on all the flags

# OsmType  Tag                 Type    Flags
# Insert your own columns here, or change phstore to polygon below
node,way    area:highway            text    phstore
node,way    aeroway                 text    phstore

pnorman

# This is the default osm2pgsql .style file that comes with osm2pgsql. 
# 
# A .style file has 4 columns that define how OSM objects end up in tables in 
# the database and what columns are created. 
# 
# OsmType: This is either "node", "way" or "node,way" and indicates if this tag 
# applies to nodes, ways, or both. 
# 
# Tag: The tag 
# 

pnorman

(SELECT 
    way,
    CASE 
      WHEN place='country' THEN 1 
      WHEN capital='yes' AND admin_level='2' THEN 10 
      WHEN place='state' THEN 20 
      WHEN place='city' THEN 21 
      WHEN place='town' THEN 30 
      ELSE NULL 
    END as prio,

pnorman

(SELECT 
    way, 
    CASE 
      WHEN place='country' THEN 1 
      WHEN place='state' THEN 10 
      WHEN tags->'is_capital'='country' THEN 20 
      WHEN place='city' THEN 21 
      WHEN place='town' THEN 30 
      ELSE 100 
    END as prio, 

pnorman

package osmapi
/*
This file is part of openstreetmap-api-testsuite

Copyright (c) 2013 Paul Norman, released under the MIT license.

Defines various test cases for nodes with diff processing
*/
import io.gatling.core.Predef._
import io.gatling.http.Predef._

pnorman

== cgimap ==

HTTP/1.1 200 OK
Date: Tue, 16 Jul 2013 03:41:53 GMT
Server: Apache/2.2.22 (Ubuntu)
Content-Disposition: attachment; filename="map.osm"
Content-Encoding: identity
Cache-Control: private, max-age=0, must-revalidate
Vary: Accept-Encoding
Content-Type: text/xml; charset=utf-8

pnorman

test done                              cold          hot   mem
linestring, apidb, extract         33m28.449s   28m55.834s    7G
way_nodes, pgsnapshot, extract     15m13.645s   10m23.546s    6G
linestring, pgsnapshot, extract    14m22.915s   10m23.181s    4G
parent_ways, pgsnapshot, extract*  14m39.141s   10m12.592s    5G
way_nodes, pgsnapshot, full        37m2.924s    10m10.498s   11G
linestring, pgsnapshot, full       27m40.750s   10m25.437s  8.5G
* Different DB, but same data

pnorman

http://192.168.1.5:31337/api/0.6/map?bbox=-122.8926909,49.2490143,-122.892648,49.2490388
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8926909,49.2490115,-122.892648,49.2490143
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927063,49.2490388,-122.8926325,49.2490416
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927063,49.2490115,-122.8926909,49.2490388
http://192.168.1.5:31337/api/0.6/map?bbox=-122.892648,49.2490115,-122.8926325,49.2490388
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927063,49.2490059,-122.8926325,49.2490115
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8926325,49.2490059,-122.8926201,49.2490416
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927243,49.2490059,-122.8927063,49.2490416
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927243,49.2490416,-122.8926201,49.2490483
http://192.168.1.5:31337/api/0.6/map?bbox=-122.8927243,49.2489979,-122.8926201,49.2490059

pnorman

-- Both queries produce the same results. 
-- tmp_nodes and tmp_ways populated by geometries intersecting ST_SetSRID(ST_MakeBox2D(ST_Point(-123.4387,49.3308),ST_Point(-123.3034,49.4248)),4326)
-- ~5k nodes, ~400 ways, ~600 backfilled nodes

-- 150k set from ST_SetSRID(ST_MakeBox2D(ST_Point(-123.2748,49.1929),ST_Point(-122.9850,49.3058)),4326)

-- subselect variant
EXPLAIN ANALYZE INSERT INTO tmp_nodes
  SELECT n.id,n.version,n.user_id,n.tstamp,n.changeset_id,n.tags, 
    ST_X(n.geom) * 10000000, ST_Y(n.geom) * 10000000 

pnorman

CREATE TEMPORARY TABLE tmp_nodes (
id bigint primary key, 
geom geometry, -- postgis geometry
-- actually other columns as well
tags hstore);

-- nodes has the same columns and indexes as tmp_nodes, bout about 2 billion rows
INSERT INTO tmp_nodes SELECT * FROM nodes WHERE ...; -- assorted WHERE clauses
-- more inserts with joins, complicated conditions, etc