Script to pull OSM geometry data

[nealf]

General steps for this component:

• query the database to get the list of rows that don't have geometry associated with them
• loop over those results and use the OSM ID field to query OSM and get geometry
• insert that geometry back into the Postgresql database

[nealf]

To pull info from OSM, we can query it with Overpass. Overpass Turbo let's you interactively play around with and run some queries. This page has some example queries, and I used it to create an example that grabbed a particular Way ID:

way(32515097);
/*added by auto repair*/
(._;>;);
/*end of auto repair*/
out;

We'll need to find someway to then insert it into our database, which may require something like osmtogeojson to convert it to geojson that can then be inserted into the database.

[nealf]

Our Postgres parks database REST endpoint. So for instance, to get all of the rows in the parks table that have no geometry, you can hit this endpoint: http://parks.api.codefornrv.org/parks?geom=is.null&select=id,osm_info

That will give you the row id (in our database) and the osm_info (see the wiki for how that is structured)

[nealf]

I've setup a basic instance of osmtogeojson. At the moment it requires the OSM JSON output from Overpass (use [out:json]; at the beginning of your query to get a json reponse instead of xml). Just send a POST request to osmtogeojson.codefornrv.org with a Content-Type: application/json and a body with the json response from OSM. It'll return a geojson object that we can then put into our database. Depending on what we can make work with the PostgREST api, we could extend this server to actually do the inserting into the db as well.

[nealf]

Took this Overpass Turbo query:

[out:json];
way(32515097);
(._;>;);
out;

and turned it into a url to query Overpass and get JSON: http://overpass-api.de/api/interpreter?data=[out:json];way(32515097);(._;%3E;);out;

[nealf]

SQL Query to update row example:

UPDATE parks.parks SET geom = ST_Multi(ST_GeomFromGeoJSON
    ('{
    "type":"Polygon",
    "coordinates":
    [
        [
            [1,1]
        ]
    ],
    "crs":{"type":"name","properties":{"name":"EPSG:4326"}}
}')) 
WHERE id = 7;

[nealf]

Once you get the OSM data and convert it to geojson, we can insert it by running a couple of SQL statements (can probably be combined into one, or at least a function):

SELECT ST_AsText(ST_Collect(ST_SetSRID(ST_GeomFromGeoJSON(feat->>'geometry'),4326)))
FROM (
    SELECT json_array_elements('{
    "type": "FeatureCollection",
    "features": [...more geojson is here...]
  }'::json->'features') AS feat
) AS f;

And then taking that output and plugging it into something like:

UPDATE parks.parks SET geom = ST_SetSRID(
  ST_GeomFromText( 'GEOMETRYCOLLECTION( POLYGON...' ), 
  4326) 
WHERE id = 1;

[mechy2k2000]

I got the parks that had osm info filled with geom data. Check it out

[nealf]

Here's the SQL to run at the end that will add/update the centroids for all of them:

UPDATE parks.parks SET point_location = ST_Centroid(geom) WHERE geom IS NOT NULL;