This is a static archive of our old OpenStreetMap Help Site. Please post any new questions and answers at community.osm.org.

How to fix postgis query using ‘or’ operator when group by on polygon name

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.

query postgresql polygon postgis or_operator

asked 03 Jan '15, 10:44

Hawkeye
241●2●5●11
accept rate: 0%

edited 03 Jan '15, 11:59

aseerel4c26 ♦
32.6k●18●248●554

One Answer:

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.

answered 03 Jan '15, 10:58

Frederik Ramm ♦
82.5k●92●720●1273
accept rate: 23%

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