Requêtes osmosis + gis

Embed Size (px)

Citation preview

  • 1. Requtes osmosis + GIS Frdric Rodrigo La Cantine - Paris 07/10/2011 (c)left 2011 - CC-BY-SA v3.0 @

2.

  • osmosis
  • Charge un extract OSM en base
  • Prtraitement pour faire des requtes
  • Ajout d'index sur les tags

3. Ajout de colonne gomtrique aux ways 4.

  • Schma
  • relations
  • ways
  • nodes
  • way_nodes
  • relation_members

5.

  • Schma : nodes

Colonne|Type| Modificateurs --------------+-----------------------------+--------------- id| bigint| non NULL version| integer| non NULL user_id| integer| non NULL tstamp| timestamp without time zone | non NULL changeset_id | bigint| non NULL tags| hstore|geom| geometry| 6.

  • Requte sur nodes & hstore
  • SELECT tags FROM nodes;
    • "man_made"=>"water_tower", "ele"=>"120"
  • SELECT tags->name FROM nodes;
    • "Lassiette"
  • SELECT tags->name , tags->ref:INSEE FROM nodes WHERE tags?place
    • "Saint-Jean-de-Luz", "64483"

7.

  • Schma : ways

Colonne|Type| Modificateurs--------------+-----------------------------+--------------- id| bigint| non NULL version| integer| non NULL user_id| integer| non NULL tstamp| timestamp without time zone | non NULL changeset_id | bigint| non NULL tags| hstore|nodes| bigint[]|bbox| geometry|linestring| geometry|Colonne|Type| Modificateurs-------------+---------+--------------- way_id| bigint| non NULL node_id| bigint| non NULL sequence_id | integer | non NULL 8.

  • Requte sur ways
  • SELECT tags FROM ways;
    • "building"=>"yes", "wall"=>"no"
  • SELECT tags->name FROM ways;
    • "Rue des Lilas"
  • SELECT tags->name , tags->ref FROM ways WHERE tags?highway
    • "Avenue Tiers", "N 32"

9.

  • Schma : relations

Colonne|Type| Modificateurs--------------+-----------------------------+--------------- id| bigint| non NULL version| integer| non NULL user_id| integer| non NULL tstamp| timestamp without time zone | non NULL changeset_id | bigint| non NULL tags| hstore|Colonne|Type| Modificateurs-------------+--------------+--------------- relation_id | bigint| non NULL member_id| bigint| non NULL member_type | character(1) | non NULL member_role | text| non NULL sequence_id | integer| non NULL 10.

  • Requte sur relations
  • SELECT relation.id FROM relations JOIN relation_members ON relations.id = relation_members.relation_id AND relation_members.member_type = 'W' JOIN ways ON relation_members.member_id = ways.id WHERE relations.tags?'type' AND relations.tags->'type' = 'road' AND ways.tags?'highway' AND ways.tags->'highway' = 'service'

11.

  • Requtes gospatiales
  • Extension PostGIS Postgres
    • ST_Distance
  • 12. ST_NPoints

13. ST_X, ST_Y 14. ST_Buffer 15. ST_Union 16. ST_Intersects 17. ST_AsText, ST_AsKML, ST_AsGeoJSON 18. 19.

  • Requte gospatiale
  • SELECT po.id, MIN(ST_Distance(po.geom, po.pb) AS dist FROM nodes AS pb, nodes AS po WHERE pb.tags?'amenity' AND pb.tags->'amenity' = 'post_box' AND po.tags?'amenity' AND po.tags->'amenity' = 'post_office' AND GROUP BY po.id
  • Utilisation des index
  • En unit de rfrence

20.

  • Export gospatial
  • Pgsql2shp -> QGis
  • SELECT id, tags->'source' AS source, ST_Simplify(linestring, 1e-3) AS geom FROM ways WHERE tags?'source' AND tags->'source' LIKE '%Cartographes Associs%'
  • Export plus facilement manipulable dans QGis
  • Cas des tags valeurs multiples

21.

  • Manque junction=roundabout
  • Un giratoire c'est quoi?

Moins de 25 nodes Moins de 70m de diamtre Plus de 60% d'un cercle 22.

  • Manque junction=roundabout

SELECT ways.id FROM ways WHERE -- tags ways.tags?'highway' AND (NOT ways.tags?'junction' OR ways.tags->'junction' != 'roundabout') AND -- geometry ST_NPoints(linestring) < 24AND ST_MaxDistance(linestring,linestring) < 70AND ST_Area(linestring) / ST_Area(ST_MinimumBoundingCircle(linestring)) > 0.6