mapnik/mapnik — Issue #944: Cannot detect SRID for layer
Author: nutiteq
Created: 2011-11-09 09:43:27+00:00
State: open

WIth OSM planet rendering hangs with following query:

``` SELECT ST_SRID("way") AS srid FROM
(select way,aeroway,amenity,landuse,leisure,man_made,military,"natural",power,tourism,name,highway,
case when religion in ('christian','jewish') then religion else 'INT-generic'::text end as religion
from planet_osm_polygon
where landuse is not null
or leisure is not null
or aeroway in ('apron','aerodrome')
or amenity in ('parking','university','college','school','hospital','kindergarten','grave_yard')
or military in ('barracks','danger_area')
or "natural" in ('field','beach','desert','heath','mud','wood','sand','scrub')
or power in ('station','sub_station','generator')
or tourism in ('attraction','camp_site','caravan_site','picnic_site','zoo')
or highway in ('services','rest_area')
order by z_order,way_area desc
) as leisure
WHERE "way" IS NOT NULL LIMIT 1;
```
Freezes means that the query stays running for ages (hours at least, maybe even more), and IO is heavily consumed, so I suspect full scan happening. I try to make explain analyze, but not sure if it will complete any time soon.

Style file is osm "mapnik" style converted for mapnik2. The layer definition is following. It seems to have SRID definition already, so why this query is done anyway?
```

landcover

(select way,aeroway,amenity,landuse,leisure,man_made,military,"natural",power,tourism,name,highway,
case when religion in ('christian','jewish') then religion else 'INT-generic'::text end as religion
from planet_osm_polygon
where landuse is not null
or leisure is not null
or aeroway in ('apron','aerodrome')
or amenity in ('parking','university','college','school','hospital','kindergarten','grave_yard')
or military in ('barracks','danger_area')
or "natural" in ('field','beach','desert','heath','mud','wood','sand','scrub')
or power in ('station','sub_station','generator')
or tourism in ('attraction','camp_site','caravan_site','picnic_site','zoo')
or highway in ('services','rest_area')
order by z_order,way_area desc
) as leisure

postgis

ubuntu
osm

false

-20037508,-19929239,20037508,19929239

```

Some queries. Btw the second one is fast:

osm=# select * from geometry_columns;
f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type
-----------------+----------------+--------------------+-------------------+-----------------+------+----------
osm | public | planet_osm_line | way | 2 | 0 | GEOMETRY
osm | public | planet_osm_point | way | 2 | 0 | GEOMETRY
osm | public | planet_osm_polygon | way | 2 | 0 | GEOMETRY
osm | public | planet_osm_roads | way | 2 | 0 | GEOMETRY
(4 rows)

osm=# select ST_SRID("way") AS srid FROM planet_osm_polygon limit 1;
srid
--------
900913
(1 row)

Versions:
PostGIS 2.0
Postgres 9.1