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

Yurik moved this task from All map-related tasks to Tilerator on the Maps board.May 12 2016, 10:59 PM
MaxSem moved this task from Tilerator to Styling & datasources on the Maps board.May 27 2016, 9:29 PM
Yurik added a subscriber: Pnorman.EditedJun 19 2016, 12:10 AM

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

Yurik claimed this task.Jun 19 2016, 12:46 AM

I am experimenting with the maps-test2001:

CREATE INDEX planet_osm_polygon_wikidata ON planet_osm_polygon ((tags -> 'wikidata')) WHERE tags ? 'wikidata';
Yurik added a subscriber: Gehel.EditedJun 19 2016, 1:04 AM

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)

Yurik added a comment.EditedJun 19 2016, 1:55 AM

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)
Yurik added a comment.EditedJun 20 2016, 1:19 AM

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

Yurik closed this task as Resolved.Jun 26 2016, 8:22 PM

done, pending deployment