Page MenuHomePhabricator

Implement quick shape retrieval from Postgres based on WikidataID
Closed, ResolvedPublic

Description

OSM database contains WikidataID in the tag->'wikidata' hstore column. The simplest solution seems to add this conditional index:

CREATE INDEX planet_osm_polygon_wikidata ON planet_osm_polygon ((tags -> 'wikidata')) WHERE tags ? 'wikidata';

Event Timeline

Per @Pnorman, we are using this default style with osm2pgsql, which we should alter to add the wikidataid. See also our current install script

I am experimenting with the maps-test2001:

CREATE INDEX planet_osm_polygon_wikidata ON planet_osm_polygon ((tags -> 'wikidata')) WHERE tags ? 'wikidata';

Querying all readable columns from the polygon table using the new wikidata id index (above).
Note that we check that tags contains 'wikidata' key AND it is equal to our value. Without this, our index is not being used, and it does full table scan instead.

SELECT
osm_id,access,"addr:housename","addr:housenumber","addr:interpolation",admin_level,aerialway,aeroway,amenity,area,barrier,bicycle,brand,bridge,boundary,building,construction,covered,culvert,cutting,denomination,disused,embankment,foot,"generator:source",harbour,highway,historic,horse,intermittent,junction,landuse,layer,leisure,lock,man_made,military,motorcar,name,"natural",office,oneway,operator,place,population,power,power_source,public_transport,railway,ref,religion,route,service,shop,sport,surface,toll,tourism,"tower:type",tracktype,tunnel,water,waterway,wetland,width,wood,z_order,way_area, tags
FROM planet_osm_polygon
WHERE tags ? 'wikidata' and tags->'wikidata' = 'Q1166';

I found the following columns interesting: admin_level, boundary, name, ref, z_order, way_area. I wonder if we could use the way_area to figure out the best way simplification? Reading relevant literature.

Some interesting results searching for (tags->'wikidata' IN ('Q1166', 'Q16', 'Q30', 'Q46', 'Q2', 'Q98')) (Michigan, Canada, USA, Europe, Earth, Pacific Ocean): found 1 Michigan, 3 Canadas, and about 55 USAs. No other items were given (I was hoping for Europe)

Simplification notes from @Pnorman moved to T138154#2392658

Yurik renamed this task from Add wikidata id column to OSM database to Implement quick shape retrieval from Postgres based on WikidataID.Jun 19 2016, 9:54 PM
Yurik updated the task description. (Show Details)

This service is now implemented and can be enabled as part of Maps (Kartotherian) by adding this config block as described in the README:

Make sure to create OSM index as specified in the description above

geoshapes:
  host: localhost
  database: gis
  table: planet_osm_polygon
  user: ...
  password: ...

TODO: decide on what simplification formula to use

done, pending deployment