Open thisisaaronland opened 7 years ago
For example:
DEBUG:root:SELECT id, parent_id, placetype_id, meta, ST_AsGeoJSON(geom), ST_AsGeoJSON(centroid) FROM whosonfirst WHERE (ST_Intersects(ST_GeomFromGeoJSON(%s), geom) OR ST_Intersects(ST_GeomFromGeoJSON(%s), centroid)) AND is_superseded=%s AND is_deprecated=%s AND placetype_id=%s LIMIT 5000 OFFSET 0
INFO:root:find intersecting descendants of placetype county (for 85671863 (Francisco Morazán))
...
DEBUG:root:SELECT COUNT(id) FROM whosonfirst WHERE (ST_Intersects(ST_GeomFromGeoJSON(%s), geom) OR ST_Intersects(ST_GeomFromGeoJSON(%s), centroid)) AND is_superseded=%s AND is_deprecated=%s AND placetype_id=%s
Two steps forward, one step back...
All the neighbourhoods in Montreal:
./bin/wof-pgis-intersects -placetype neighbourhood /usr/local/data/whosonfirst-data/data/101/736/545/101736545.geojson
2017/07/01 20:20:18 COUNT GEOM 83
2017/07/01 20:20:18 COUNT CENTROIDS 0
2017/07/01 20:20:18 COUNT 83
2017/07/01 20:20:18 TIME total 9.503431783s
2017/07/01 20:20:24 TIME 6.372003652s
2017/07/01 20:20:24 TIME ALL 15.8756241s
2017/07/01 20:20:24 ROWS 83
All the neighbourhoods in Quebec:
./bin/wof-pgis-intersects -placetype neighbourhood /usr/local/data/whosonfirst-data/data/136/251/273/136251273.geojson
2017/07/01 20:22:23 COUNT GEOM 298
2017/07/01 20:22:23 COUNT CENTROIDS 4
2017/07/01 20:22:23 COUNT 302
2017/07/01 20:22:23 TIME total 1m37.449554181s
2017/07/01 20:22:24 sql: Scan error on column index 6: unsupported Scan, storing driver.Value type <nil> into type *string
Scan error on column index 6?
Known slow queries:
INFO:root:find intersecting descendants of placetype macrocounty (for 85679627 (Penghu))
Basically implement this in Go, or more specifically separate goroutines (for geom and centroid queries) and waitgroups, and emit... something, CSV?
https://github.com/whosonfirst/py-mapzen-whosonfirst-spatial/blob/master/mapzen/whosonfirst/spatial/postgres.py#L85-L189