NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum

I have installed openstreetmap postgis database using .osm file for Scotland using osm2pgsql.

I have also imported a set of polygons of parliamentary constituencies in Scotland called 'area10'

I want to find out how much cycle path distance is within each constituency.

If I run this query:

SELECT m.name, sum(ST_Length(r.way))/1000 as roads_km FROM planet_osm_line r, area10 m WHERE ST_Contains(m.way,r.way) AND r.highway = 'cycleway' OR r.highway = 'path' AND bicycle = 'yes' GROUP BY m.name

The query runs indefinitely or at least for 45mins or so until my computer overheats but that is another issue...

If I run this query (without 'OR' operator):

SELECT m.name, sum(ST_Length(r.way))/1000 as roads_km FROM planet_osm_line r, area10 m WHERE ST_Contains(m.way,r.way) AND r.highway in ('cycleway','path') GROUP BY m.name

This returns within about 30 seconds:

`name | roads_km

  • Mid Fife and Glenrothes P Const | 87.17134
  • Glasgow Provan P Const | 19.11762
  • Eastwood P Const | 19.97493`

...

Question: So why does using the OR operator in the WHERE clause greatly increase the time of the query? I really need to use the OR operator as there are lots of different highway= tags which I would like to include:

OR highway = 'path' AND bicycle = 'designated' OR highway = 'footway' AND bicycle = 'designated' OR highway='footway' AND bicycle = 'yes'

etc

I have also tried a similar query using the h-store tag values with the same issue.

Any help very much appreciated. Thank you.

asked 03 Jan '15, 10:44

Hawkeye's gravatar image

Hawkeye
2412511
accept rate: 0%

edited 03 Jan '15, 11:59

aseerel4c26's gravatar image

aseerel4c26 ♦
32.6k18248554


You have your parentheses wrong. Try

WHERE ST_Contains(m.way,r.way) AND 
   (r.highway = 'cycleway' OR (r.highway = 'path' AND bicycle = 'yes'))
GROUP BY m.name

Also, you might want to do something like

ST_Length(r.way::geography)

to actually get the length in metres instead of Google Mercator units which are not exactly metres.

permanent link

answered 03 Jan '15, 10:58

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
82.5k927201273
accept rate: 23%

2

Thank you! It's working:

SELECT m.name, sum(ST_Length(ST_Transform(r.way,4326)::geography))/1000 as roads_km FROM planet_osm_line r, area10 m WHERE ST_Contains(m.way,r.way) AND (r.highway = 'cycleway' OR (r.highway = 'path' AND bicycle = 'yes')) GROUP BY m.name

(03 Jan '15, 11:47) Hawkeye

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×165
×147
×134
×92
×1

question asked: 03 Jan '15, 10:44

question was seen: 4,676 times

last updated: 03 Jan '15, 11:59

NOTICE: help.openstreetmap.org is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum