Cannot detect SRID for layer

[nutiteq]

WIth OSM planet rendering hangs with following query:

     (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

[springmeyer]

Interesting that your SRID is 0 - perhaps an osm2pgsql bug when used with postgis 2.0 - needs looking into.

Can you post the result of explain analyze ... on your slow query (and the fast one).

[nutiteq]

Yes, this 0 SRID is interesting. In PostGIS 2.0 geometry_columns is a view which is based on real data, not physical table, maybe osm2pgsql is not aware of that. I now got also explain results

Limit  (cost=69072282.78..69072282.79 rows=1 width=32) (actual time=3556751.392..3556751.402 rows=1 loops=1)
->  Subquery Scan on leisure  (cost=69072282.78..69834319.56 rows=50802452 width=32) (actual time=3556751.381..3556751.381 rows=1 loops=1)
->  Sort  (cost=69072282.78..69199288.91 rows=50802452 width=424) (actual time=3556415.906..3556415.906 rows=1 loops=1)
           Sort Key: planet_osm_polygon.z_order, planet_osm_polygon.way_area
           Sort Method: external merge  Disk: 3229576kB
           ->  Seq Scan on planet_osm_polygon  (cost=0.00..4226516.67 rows=50802452 width=424) (actual time=1.367..1240575.917 rows=7287274 loops=1)
                 Filter: ((way IS NOT NULL) AND ((landuse IS NOT NULL) OR (leisure IS NOT NULL) OR (aeroway = ANY ('{apron,aerodrome}'::text[])) OR (amenity = ANY ('{parking,university,college,school,hospital,kindergarten,grave_yard}'::text[])) OR (military = ANY ('{barracks,danger_area}'::text[])) OR ("natural" = ANY ('{field,beach,desert,heath,mud,wood,sand,scrub}'::text[])) OR (power = ANY ('{station,sub_station,generator}'::text[])) OR (tourism = ANY ('{attraction,camp_site,caravan_site,picnic_site,zoo}'::text[])) OR (highway = ANY ('{services,rest_area}'::text[]))))
Total runtime: 3558503.044 ms

My quick query was:

osm=# explain analyze select ST_AsEwkt(way) from planet_osm_polygon limit 1;
                                                        QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..0.05 rows=1 width=32) (actual time=61.970..61.972 rows=1 loops=1)
 ->  Seq Scan on planet_osm_polygon  (cost=0.00..2376274.55 rows=51058844 width=32) (actual time=61.966..61.966 rows=1 loops=1)
Total runtime: 62.053 ms

(3 rows)

Just upgraded to latest mapnik from github - no difference there.

[springmeyer]

thanks for the extra details - will be helpful to replicate. In the meantime you should just pass:

<Parameter name="srid">900913</Parameter>

in your osm.xml config to ensure this lag does not happen (allows mapnik to skip the query, both the one that is failing that hits geometry_columns and the one that is hanging).

Please, do this and let me know if you see other hangs later on with these big tables. There may be other postgis 2.x subtle incompatibilities. I'll try to get postgis 2.x running locally to replicate the first issue this week, latest early next week.

[nutiteq]

Adding this finally helped. There was one another issue with PostGIS 2.0: there is no asBinary() function. My quick check did not find any reference that it was deprecated or something. I restored it with following SQL in database, but perhaps in mapnik you should use st_asbinary(geometry) which is there for sure.

CREATE OR REPLACE FUNCTION asbinary(geometry)
  RETURNS bytea AS
'SELECT st_asbinary($1)'
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;
ALTER FUNCTION asbinary(geometry) OWNER TO ubuntu;

[springmeyer]

yep, w'ere aware of the ST_binary issue and have a separate ticket for it which will get fixed shortly, thanks and nice workaround.

[springmeyer]

update on this: I installed postgis trunk and osm2pgsql trunk and did not see this problem (of srid=0), so osm2pgsql must have been fixed upstream to properly support postgis 2.0. Anyway, I've also fixed the AsBinary thing as per #956.

Going to leave this issue open until i have time to look into the issue of the slow query, if the srid is not known (since likely this condition could still be hit by some edge cases).

[springmeyer]

closing, not actionable. original problem was 0 srid written by osm2pgsql, not much we can do on the mapnik side.