| 1 | #!/usr/bin/python |
|---|
| 2 | # |
|---|
| 3 | # Mini OSM Module |
|---|
| 4 | # --------------- |
|---|
| 5 | # |
|---|
| 6 | # Provides OSM functionality from a local (normally planet.osm powered) |
|---|
| 7 | # OSM database, in Python. |
|---|
| 8 | # |
|---|
| 9 | # GPL |
|---|
| 10 | # |
|---|
| 11 | # Nick Burch |
|---|
| 12 | # v0.02 (31/07/2006) |
|---|
| 13 | |
|---|
| 14 | import os |
|---|
| 15 | import sys |
|---|
| 16 | import math |
|---|
| 17 | from geo_helper import calculate_distance_and_bearing |
|---|
| 18 | |
|---|
| 19 | class mini_osm: |
|---|
| 20 | dbh = None |
|---|
| 21 | |
|---|
| 22 | #def __init__(self, type): |
|---|
| 23 | # if type == "pgsql" or type == "postgres": |
|---|
| 24 | # self = mini_osm_pgsql() |
|---|
| 25 | # else: |
|---|
| 26 | # raise "Un-supported type '%s'" % type |
|---|
| 27 | def __init__(self): |
|---|
| 28 | raise "Must create child classes" |
|---|
| 29 | |
|---|
| 30 | def connect(self): |
|---|
| 31 | raise "Implementation must provide a connect method" |
|---|
| 32 | |
|---|
| 33 | # ###################################################################### |
|---|
| 34 | |
|---|
| 35 | def getBoundingBox(self, lat, long, distance): |
|---|
| 36 | """For a given lat, long and distance, get the bounding box""" |
|---|
| 37 | |
|---|
| 38 | lat_rad = lat / 360.0 * 2.0 * math.pi |
|---|
| 39 | long_rad = long / 360.0 * 2.0 * math.pi |
|---|
| 40 | |
|---|
| 41 | # Calculate how far the distance is in lat+long |
|---|
| 42 | # Values should be as right as possible, whilst being compatible |
|---|
| 43 | # with the answers from geo_helper.calculate_distance_and_bearing() |
|---|
| 44 | deg_lat_km = 110.574235 |
|---|
| 45 | deg_long_km = 110.572833 * math.cos(lat_rad) |
|---|
| 46 | #deg_lat_km = 111.133 - 0.559*math.cos(2.0*lat_rad) |
|---|
| 47 | delta_lat = distance / 1000.0 / deg_lat_km |
|---|
| 48 | delta_long = distance / 1000.0 / deg_long_km |
|---|
| 49 | |
|---|
| 50 | # Compute the min+max lat+long |
|---|
| 51 | min_lat = lat - delta_lat |
|---|
| 52 | min_long = long - delta_long |
|---|
| 53 | max_lat = lat + delta_lat |
|---|
| 54 | max_long = long + delta_long |
|---|
| 55 | |
|---|
| 56 | return (min_lat,min_long,max_lat,max_long) |
|---|
| 57 | |
|---|
| 58 | # ###################################################################### |
|---|
| 59 | |
|---|
| 60 | def _getNodesInAreaSQL(self): |
|---|
| 61 | """SQL to get all the nodes in an area""" |
|---|
| 62 | sql = "SELECT id, latitude, longitude, name, value \ |
|---|
| 63 | FROM nodes \ |
|---|
| 64 | LEFT OUTER JOIN node_tags \ |
|---|
| 65 | ON (id = node) \ |
|---|
| 66 | WHERE (latitude BETWEEN %s AND %s) \ |
|---|
| 67 | AND (longitude BETWEEN %s AND %s)" |
|---|
| 68 | return sql |
|---|
| 69 | |
|---|
| 70 | def _getNodesInAreaWithTagSQL(self,tag_name,tag_value): |
|---|
| 71 | """SQL to get all the nodes in an area with the given tag name, and optionally also tag value""" |
|---|
| 72 | tag_match = "name = %s" |
|---|
| 73 | if not tag_value == None: |
|---|
| 74 | tag_match += " AND value = %s" |
|---|
| 75 | |
|---|
| 76 | sql = "SELECT id, latitude, longitude, name, value \ |
|---|
| 77 | FROM nodes \ |
|---|
| 78 | INNER JOIN node_tags \ |
|---|
| 79 | ON (id = node) \ |
|---|
| 80 | WHERE id IN ( \ |
|---|
| 81 | SELECT id \ |
|---|
| 82 | FROM nodes \ |
|---|
| 83 | INNER JOIN node_tags \ |
|---|
| 84 | ON (id = node AND " + tag_match + ") \ |
|---|
| 85 | WHERE (latitude BETWEEN %s AND %s) \ |
|---|
| 86 | AND (longitude BETWEEN %s AND %s) \ |
|---|
| 87 | )" |
|---|
| 88 | return sql |
|---|
| 89 | |
|---|
| 90 | def _getNodesWithTagNameAndTypeSQL(self,name,tag_type,tag_values): |
|---|
| 91 | """SQL to get all the nodes in the DB with the given name, and one of the given other tags, eg name=London, tag_type=place, tag_values=city,town""" |
|---|
| 92 | |
|---|
| 93 | in_sql = "" |
|---|
| 94 | for val in (tag_values): |
|---|
| 95 | if len(in_sql) > 0: |
|---|
| 96 | in_sql += "," |
|---|
| 97 | in_sql += "%s" |
|---|
| 98 | |
|---|
| 99 | sql = "SELECT id, latitude, longitude, name, value \ |
|---|
| 100 | FROM nodes \ |
|---|
| 101 | INNER JOIN node_tags AS tags \ |
|---|
| 102 | ON (id = tags.node) \ |
|---|
| 103 | WHERE id IN ( \ |
|---|
| 104 | SELECT nt.node \ |
|---|
| 105 | FROM node_tags AS nt \ |
|---|
| 106 | INNER JOIN node_tags AS ot \ |
|---|
| 107 | ON (nt.name = 'name' AND nt.value = %s \ |
|---|
| 108 | AND nt.node = ot.node \ |
|---|
| 109 | AND ot.name = %s AND ot.value IN (" + in_sql + ") \ |
|---|
| 110 | ) \ |
|---|
| 111 | )" |
|---|
| 112 | return sql |
|---|
| 113 | |
|---|
| 114 | def _processNodesQuery(self, sth): |
|---|
| 115 | """Processes the given nodes query, and return a dict of the nodes""" |
|---|
| 116 | |
|---|
| 117 | nodes_db = sth.fetchall() |
|---|
| 118 | |
|---|
| 119 | nodes = {} |
|---|
| 120 | last_node = { "id":-1 } |
|---|
| 121 | for node in (nodes_db): |
|---|
| 122 | if last_node["id"] != node.id: |
|---|
| 123 | if last_node["id"] != -1: |
|---|
| 124 | nodes[last_node["id"]] = last_node |
|---|
| 125 | last_node = { "id":node.id, "lat":node.latitude, "long":node.longitude, "tags":[] } |
|---|
| 126 | if node.name != None and len(node.name): |
|---|
| 127 | last_node["tags"].append( (node.name,node.value) ) |
|---|
| 128 | if last_node["id"] != -1: |
|---|
| 129 | nodes[last_node["id"]] = last_node |
|---|
| 130 | |
|---|
| 131 | return nodes |
|---|
| 132 | |
|---|
| 133 | def getNodesInArea(self, lat, long, distance): |
|---|
| 134 | """Fetch all the nodes within a given distance of the lat+long""" |
|---|
| 135 | |
|---|
| 136 | # Calculate how far the distance is in lat+long |
|---|
| 137 | (min_lat,min_long,max_lat,max_long) = self.getBoundingBox(lat,long,distance) |
|---|
| 138 | return self.getNodesInBBox(min_lat,min_long,max_lat,max_long) |
|---|
| 139 | |
|---|
| 140 | def getNodesInBBox(self, min_lat, min_long, max_lat, max_long): |
|---|
| 141 | """Find all the nodes within the given bounding box""" |
|---|
| 142 | # Find nodes |
|---|
| 143 | sql = self._getNodesInAreaSQL() |
|---|
| 144 | sth = self.dbh.cursor() |
|---|
| 145 | sth.execute(sql, min_lat, max_lat, min_long, max_long) |
|---|
| 146 | |
|---|
| 147 | return self._processNodesQuery(sth) |
|---|
| 148 | |
|---|
| 149 | def getNodesWithTagNameAndType(self,name,tag_type,tag_values): |
|---|
| 150 | """Fetch all the nodes with the given name, and given other tag having one of the supplied values""" |
|---|
| 151 | if len(tag_values) == 0: |
|---|
| 152 | return {} |
|---|
| 153 | sql = self._getNodesWithTagNameAndTypeSQL(name,tag_type,tag_values) |
|---|
| 154 | sth = self.dbh.cursor() |
|---|
| 155 | params = [name, tag_type] |
|---|
| 156 | for val in tag_values: |
|---|
| 157 | params.append(val) |
|---|
| 158 | sth.execute(sql,params) |
|---|
| 159 | |
|---|
| 160 | return self._processNodesQuery(sth) |
|---|
| 161 | |
|---|
| 162 | def getNodesInAreaWithTag(self, lat, long, distance, tag_name, tag_value=None): |
|---|
| 163 | """Fetch all the nodes within a given distance of the lat+long, with the given tag name (and optionally tag value)""" |
|---|
| 164 | |
|---|
| 165 | # Calculate how far the distance is in lat+long |
|---|
| 166 | (min_lat,min_long,max_lat,max_long) = self.getBoundingBox(lat,long,distance) |
|---|
| 167 | return self.getNodesInBBoxWithTag(min_lat,min_long,max_lat,max_long,tag_name,tag_value) |
|---|
| 168 | |
|---|
| 169 | def getNodesInBBoxWithTag(self, min_lat, min_long, max_lat, max_long, tag_name, tag_value=None): |
|---|
| 170 | """Fetch all the nodes within the given bounding, with the given tag name (and optionally tag value)""" |
|---|
| 171 | params = [tag_name] |
|---|
| 172 | if not tag_value == None: |
|---|
| 173 | params.append(tag_value) |
|---|
| 174 | params.append(min_lat) |
|---|
| 175 | params.append(max_lat) |
|---|
| 176 | params.append(min_long) |
|---|
| 177 | params.append(max_long) |
|---|
| 178 | |
|---|
| 179 | # Find nodes |
|---|
| 180 | sql = self._getNodesInAreaWithTagSQL(tag_name,tag_value) |
|---|
| 181 | sth = self.dbh.cursor() |
|---|
| 182 | sth.execute(sql, params) |
|---|
| 183 | |
|---|
| 184 | return self._processNodesQuery(sth) |
|---|
| 185 | |
|---|
| 186 | # ###################################################################### |
|---|
| 187 | |
|---|
| 188 | def _getSegmentsForNodesSQL(self,node_ids): |
|---|
| 189 | sql = "SELECT id, node_a, node_b, name, value \ |
|---|
| 190 | FROM segments \ |
|---|
| 191 | LEFT OUTER JOIN segment_tags \ |
|---|
| 192 | ON (segment = id) \ |
|---|
| 193 | WHERE node_a IN (%s) OR node_b IN (%s)" % (node_ids,node_ids) |
|---|
| 194 | return sql |
|---|
| 195 | |
|---|
| 196 | def getSegmentsForNodes(self,nodes): |
|---|
| 197 | """Fetch all the segments for the given list of nodes""" |
|---|
| 198 | global dbh |
|---|
| 199 | |
|---|
| 200 | # Get list of ids |
|---|
| 201 | ids = "" |
|---|
| 202 | for id in (nodes.keys()): |
|---|
| 203 | if len(ids): |
|---|
| 204 | ids += "," |
|---|
| 205 | ids += str(id) |
|---|
| 206 | |
|---|
| 207 | # Find |
|---|
| 208 | sql = self._getSegmentsForNodesSQL(ids) |
|---|
| 209 | sth = self.dbh.cursor() |
|---|
| 210 | sth.execute(sql) |
|---|
| 211 | |
|---|
| 212 | segs_db = sth.fetchall() |
|---|
| 213 | |
|---|
| 214 | segs = {} |
|---|
| 215 | last_seg = { "id":-1 } |
|---|
| 216 | for seg in (segs_db): |
|---|
| 217 | if last_seg["id"] != seg.id: |
|---|
| 218 | if last_seg["id"] != -1: |
|---|
| 219 | segs[last_seg["id"]] = last_seg |
|---|
| 220 | last_seg = { "id":seg.id, "node_a":seg.node_a, "node_b":seg.node_b, "tags":[] } |
|---|
| 221 | if seg.name != None: |
|---|
| 222 | last_seg["tags"].append( (seg.name,seg.value) ) |
|---|
| 223 | if last_seg["id"] != -1: |
|---|
| 224 | segs[last_seg["id"]] = last_seg |
|---|
| 225 | |
|---|
| 226 | return segs |
|---|
| 227 | |
|---|
| 228 | # ###################################################################### |
|---|
| 229 | |
|---|
| 230 | def _getWayIdsForSegments(self,segs): |
|---|
| 231 | """Fetch the IDs of all the ways for the given list of segments""" |
|---|
| 232 | global dbh |
|---|
| 233 | |
|---|
| 234 | # Get list of ids |
|---|
| 235 | ids = "" |
|---|
| 236 | for id in (segs.keys()): |
|---|
| 237 | if len(ids): |
|---|
| 238 | ids += "," |
|---|
| 239 | ids += str(id) |
|---|
| 240 | |
|---|
| 241 | # Find |
|---|
| 242 | sql = "SELECT way FROM way_segments WHERE segment IN (%s)" % ids |
|---|
| 243 | sth = self.dbh.cursor() |
|---|
| 244 | sth.execute(sql) |
|---|
| 245 | |
|---|
| 246 | ways_db = sth.fetchall() |
|---|
| 247 | way_ids = [] |
|---|
| 248 | for way in (ways_db): |
|---|
| 249 | way_ids.append(way.way) |
|---|
| 250 | return way_ids |
|---|
| 251 | |
|---|
| 252 | def getWaysForSegments(self,segs): |
|---|
| 253 | """Fetch all the ways for the given list of segments""" |
|---|
| 254 | global dbh |
|---|
| 255 | |
|---|
| 256 | # Get list of way ids |
|---|
| 257 | way_ids = self._getWayIdsForSegments(segs) |
|---|
| 258 | if len(way_ids) == 0: |
|---|
| 259 | return [] |
|---|
| 260 | ids = "" |
|---|
| 261 | ways = {} |
|---|
| 262 | for id in (way_ids): |
|---|
| 263 | if len(ids): |
|---|
| 264 | ids += "," |
|---|
| 265 | ids += str(id) |
|---|
| 266 | ways[id] = { "id":id, "segments":[], "tags":[] } |
|---|
| 267 | |
|---|
| 268 | # Find segments |
|---|
| 269 | sql = "SELECT way, segment FROM way_segments WHERE way IN (%s)" % ids |
|---|
| 270 | sth = self.dbh.cursor() |
|---|
| 271 | sth.execute(sql) |
|---|
| 272 | |
|---|
| 273 | segs_db = sth.fetchall() |
|---|
| 274 | for seg in (segs_db): |
|---|
| 275 | ways[seg.way]["segments"].append(seg.segment) |
|---|
| 276 | |
|---|
| 277 | # Find tags |
|---|
| 278 | sql = "SELECT way, name, value FROM way_tags WHERE way IN (%s)" % ids |
|---|
| 279 | sth = self.dbh.cursor() |
|---|
| 280 | sth.execute(sql) |
|---|
| 281 | |
|---|
| 282 | tags_db = sth.fetchall() |
|---|
| 283 | for tag in (tags_db): |
|---|
| 284 | ways[tag.way]["tags"].append( (tag.name,tag.value) ) |
|---|
| 285 | |
|---|
| 286 | return ways |
|---|
| 287 | |
|---|
| 288 | # ###################################################################### |
|---|
| 289 | |
|---|
| 290 | def splatWayTagsOntoSegments(self,ways,segments): |
|---|
| 291 | """Splats the way tags out onto the segments that make up the way""" |
|---|
| 292 | |
|---|
| 293 | # Push tags from ways down onto segments |
|---|
| 294 | for way in (ways.values()): |
|---|
| 295 | for seg_id in (way["segments"]): |
|---|
| 296 | # Skip segments out of area |
|---|
| 297 | if segments.has_key(seg_id): |
|---|
| 298 | for tag in (way["tags"]): |
|---|
| 299 | segments[seg_id]["tags"].append(tag) |
|---|
| 300 | |
|---|
| 301 | def splatTagsOntoObjects(self,objects,want_tags): |
|---|
| 302 | """Splats certain tags down onto the main objects, setting them to None if not found""" |
|---|
| 303 | for obj in (objects.values()): |
|---|
| 304 | # Set to None, so always there |
|---|
| 305 | for tag in (want_tags): |
|---|
| 306 | obj[tag] = None |
|---|
| 307 | # Now search for them |
|---|
| 308 | for tag in obj["tags"]: |
|---|
| 309 | (tagname,tagvalue) = tag |
|---|
| 310 | if want_tags.__contains__(tagname): |
|---|
| 311 | obj[tagname] = tagvalue |
|---|
| 312 | |
|---|
| 313 | def calculateDistanceToSegments(self,lat,long,segments,nodes): |
|---|
| 314 | """Calculates the distances to the segments""" |
|---|
| 315 | |
|---|
| 316 | for seg in (segments): |
|---|
| 317 | node_a = nodes[seg["node_a"]] |
|---|
| 318 | node_b = nodes[seg["node_b"]] |
|---|
| 319 | seg["node_a_node"] = node_a |
|---|
| 320 | seg["node_b_node"] = node_b |
|---|
| 321 | avg_lat = 0.5 * (node_a["lat"] + node_b["lat"]) |
|---|
| 322 | avg_long = 0.5 * (node_a["long"] + node_b["long"]) |
|---|
| 323 | |
|---|
| 324 | dist_bearing = calculate_distance_and_bearing(lat,long,avg_lat,avg_long) |
|---|
| 325 | seg["distance"] = dist_bearing[0] |
|---|
| 326 | seg["bearing"] = dist_bearing[1] |
|---|
| 327 | |
|---|
| 328 | def filterSegmentsByTags(self,segments,nodes,want_tag_names,want_tag_values=None): |
|---|
| 329 | """Filters the segments, only returning ones with one of the given tags, optionally also by tag value""" |
|---|
| 330 | |
|---|
| 331 | wanted = [] |
|---|
| 332 | for seg in (segments.values()): |
|---|
| 333 | # Exclude any sections that pass out of the boundary |
|---|
| 334 | if not nodes.has_key(seg["node_a"]): |
|---|
| 335 | continue |
|---|
| 336 | if not nodes.has_key(seg["node_b"]): |
|---|
| 337 | continue |
|---|
| 338 | |
|---|
| 339 | # Filter by tags |
|---|
| 340 | for tag in seg["tags"]: |
|---|
| 341 | (tagname,tagvalue) = tag |
|---|
| 342 | if want_tag_names.__contains__(tagname): |
|---|
| 343 | # Check value, if required |
|---|
| 344 | if want_tag_values == None: |
|---|
| 345 | wanted.append(seg) |
|---|
| 346 | else: |
|---|
| 347 | if want_tag_values.__contains__(tagvalue): |
|---|
| 348 | wanted.append(seg) |
|---|
| 349 | return wanted |
|---|
| 350 | |
|---|
| 351 | # ###################################################################### |
|---|
| 352 | |
|---|
| 353 | class mini_osm_pgsql(mini_osm): |
|---|
| 354 | """PostGreSQL Specific Mini OSM Implementation""" |
|---|
| 355 | from pyPgSQL import PgSQL |
|---|
| 356 | |
|---|
| 357 | # Database settings |
|---|
| 358 | dbname = "planetosm" |
|---|
| 359 | dbhost = "localhost" |
|---|
| 360 | dbuser = "nick" |
|---|
| 361 | dbpass = "" |
|---|
| 362 | |
|---|
| 363 | dbh = None |
|---|
| 364 | |
|---|
| 365 | def __init__(self): |
|---|
| 366 | self.connect() |
|---|
| 367 | |
|---|
| 368 | def connect(self): |
|---|
| 369 | """ Connect to the database""" |
|---|
| 370 | if len(self.dbhost): |
|---|
| 371 | self.dbh = self.PgSQL.connect(database=self.dbname, host=self.dbhost, user=self.dbuser, password=self.dbpass) |
|---|
| 372 | else: |
|---|
| 373 | self.dbh = self.PgSQL.connect(database=self.dbname, user=self.dbuser, password=self.dbpass) |
|---|
