mapbox / mapbox/osm-bright

osm2pgsql imported planet slow queries

Open
#58 6 comments 0 reactions 0 assignees View on GitHub
Dominant language
CartoCSS
Stars
703
Forks
222
PR merge metrics
No merged PRs in 30d

Description

I imported a full planet via osm2pgsql.

For example "SELECT way, way_area AS area, COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type
FROM planet_osm_polygon
WHERE way_area > 100000
ORDER BY way_area DESC
) AS data" query

and

SELECT ST_AsBinary("way") AS geom,"area","name","type" from ( SELECT COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type,
name, way_area AS area,
ST_PointOnSurface(way) AS way
FROM planet_osm_polygon
WHERE name IS NOT NULL
AND ST_SetSRID(way,900913) && ST_SetSRID('BOX3D(242458.2537205777 5068997.967759746,243681.2461731433 5070220.960212312)'::box3d, 900913)
AND ST_IsValid(way)
 UNION ALL

  SELECT 'building' AS type, name, way_area AS area,
    ST_PointOnSurface(way) AS way
  FROM planet_osm_polygon
  WHERE name IS NOT NULL
    AND building NOT IN ('', 'no', '0', 'false')
    AND ST_SetSRID(way,900913) && ST_SetSRID('BOX3D(242458.2537205777 5068997.967759746,243681.2461731433 5070220.960212312)'::box3d, 900913)
    AND ST_IsValid(way)
  ORDER BY area DESC
) AS data

hangs forever. Does anyone encounter this problem?

When I import only my city rendering also takes reasonable time.

Contributor guide

Open the contributing guide

Research direction

The report provides slow SQL queries against planet_osm_polygon after a full osm2pgsql import, but names no repository file or test. Start by reproducing the queries on a full-planet import and inspect the database behavior; done would require identifying a confirmed cause and documenting a project-specific resolution or guidance.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases, performance
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.