Exemplo n.º 1
0
    def test_simple(self):
        self.assertEqual(pghstore.loads('"key" => "value"'), {"key": "value"})

        self.assertEqual(
            pghstore.loads('"key" => "value", "key2" => "value2"'), {
                "key": "value",
                "key2": "value2"
            })
Exemplo n.º 2
0
 def test_null(self):
     self.assertEqual(pghstore.loads('"key" => null'), {"key": None})
     self.assertEqual(pghstore.loads('"key" => NULL'), {"key": None})    
     self.assertEqual(pghstore.loads(
             '"key" => NULL, "key2": "value2"'), 
                      {"key": None, 
                       "key2": "value2"});
     self.assertEqual(pghstore.loads(
             '"key0" => "value0", "key" => NULL, "key2": "value2"'), 
                      {"key0": "value0", 
                       "key": None, 
                       "key2": "value2"});
Exemplo n.º 3
0
def objects_member_of(objects, other_selects, self_selects, options):
    if "relation" in other_selects:
        plan = plpy.prepare(
            "select *, (select name from users where id=user_id) as user from relation_members join relations on relation_members.relation_id=relations.id where member_id=$1 and member_type=$2",
            ["bigint", "text"],
        )
        for ob in objects:
            member_id = ob["id"]

            res = plpy.cursor(plan, [int(member_id[1:]), member_id[0:1].upper()])
            for r in res:
                t = {"id": "r" + str(r["id"]), "tags": pghstore.loads(r["tags"]), "types": ["relation"], "geo": None}
                link_tags = {
                    "sequence_id": str(r["sequence_id"]),
                    "role": str(r["member_role"]),
                    "member_id": r["member_type"].lower() + str(r["member_id"]),
                }
                t["tags"]["osm:id"] = str(t["id"])
                t["tags"]["osm:version"] = str(r["version"])
                t["tags"]["osm:user_id"] = str(r["user_id"])
                t["tags"]["osm:user"] = r["user"]
                t["tags"]["osm:timestamp"] = str(r["tstamp"])
                t["tags"]["osm:changeset"] = str(r["changeset_id"])
                yield ((ob, t, link_tags))

    if "way" in other_selects:
        plan = plpy.prepare(
            "select *, (select name from users where id=user_id) as user from way_nodes join ways on way_nodes.way_id=ways.id where node_id=$1",
            ["bigint"],
        )
        for o in objects:
            member_id = o["id"]

            if member_id[0] != "n":
                continue

            num_id = int(member_id[1:])
            res = plpy.cursor(plan, [num_id])
            for r in res:
                t = {
                    "id": "w" + str(r["id"]),
                    "tags": pghstore.loads(r["tags"]),
                    "types": ["way"],
                    "geo": r["linestring"],
                }
                link_tags = {"member_id": member_id, "sequence_id": str(r["sequence_id"])}
                t["tags"]["osm:id"] = str(t["id"])
                t["tags"]["osm:version"] = str(r["version"])
                t["tags"]["osm:user_id"] = str(r["user_id"])
                t["tags"]["osm:user"] = r["user"]
                t["tags"]["osm:timestamp"] = str(r["tstamp"])
                t["tags"]["osm:changeset"] = str(r["changeset_id"])
                yield ((ob, t, link_tags))
Exemplo n.º 4
0
 def test_utf8(self):
     self.maxDiff = None
     #self.assertEqual(pghstore.loads('"åäö" => "åäö"'), {"åäö": "åäö"})
     s = '"name"=>"Noorwe\xc3\xab", "name2"=>"öäå"'
     self.assertEqual(pghstore.loads(s), 
                      {"name": "Noorwe\xc3\xab",
                       "name2": "öäå"})
     names = '"name"=>"Norge/Noreg", "name:af"=>"Noorwe\xc3\xab", "name:ar"=>"\xd8\xa7\xd9\x84\xd9\x86\xd8\xb1\xd9\x88\xd9\x8a\xd8\xac", "name:be"=>"\xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:br"=>"Norvegia", "name:ca"=>"Noruega", "name:cs"=>"Norsko", "name:cy"=>"Norwy", "name:da"=>"Norge", "name:de"=>"Norwegen", "name:el"=>"\xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1", "name:en"=>"Norway", "name:eo"=>"Norvegio", "name:es"=>"Noruega", "name:et"=>"Norra", "name:fa"=>"\xd9\x86\xd8\xb1\xd9\x88\xda\x98", "name:fi"=>"Norja", "name:fo"=>"Noregur", "name:fr"=>"Norv\xc3\xa8ge", "name:fy"=>"Noarwegen", "name:ga"=>"An Iorua", "name:gd"=>"Nirribhidh", "name:he"=>"\xd7\xa0\xd7\x95\xd7\xa8\xd7\x95\xd7\x95\xd7\x92\xd7\x99\xd7\x94", "name:hr"=>"Norve\xc5\xa1ka", "name:hu"=>"Norv\xc3\xa9gia", "name:hy"=>"\xd5\x86\xd5\xb8\xd6\x80\xd5\xbe\xd5\xa5\xd5\xa3\xd5\xab\xd5\xa1", "name:id"=>"Norwegia", "name:is"=>"Noregur", "name:it"=>"Norvegia", "name:ja"=>"\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc", "name:la"=>"Norvegia", "name:lb"=>"Norwegen", "name:li"=>"Noorwege", "name:lt"=>"Norvegija", "name:lv"=>"Norv\xc4\x93\xc4\xa3ija", "name:mn"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8", "name:nb"=>"Norge", "name:nl"=>"Noorwegen", "name:nn"=>"Noreg", "name:no"=>"Norge", "name:pl"=>"Norwegia", "name:ru"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8\xd1\x8f", "name:sk"=>"N\xc3\xb3rsko", "name:sl"=>"Norve\xc5\xa1ka", "name:sv"=>"Norge", "name:th"=>"\xe0\xb8\x9b\xe0\xb8\xa3\xe0\xb8\xb0\xe0\xb9\x80\xe0\xb8\x97\xe0\xb8\xa8\xe0\xb8\x99\xe0\xb8\xad\xe0\xb8\xa3\xe0\xb9\x8c\xe0\xb9\x80\xe0\xb8\xa7\xe0\xb8\xa2\xe0\xb9\x8c", "name:tr"=>"Norve\xc3\xa7", "name:uk"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:vi"=>"Na Uy", "name:zh"=>"\xe6\x8c\xaa\xe5\xa8\x81", "name:haw"=>"Nolewai", "name:zh_py"=>"Nuowei", "name:zh_pyt"=>"Nu\xc3\xb3w\xc4\x93i", "official_name"=>"Kongeriket Norge", "official_name:be"=>"\xd0\x9a\xd0\xb0\xd1\x80\xd0\xb0\xd0\xbb\xd0\xb5\xd1\x9e\xd1\x81\xd1\x82\xd0\xb2\xd0\xb0 \xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "official_name:el"=>"\xce\x92\xce\xb1\xcf\x83\xce\xaf\xce\xbb\xce\xb5\xce\xb9\xce\xbf \xcf\x84\xce\xb7\xcf\x82 \xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1\xcf\x82", "official_name:en"=>"Kingdom of Norway", "official_name:id"=>"Kerajaan Norwegia", "official_name:it"=>"Regno di Norvegia", "official_name:ja"=>"\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc\xe7\x8e\x8b\xe5\x9b\xbd", "official_name:lb"=>"Kinneksr\xc3\xa4ich Norwegen", "official_name:lt"=>"Norvegijos Karalyst\xc4\x97", "official_name:sk"=>"N\xc3\xb3rske kr\xc3\xa1\xc4\xbeovstvo", "official_name:sv"=>"Konungariket Norge", "official_name:vi"=>"V\xc6\xb0\xc6\xa1ng qu\xe1\xbb\x91c Na Uy"'
     r = pghstore.loads(names)
     self.assertEqual(
         pghstore.loads(names),
         {"name": "Norge/Noreg", "name:af": "Noorwe\xc3\xab", "name:ar": "\xd8\xa7\xd9\x84\xd9\x86\xd8\xb1\xd9\x88\xd9\x8a\xd8\xac", "name:be": "\xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:br": "Norvegia", "name:ca": "Noruega", "name:cs": "Norsko", "name:cy": "Norwy", "name:da": "Norge", "name:de": "Norwegen", "name:el": "\xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1", "name:en": "Norway", "name:eo": "Norvegio", "name:es": "Noruega", "name:et": "Norra", "name:fa": "\xd9\x86\xd8\xb1\xd9\x88\xda\x98", "name:fi": "Norja", "name:fo": "Noregur", "name:fr": "Norv\xc3\xa8ge", "name:fy": "Noarwegen", "name:ga": "An Iorua", "name:gd": "Nirribhidh", "name:he": "\xd7\xa0\xd7\x95\xd7\xa8\xd7\x95\xd7\x95\xd7\x92\xd7\x99\xd7\x94", "name:hr": "Norve\xc5\xa1ka", "name:hu": "Norv\xc3\xa9gia", "name:hy": "\xd5\x86\xd5\xb8\xd6\x80\xd5\xbe\xd5\xa5\xd5\xa3\xd5\xab\xd5\xa1", "name:id": "Norwegia", "name:is": "Noregur", "name:it": "Norvegia", "name:ja": "\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc", "name:la": "Norvegia", "name:lb": "Norwegen", "name:li": "Noorwege", "name:lt": "Norvegija", "name:lv": "Norv\xc4\x93\xc4\xa3ija", "name:mn": "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8", "name:nb": "Norge", "name:nl": "Noorwegen", "name:nn": "Noreg", "name:no": "Norge", "name:pl": "Norwegia", "name:ru": "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8\xd1\x8f", "name:sk": "N\xc3\xb3rsko", "name:sl": "Norve\xc5\xa1ka", "name:sv": "Norge", "name:th": "\xe0\xb8\x9b\xe0\xb8\xa3\xe0\xb8\xb0\xe0\xb9\x80\xe0\xb8\x97\xe0\xb8\xa8\xe0\xb8\x99\xe0\xb8\xad\xe0\xb8\xa3\xe0\xb9\x8c\xe0\xb9\x80\xe0\xb8\xa7\xe0\xb8\xa2\xe0\xb9\x8c", "name:tr": "Norve\xc3\xa7", "name:uk": "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:vi": "Na Uy", "name:zh": "\xe6\x8c\xaa\xe5\xa8\x81", "name:haw": "Nolewai", "name:zh_py": "Nuowei", "name:zh_pyt": "Nu\xc3\xb3w\xc4\x93i", "official_name": "Kongeriket Norge", "official_name:be": "\xd0\x9a\xd0\xb0\xd1\x80\xd0\xb0\xd0\xbb\xd0\xb5\xd1\x9e\xd1\x81\xd1\x82\xd0\xb2\xd0\xb0 \xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "official_name:el": "\xce\x92\xce\xb1\xcf\x83\xce\xaf\xce\xbb\xce\xb5\xce\xb9\xce\xbf \xcf\x84\xce\xb7\xcf\x82 \xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1\xcf\x82", "official_name:en": "Kingdom of Norway", "official_name:id": "Kerajaan Norwegia", "official_name:it": "Regno di Norvegia", "official_name:ja": "\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc\xe7\x8e\x8b\xe5\x9b\xbd", "official_name:lb": "Kinneksr\xc3\xa4ich Norwegen", "official_name:lt": "Norvegijos Karalyst\xc4\x97", "official_name:sk": "N\xc3\xb3rske kr\xc3\xa1\xc4\xbeovstvo", "official_name:sv": "Konungariket Norge", "official_name:vi": "V\xc6\xb0\xc6\xa1ng qu\xe1\xbb\x91c Na Uy"})
Exemplo n.º 5
0
 def test_null(self):
     self.assertEqual(pghstore.loads('"key" => null'), {"key": None})
     self.assertEqual(pghstore.loads('"key" => NULL'), {"key": None})
     self.assertEqual(pghstore.loads('"key" => NULL, "key2": "value2"'), {
         "key": None,
         "key2": "value2"
     })
     self.assertEqual(
         pghstore.loads(
             '"key0" => "value0", "key" => NULL, "key2": "value2"'), {
                 "key0": "value0",
                 "key": None,
                 "key2": "value2"
             })
Exemplo n.º 6
0
def objects_member_of(member_id, parent_type, parent_conditions):
    if parent_type == 'relation':
        plan = plpy.prepare('select * from planet_osm_rels where members @> Array[$1]', ['text']);
        res = plpy.execute(plan, [member_id])
        for r in res:
            for member in flatarray_to_members(r['members']):
                if member['member_id'] == member_id:
                    t = {
                        'id': 'r' + str(r['id']),
                        'tags': flatarray_to_tags(r['tags']) if r['tags'] else {},
                        'type': ['relation'],
                        'geo': None,
                        'link_tags': member
                    }
                    yield(t)

    if parent_type == 'way':
        num_id = int(member_id[1:])
        plan = plpy.prepare('select id, nodes, planet_osm_line.tags, way as geo from planet_osm_ways left join planet_osm_line on planet_osm_ways.id=planet_osm_line.osm_id where nodes::bigint[] @> Array[$1]', ['bigint']);
        res = plpy.execute(plan, [num_id])
        for r in res:
            for i, member in enumerate(r['nodes']):
                if member == num_id:
                    t = {
                        'id': 'w' + str(r['id']),
                        'tags': pghstore.loads(r['tags']) if r['tags'] else {},
                        'type': ['way'],
                        'geo': r['geo'],
                        'link_tags': {
                            'member_id': member_id,
                            'sequence_id': str(i)
                        }
                    }
                    yield(t)
Exemplo n.º 7
0
def objects_by_id(id_list):
    _id_list = [ int(i[1:]) for i in id_list if i[0] == 'n' ]
    plan = plpy.prepare('select * from planet_osm_point where osm_id=any($1)', ['bigint[]']);
    res = plpy.execute(plan, [_id_list])
    for r in res:
        yield {
            'id': 'n' + str(r['osm_id']),
            'members': [],
            'tags': pghstore.loads(r['tags']),
            'geo': r['way'],
            'types': ['node', 'point']
        }

    _id_list = [ int(i[1:]) for i in id_list if i[0] == 'w' ]
    plan = plpy.prepare("select t.*, planet_osm_ways.nodes from (select osm_id, tags, way, 'line' as _type from planet_osm_line where osm_id=any($1) union select osm_id, tags, way, 'way' as _type from planet_osm_polygon where osm_id=any($1)) t left join planet_osm_ways on t.osm_id=planet_osm_ways.id", ['bigint[]']);
    res = plpy.execute(plan, [_id_list])
    for r in res:
        yield {
            'id': 'w' + str(r['osm_id']),
            'members': [ {
                    'member_id': 'n' + str(m),
                    'sequence_id': str(i)
                }
                for i, m in enumerate(r['nodes'])
            ],
            'tags': pghstore.loads(r['tags']),
            'geo': r['way'],
            'types': ['way', r['_type']]
        }

    _id_list = [ int(i[1:]) for i in id_list if i[0] == 'r' ]
    plan = plpy.prepare("select id, planet_osm_rels.tags, members, planet_osm_polygon.way from planet_osm_rels left join planet_osm_polygon on -planet_osm_rels.id=planet_osm_polygon.osm_id where id=any($1)", ['bigint[]'])
    res = plpy.execute(plan, [_id_list])
    for r in res:
        yield {
            'id': 'r' + str(r['id']),
            'tags': flatarray_to_tags(r['tags']),
            'members': flatarray_to_members(r['members']),
            'geo': r['way'],
            'types': ['relation'] if r['way'] is None else ['relation', 'area']
        }
Exemplo n.º 8
0
def process_mapnik(style_id, args, stat, conn):
    f1 = resource_stream(__name__, args.base_style + '.mapnik')
    f2 = open(style_id + '.mapnik', 'w')

    replacement = {
        'style_id': style_id,
        'host': args.host,
        'password': args.password,
        'database': args.database,
        'user': args.user,
        'columns': '{columns}'
    }

    stat['mapnik_columns'] = set()

    # dirty hack - when render_context.bbox is null, pass type 'canvas' instead of style-element
    res = db.prepare("select * from pgmapcss_{style_id}(null, 0, Array['canvas']) where pseudo_element='default'".format(**replacement))
    result = res()
    if len(result) > 0:
        canvas_properties = result[0][res.column_names.index('properties')]
        canvas_properties = pghstore.loads(canvas_properties)

        for (k, v) in canvas_properties.items():
            replacement['canvas|' + k] = v or ''

    text = process(f1, replacement, stat)

    # style-element is an extra column in result set
    stat['mapnik_columns'].remove('style-element')

    # finally replace 'columns'
    replacement = {}
    replacement['columns'] = ',\n  '.join([
        ' || \' \' || '.join([
            '(properties->' + db.format(p) + ')'
            for p in props.split(' ')
        ]) + ' as "' + shorten_column(props) + '"'
        for props in stat['mapnik_columns']
    ])

    f2.write(text.format(**replacement))

    f1.close()
    f2.close()

    print('File ' + style_id + '.mapnik successfully written.')
Exemplo n.º 9
0
def process_mapnik(style_id, args, stat, conn):
    f1 = resource_stream(__name__, args.base_style + '.mapnik')
    f2 = open(style_id + '.mapnik', 'w')

    replacement = {
        'style_id': style_id,
        'host': args.host,
        'password': args.password,
        'database': args.database,
        'user': args.user
    }

    replacement['columns'] = ', '.join([
        'properties->' + db.format(prop) + ' as ' + db.ident(prop)
        for prop in stat_properties(stat)
    ])

    # dirty hack - when render_context.bbox is null, pass type 'canvas' instead of style-element
    res = db.prepare("select * from {style_id}_match(null, 0, Array['canvas'])".format(**replacement))
    result = res()
    if len(result) > 0:
        canvas_properties = result[0][res.column_names.index('properties')]
        canvas_properties = pghstore.loads(canvas_properties)

        for (k, v) in canvas_properties.items():
            replacement['canvas|' + k] = v or ''

    f2.write(process(f1, replacement, stat))

    f1.close()
    f2.close()

    print('File ' + style_id + '.mapnik successfully written.')

    if len(unresolvable_properties):
        print('WARNING: Not all values for the following properties could be guessed (e.g. as they are the result of an eval-expression, and therefore some features in the resulting image(s) may be missing: ' + ', '.join(unresolvable_properties))
Exemplo n.º 10
0
def objects_by_id(id_list, options):
    _id_list = [int(i[1:]) for i in id_list if i[0] == "n"]
    plan = plpy.prepare(
        "select *, (select name from users where id=user_id) as user from nodes where id=any($1)", ["bigint[]"]
    )
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            "id": "n" + str(r["id"]),
            "members": [],
            "tags": pghstore.loads(r["tags"]),
            "geo": r["geom"],
            "types": ["node", "point"],
        }
        t["tags"]["osm:id"] = str(t["id"])
        t["tags"]["osm:version"] = str(r["version"])
        t["tags"]["osm:user_id"] = str(r["user_id"])
        t["tags"]["osm:user"] = r["user"]
        t["tags"]["osm:timestamp"] = str(r["tstamp"])
        t["tags"]["osm:changeset"] = str(r["changeset_id"])
        yield (t)

    _id_list = [int(i[1:]) for i in id_list if i[0] == "w"]
    plan = plpy.prepare(
        "select id, tags, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, linestring as linestring, array_agg(node_id) as member_ids from (select ways.*, node_id from ways left join way_nodes on ways.id=way_nodes.way_id where ways.id=any($1) order by way_nodes.sequence_id) t group by id, tags, version, user_id, tstamp, changeset_id, linestring",
        ["bigint[]"],
    )
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            "id": "w" + str(r["id"]),
            "members": [{"member_id": "n" + str(m), "sequence_id": str(i)} for i, m in enumerate(r["member_ids"])],
            "tags": pghstore.loads(r["tags"]),
            "geo": r["linestring"],
            "types": ["way", "line", "area"],
        }
        t["tags"]["osm:id"] = str(t["id"])
        t["tags"]["osm:version"] = str(r["version"])
        t["tags"]["osm:user_id"] = str(r["user_id"])
        t["tags"]["osm:user"] = r["user"]
        t["tags"]["osm:timestamp"] = str(r["tstamp"])
        t["tags"]["osm:changeset"] = str(r["changeset_id"])
        yield (t)

    _id_list = [int(i[1:]) for i in id_list if i[0] == "r"]
    plan = plpy.prepare(
        "select id, tags, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, array_agg(lower(member_type) || member_id) as member_ids, array_agg(member_role) as member_roles from (select relations.*, member_type, member_id, member_role from relations left join relation_members on relations.id=relation_members.relation_id where relations.id=any($1) order by relation_members.sequence_id) t group by id, tags, version, user_id, tstamp, changeset_id",
        ["bigint[]"],
    )
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            "id": "r" + str(r["id"]),
            "tags": pghstore.loads(r["tags"]),
            "members": [
                {"member_id": m[0], "role": m[1], "sequence_id": i}
                for i, m in enumerate(zip(r["member_ids"], r["member_roles"]))
            ],
            "geo": None,
            "types": ["relation"],
        }
        t["tags"]["osm:id"] = str(t["id"])
        t["tags"]["osm:version"] = str(r["version"])
        t["tags"]["osm:user_id"] = str(r["user_id"])
        t["tags"]["osm:user"] = r["user"]
        t["tags"]["osm:timestamp"] = str(r["tstamp"])
        t["tags"]["osm:changeset"] = str(r["changeset_id"])
        yield (t)
Exemplo n.º 11
0
def objects_bbox(_bbox, db_selects, options, add_columns={}, add_param_type=[], add_param_value=[]):
    import pghstore

    qry = ""

    if len(add_columns):
        add_columns_qry = ", " + ", ".join([q + ' as "' + k + '"' for k, q in add_columns.items()])
    else:
        add_columns_qry = ""

    if _bbox:
        param_type = ["geometry"] + add_param_type
        param_value = [_bbox] + add_param_value
    else:
        param_type = add_param_type
        param_value = add_param_value

    # nodes
    w = []
    for t in ("*", "node", "point"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ""
        if _bbox is not None:
            bbox = "geom && $1 and ST_Intersects(geom, $1) and"

        qry = """
select 'n' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, geom as geo, Array['point', 'node'] as types
       {add_columns}
from nodes
where {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry.replace("__geo__", "geom")
        )

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r["types"] = list(r["types"])
            r["tags"] = pghstore.loads(r["tags"])
            r["tags"]["osm:id"] = str(r["id"])
            r["tags"]["osm:version"] = str(r["version"])
            r["tags"]["osm:user_id"] = str(r["user_id"])
            r["tags"]["osm:user"] = r["user"]
            r["tags"]["osm:timestamp"] = str(r["tstamp"])
            r["tags"]["osm:changeset"] = str(r["changeset_id"])
            yield (r)

    # ways
    w = []
    for t in ("*", "line", "area", "way"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ""
        if _bbox is not None:
            bbox = "linestring && $1 and (ST_NPoints(linestring) = 1 or ST_Intersects(linestring, $1)) and"

        qry = """
select * {add_columns} from (
select 'w' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, (CASE WHEN ST_NPoints(linestring) >= 4 and ST_IsClosed(linestring) THEN ST_MakePolygon(linestring) ELSE linestring END) as geo, (ST_NPoints(linestring) >= 4) and ST_IsClosed(linestring) as is_closed, Array['line', 'way'] as types
       """
        # START db.multipolygons
        # START db.multipolygons-v0.2
        # deprecated by osmosis-multipolygon v0.3
        qry += """
, (select array_agg(has_outer_tags) from relation_members join multipolygons on relation_members.relation_id=multipolygons.id where relation_members.member_id=ways.id and relation_members.member_type='W' and relation_members.member_role in ('outer', 'exclave')) part_of_mp_outer
        """
        # ELSE db.multipolygons-v0.2
        qry += """
, (select array_agg(true) from multipolygons where hide_outer_ways @> Array[ways.id]) part_of_mp_outer
        """
        # END db.multipolygons-v0.2
        # END db.multipolygons
        qry += """
from ways
where {bbox} ( {w} ) offset 0) t
"""

        qry = qry.format(bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry.replace("__geo__", "geo"))

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r["types"] = list(r["types"])
            r["tags"] = pghstore.loads(r["tags"])
            if r["is_closed"]:
                # START db.multipolygons
                if not r["part_of_mp_outer"] or True not in r["part_of_mp_outer"]:
                    # END db.multipolygons
                    r["types"].append("area")
            r["tags"]["osm:id"] = str(r["id"])
            r["tags"]["osm:version"] = str(r["version"])
            r["tags"]["osm:user_id"] = str(r["user_id"])
            r["tags"]["osm:user"] = r["user"]
            r["tags"]["osm:timestamp"] = str(r["tstamp"])
            r["tags"]["osm:changeset"] = str(r["changeset_id"])
            yield (r)

    done_multipolygons = set()
    # START db.multipolygons
    # multipolygons
    w = []
    for t in ("*", "relation", "area"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ""
        if _bbox is not None:
            bbox = "geom && $1 and ST_Intersects(geom, $1) and"

        qry = """
select * {add_columns} from (
select (CASE WHEN has_outer_tags THEN 'm' ELSE 'r' END) || cast(id as text) as id, id as rid, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, has_outer_tags,
       tags, geom as geo, Array['area'] as types
from (select multipolygons.*, relations.version, relations.user_id, relations.tstamp, relations.changeset_id from multipolygons left join relations on multipolygons.id = relations.id) t
where {bbox} ( {w} ) offset 0) t
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry.replace("__geo__", "geo")
        )

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r["types"] = list(r["types"])
            r["tags"] = pghstore.loads(r["tags"])
            r["tags"]["osm:id"] = str(r["id"])
            r["tags"]["osm:version"] = str(r["version"])
            r["tags"]["osm:user_id"] = str(r["user_id"])
            r["tags"]["osm:user"] = r["user"]
            r["tags"]["osm:timestamp"] = str(r["tstamp"])
            r["tags"]["osm:changeset"] = str(r["changeset_id"])
            if r["has_outer_tags"]:
                r["tags"]["osm:has_outer_tags"] = "yes"
            else:
                done_multipolygons.add(r["rid"])
                r["types"].append("relation")
            yield (r)
    # END db.multipolygons

    # relations - (no bbox match!)
    w = []
    for t in ("*", "relation"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select * {add_columns} from (
select 'r' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, null as geo, Array['relation'] as types
from relations
where ({w}) and not id = ANY(Array[{done}]::bigint[])) t
""".format(
            w=" or ".join(w), add_columns=add_columns_qry, done=",".join({str(d) for d in done_multipolygons})
        )

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r["types"] = list(r["types"])
            r["tags"] = pghstore.loads(r["tags"])
            r["tags"]["osm:id"] = str(r["id"])
            r["tags"]["osm:version"] = str(r["version"])
            r["tags"]["osm:user_id"] = str(r["user_id"])
            r["tags"]["osm:user"] = r["user"]
            r["tags"]["osm:timestamp"] = str(r["tstamp"])
            r["tags"]["osm:changeset"] = str(r["changeset_id"])
            yield (r)
Exemplo n.º 12
0
def objects_by_id(id_list, options):
    _id_list = [int(i[1:]) for i in id_list if i[0] == "n"]
    plan = plpy.prepare("select * from planet_osm_point where osm_id=any($1)", ["bigint[]"])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {"id": "n" + str(r["osm_id"]), "members": [], "geo": r["way"], "types": ["node", "point"]}
        # START db.columns.node
        t["tags"] = {k: r[k] for k in r if k not in ["id", "geo", "types", "tags", "way", "osm_id"] if r[k] is not None}
        # START db.has-hstore
        t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
        # END db.has-hstore
        # END db.columns.node
        # START db.hstore-only
        t["tags"] = pghstore.loads(r["tags"])
        # END db.hstore-only
        t["tags"]["osm:id"] = t["id"]
        yield t

    _id_list = [int(i[1:]) for i in id_list if i[0] == "w"]
    plan = plpy.prepare(
        "select t.*, planet_osm_ways.nodes from (select osm_id, tags, way, 'line' as _type from planet_osm_line where osm_id=any($1) union select osm_id, tags, way, 'way' as _type from planet_osm_polygon where osm_id=any($1)) t left join planet_osm_ways on t.osm_id=planet_osm_ways.id",
        ["bigint[]"],
    )
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            "id": "w" + str(r["osm_id"]),
            "members": [{"member_id": "n" + str(m), "sequence_id": str(i)} for i, m in enumerate(r["nodes"])],
            "geo": r["way"],
            "types": ["way", r["_type"]],
        }
        # START db.columns.way
        t["tags"] = {
            k: r[k]
            for k in r
            if k not in ["osm_id", "geo", "types", "tags", "nodes", "_type", "way"]
            if r[k] is not None
        }
        # START db.has-hstore
        t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
        # END db.has-hstore
        # END db.columns.way
        # START db.hstore-only
        t["tags"] = pghstore.loads(r["tags"])
        # END db.hstore-only
        t["tags"]["osm:id"] = t["id"]
        yield t

    _id_list = [int(i[1:]) for i in id_list if i[0] == "r"]
    plan = plpy.prepare(
        "select id, planet_osm_rels.tags, members, planet_osm_polygon.way from planet_osm_rels left join planet_osm_polygon on -planet_osm_rels.id=planet_osm_polygon.osm_id where id=any($1)",
        ["bigint[]"],
    )
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            "id": "r" + str(r["id"]),
            "tags": flatarray_to_tags(r["tags"]) if r["tags"] else {},
            "members": flatarray_to_members(r["members"]),
            "geo": r["way"],
            "types": ["relation"] if r["way"] is None else ["relation", "area"],
        }
        t["tags"]["osm:id"] = t["id"]
        yield t
Exemplo n.º 13
0
def objects_bbox(_bbox, db_selects, options, add_columns={}, add_param_type=[], add_param_value=[]):
    import pghstore

    qry = ""

    bbox = ""
    replacements = {"parent_bbox": ""}

    if _bbox is not None:
        bbox = "way && $1 and ST_Intersects(way, $1) and"
        replacements["parent_bbox"] = "way && $1 and ST_Intersects(way, $1) and"

    if len(add_columns):
        add_columns_qry = ", " + ", ".join([q + ' as "' + k + '"' for k, q in add_columns.items()])
    else:
        add_columns_qry = ""

    if _bbox:
        param_type = ["geometry"] + add_param_type
        param_value = [_bbox] + add_param_value
    else:
        param_type = add_param_type
        param_value = add_param_value

    # planet_osm_point
    w = []
    for t in ("*", "node", "point"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select 'n' || cast(osm_id as text) as id,
       way as geo, Array['point', 'node'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.node}
       {add_columns}
from planet_osm_point
where {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry
        )

        qry = qry.replace("__PARENT_BBOX__", replacements["parent_bbox"])
        qry = qry.replace("__TYPE_SHORT__", "n")
        qry = qry.replace("__TYPE_MODIFY__", "")

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {"id": r["id"], "geo": r["geo"], "types": r["types"]}

            # START db.columns.node
            t["tags"] = {
                k: r[k]
                for k in r
                if k not in ["id", "geo", "types", "tags"] and k not in add_columns
                if r[k] is not None
            }
            # START db.has-hstore
            t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
            # END db.has-hstore
            # END db.columns.node
            # START db.hstore-only
            t["tags"] = pghstore.loads(r["tags"])
            # END db.hstore-only
            t["tags"]["osm:id"] = str(r["id"])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_line - ways
    w = []
    for t in ("*", "line", "way"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select 'w' || cast(osm_id as text) as id,
       way as geo, Array['line', 'way'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_line
where osm_id>0 and {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry
        )
        qry = qry.replace("__PARENT_BBOX__", replacements["parent_bbox"])
        qry = qry.replace("__TYPE_SHORT__", "w")
        qry = qry.replace("__TYPE_MODIFY__", "")

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {"id": r["id"], "geo": r["geo"], "types": r["types"]}

            # START db.columns.way
            t["tags"] = {
                k: r[k]
                for k in r
                if k not in ["id", "geo", "types", "tags"] and k not in add_columns
                if r[k] is not None
            }
            # START db.has-hstore
            t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t["tags"] = pghstore.loads(r["tags"])
            # END db.hstore-only
            t["tags"]["osm:id"] = str(r["id"])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_line - relations
    w = []
    for t in ("*", "line", "relation"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select 'r' || cast(-osm_id as text) as id,
       way as geo, Array['line', 'relation'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_line
where osm_id<0 and {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry
        )
        qry = qry.replace("__PARENT_BBOX__", replacements["parent_bbox"])
        qry = qry.replace("__TYPE_SHORT__", "w")
        qry = qry.replace("__TYPE_MODIFY__", "")

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {"id": r["id"], "geo": r["geo"], "types": r["types"]}

            # START db.columns.way
            t["tags"] = {
                k: r[k]
                for k in r
                if k not in ["id", "geo", "types", "tags"] and k not in add_columns
                if r[k] is not None
            }
            # START db.has-hstore
            t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t["tags"] = pghstore.loads(r["tags"])
            # END db.hstore-only
            t["tags"]["osm:id"] = str(r["id"])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_polygon - ways
    w = []
    for t in ("*", "area", "way"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select 'w' || cast(osm_id as text) as id,
       way as geo, Array['area', 'way'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_polygon
where osm_id>0 and {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry
        )

        qry = qry.replace("__PARENT_BBOX__", replacements["parent_bbox"])
        qry = qry.replace("__TYPE_SHORT__", "r")
        qry = qry.replace("__TYPE_MODIFY__", "-")

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {"id": r["id"], "geo": r["geo"], "types": r["types"]}

            # START db.columns.way
            t["tags"] = {
                k: r[k]
                for k in r
                if k not in ["id", "geo", "types", "tags"] and k not in add_columns
                if r[k] is not None
            }
            # START db.has-hstore
            t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t["tags"] = pghstore.loads(r["tags"])
            # END db.hstore-only
            t["tags"]["osm:id"] = str(r["id"])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_polygon - relations
    w = []
    for t in ("*", "area", "relation"):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = """
select 'r' || cast(-osm_id as text) as id,
       way as geo, Array['area', 'relation'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_polygon
where osm_id<0 and {bbox} ( {w} )
""".format(
            bbox=bbox, w=" or ".join(w), add_columns=add_columns_qry
        )

        qry = qry.replace("__PARENT_BBOX__", replacements["parent_bbox"])
        qry = qry.replace("__TYPE_SHORT__", "r")
        qry = qry.replace("__TYPE_MODIFY__", "-")

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {"id": r["id"], "geo": r["geo"], "types": r["types"]}

            # START db.columns.way
            t["tags"] = {
                k: r[k]
                for k in r
                if k not in ["id", "geo", "types", "tags"] and k not in add_columns
                if r[k] is not None
            }
            # START db.has-hstore
            t["tags"] = dict(pghstore.loads(r["tags"]).items() | t["tags"].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t["tags"] = pghstore.loads(r["tags"])
            # END db.hstore-only
            t["tags"]["osm:id"] = str(r["id"])

            for k in add_columns:
                t[k] = r[k]

            yield (t)
Exemplo n.º 14
0
    def costAlgo(r):
        parsedJson = json.loads(r[1])
        # This needs to change depending on the stream information. Then we handle that and calculate cost.
        fromHash = str(parsedJson["from"])
        geohash = Geohash.encode(fromHash.get("lat"), fromHash.get("lon"), 9)
        dest = str(parsedJson["to"])
        toHash = Geohash.encode(dest.get("lat"), dest.get("lon"), 8)

        keyHash = str(parsedJson["key"])
        tStamp = str(parsedJson["timestamp"])
        value = str(parsedJson["value"])

        # Try to connect and insert into our database
        try:
            conn = psycopg2.connect(
                "dbname='DRP' user='******' password='******'")
        except:
            print "I am unable to connect to the database."

        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        try:
            result = None

            while result == None:
                cur.execute(
                    """SELECT * FROM geohashed_ways WHERE geohash LIKE  """ +
                    "'" + geohash + "'%;")
                result = cur.fetchone()
                geohash = geohash[:-1]

        except:
            print "I can't SELECT."

        # Compute the cost using the updated data that was streamed and data already existing in our database.
        tagDict = pghstore.loads(result['tags'])
        isAccident = False
        if keyHash == 'TRAFFIC_INCIDENT':
            cost = value * 10
            isAccident = True
        else:
            cost = value

        for k, v in tagDict:
            if k == 'lanes':

                if tagDict[k] == 2 and result['oneway'] == 'yes':
                    if isAccident:
                        cost = cost + (value * 10)
                    else:
                        cost = cost + 20
                elif tagDict[k] == 2 and result['oneway'] == 'no':
                    if isAccident:
                        cost = cost + (value * 5)
                    else:
                        cost = cost + 15
                elif tagDict[k] > 2 and result('oneway') == 'yes':
                    if isAccident:
                        cost = cost + (value * 5)
                    else:
                        cost = cost + 10
                elif tagDict[k] > 2 and result('oneway') == 'no':
                    if isAccident:
                        cost = cost + (value * 2)
                    else:
                        cost = cost + 5

            elif k == 'highway':
                if tagDict[k] == 'primary':
                    cost = cost - 20
                elif tagDict[k] == 'secondary':
                    cost = cost - 15
                elif tagDict[k] == 'trunk':
                    cost = cost - 10
                elif tagDict[k] == 'trunk_link':
                    cost = cost - 5

            elif k == 'maxspeed':
                if tagDict[k] >= 50:
                    cost = cost - tagDict[k]
                else:
                    cost = cost + tagDict[k]

        try:
            # Insert the updated cost for the way into our database.
            cur.execute("""INSERT INTO ways cost VALUES """ + cost +
                        " where way.id=" + result['id'] + ';')
        except:
            print "I can't INSERT"
        cur.close()
        conn.close()
Exemplo n.º 15
0
 def test_utf8(self):
     self.maxDiff = None
     #self.assertEqual(pghstore.loads('"åäö" => "åäö"'), {"åäö": "åäö"})
     s = '"name"=>"Noorwe\xc3\xab", "name2"=>"öäå"'
     self.assertEqual(pghstore.loads(s), {
         "name": "Noorwe\xc3\xab",
         "name2": "öäå"
     })
     names = '"name"=>"Norge/Noreg", "name:af"=>"Noorwe\xc3\xab", "name:ar"=>"\xd8\xa7\xd9\x84\xd9\x86\xd8\xb1\xd9\x88\xd9\x8a\xd8\xac", "name:be"=>"\xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:br"=>"Norvegia", "name:ca"=>"Noruega", "name:cs"=>"Norsko", "name:cy"=>"Norwy", "name:da"=>"Norge", "name:de"=>"Norwegen", "name:el"=>"\xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1", "name:en"=>"Norway", "name:eo"=>"Norvegio", "name:es"=>"Noruega", "name:et"=>"Norra", "name:fa"=>"\xd9\x86\xd8\xb1\xd9\x88\xda\x98", "name:fi"=>"Norja", "name:fo"=>"Noregur", "name:fr"=>"Norv\xc3\xa8ge", "name:fy"=>"Noarwegen", "name:ga"=>"An Iorua", "name:gd"=>"Nirribhidh", "name:he"=>"\xd7\xa0\xd7\x95\xd7\xa8\xd7\x95\xd7\x95\xd7\x92\xd7\x99\xd7\x94", "name:hr"=>"Norve\xc5\xa1ka", "name:hu"=>"Norv\xc3\xa9gia", "name:hy"=>"\xd5\x86\xd5\xb8\xd6\x80\xd5\xbe\xd5\xa5\xd5\xa3\xd5\xab\xd5\xa1", "name:id"=>"Norwegia", "name:is"=>"Noregur", "name:it"=>"Norvegia", "name:ja"=>"\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc", "name:la"=>"Norvegia", "name:lb"=>"Norwegen", "name:li"=>"Noorwege", "name:lt"=>"Norvegija", "name:lv"=>"Norv\xc4\x93\xc4\xa3ija", "name:mn"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8", "name:nb"=>"Norge", "name:nl"=>"Noorwegen", "name:nn"=>"Noreg", "name:no"=>"Norge", "name:pl"=>"Norwegia", "name:ru"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8\xd1\x8f", "name:sk"=>"N\xc3\xb3rsko", "name:sl"=>"Norve\xc5\xa1ka", "name:sv"=>"Norge", "name:th"=>"\xe0\xb8\x9b\xe0\xb8\xa3\xe0\xb8\xb0\xe0\xb9\x80\xe0\xb8\x97\xe0\xb8\xa8\xe0\xb8\x99\xe0\xb8\xad\xe0\xb8\xa3\xe0\xb9\x8c\xe0\xb9\x80\xe0\xb8\xa7\xe0\xb8\xa2\xe0\xb9\x8c", "name:tr"=>"Norve\xc3\xa7", "name:uk"=>"\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "name:vi"=>"Na Uy", "name:zh"=>"\xe6\x8c\xaa\xe5\xa8\x81", "name:haw"=>"Nolewai", "name:zh_py"=>"Nuowei", "name:zh_pyt"=>"Nu\xc3\xb3w\xc4\x93i", "official_name"=>"Kongeriket Norge", "official_name:be"=>"\xd0\x9a\xd0\xb0\xd1\x80\xd0\xb0\xd0\xbb\xd0\xb5\xd1\x9e\xd1\x81\xd1\x82\xd0\xb2\xd0\xb0 \xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f", "official_name:el"=>"\xce\x92\xce\xb1\xcf\x83\xce\xaf\xce\xbb\xce\xb5\xce\xb9\xce\xbf \xcf\x84\xce\xb7\xcf\x82 \xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1\xcf\x82", "official_name:en"=>"Kingdom of Norway", "official_name:id"=>"Kerajaan Norwegia", "official_name:it"=>"Regno di Norvegia", "official_name:ja"=>"\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc\xe7\x8e\x8b\xe5\x9b\xbd", "official_name:lb"=>"Kinneksr\xc3\xa4ich Norwegen", "official_name:lt"=>"Norvegijos Karalyst\xc4\x97", "official_name:sk"=>"N\xc3\xb3rske kr\xc3\xa1\xc4\xbeovstvo", "official_name:sv"=>"Konungariket Norge", "official_name:vi"=>"V\xc6\xb0\xc6\xa1ng qu\xe1\xbb\x91c Na Uy"'
     r = pghstore.loads(names)
     self.assertEqual(
         pghstore.loads(names), {
             "name": "Norge/Noreg",
             "name:af": "Noorwe\xc3\xab",
             "name:ar":
             "\xd8\xa7\xd9\x84\xd9\x86\xd8\xb1\xd9\x88\xd9\x8a\xd8\xac",
             "name:be":
             "\xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f",
             "name:br": "Norvegia",
             "name:ca": "Noruega",
             "name:cs": "Norsko",
             "name:cy": "Norwy",
             "name:da": "Norge",
             "name:de": "Norwegen",
             "name:el":
             "\xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1",
             "name:en": "Norway",
             "name:eo": "Norvegio",
             "name:es": "Noruega",
             "name:et": "Norra",
             "name:fa": "\xd9\x86\xd8\xb1\xd9\x88\xda\x98",
             "name:fi": "Norja",
             "name:fo": "Noregur",
             "name:fr": "Norv\xc3\xa8ge",
             "name:fy": "Noarwegen",
             "name:ga": "An Iorua",
             "name:gd": "Nirribhidh",
             "name:he":
             "\xd7\xa0\xd7\x95\xd7\xa8\xd7\x95\xd7\x95\xd7\x92\xd7\x99\xd7\x94",
             "name:hr": "Norve\xc5\xa1ka",
             "name:hu": "Norv\xc3\xa9gia",
             "name:hy":
             "\xd5\x86\xd5\xb8\xd6\x80\xd5\xbe\xd5\xa5\xd5\xa3\xd5\xab\xd5\xa1",
             "name:id": "Norwegia",
             "name:is": "Noregur",
             "name:it": "Norvegia",
             "name:ja":
             "\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc",
             "name:la": "Norvegia",
             "name:lb": "Norwegen",
             "name:li": "Noorwege",
             "name:lt": "Norvegija",
             "name:lv": "Norv\xc4\x93\xc4\xa3ija",
             "name:mn":
             "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8",
             "name:nb": "Norge",
             "name:nl": "Noorwegen",
             "name:nn": "Noreg",
             "name:no": "Norge",
             "name:pl": "Norwegia",
             "name:ru":
             "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd0\xb8\xd1\x8f",
             "name:sk": "N\xc3\xb3rsko",
             "name:sl": "Norve\xc5\xa1ka",
             "name:sv": "Norge",
             "name:th":
             "\xe0\xb8\x9b\xe0\xb8\xa3\xe0\xb8\xb0\xe0\xb9\x80\xe0\xb8\x97\xe0\xb8\xa8\xe0\xb8\x99\xe0\xb8\xad\xe0\xb8\xa3\xe0\xb9\x8c\xe0\xb9\x80\xe0\xb8\xa7\xe0\xb8\xa2\xe0\xb9\x8c",
             "name:tr": "Norve\xc3\xa7",
             "name:uk":
             "\xd0\x9d\xd0\xbe\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f",
             "name:vi": "Na Uy",
             "name:zh": "\xe6\x8c\xaa\xe5\xa8\x81",
             "name:haw": "Nolewai",
             "name:zh_py": "Nuowei",
             "name:zh_pyt": "Nu\xc3\xb3w\xc4\x93i",
             "official_name": "Kongeriket Norge",
             "official_name:be":
             "\xd0\x9a\xd0\xb0\xd1\x80\xd0\xb0\xd0\xbb\xd0\xb5\xd1\x9e\xd1\x81\xd1\x82\xd0\xb2\xd0\xb0 \xd0\x9d\xd0\xb0\xd1\x80\xd0\xb2\xd0\xb5\xd0\xb3\xd1\x96\xd1\x8f",
             "official_name:el":
             "\xce\x92\xce\xb1\xcf\x83\xce\xaf\xce\xbb\xce\xb5\xce\xb9\xce\xbf \xcf\x84\xce\xb7\xcf\x82 \xce\x9d\xce\xbf\xcf\x81\xce\xb2\xce\xb7\xce\xb3\xce\xaf\xce\xb1\xcf\x82",
             "official_name:en": "Kingdom of Norway",
             "official_name:id": "Kerajaan Norwegia",
             "official_name:it": "Regno di Norvegia",
             "official_name:ja":
             "\xe3\x83\x8e\xe3\x83\xab\xe3\x82\xa6\xe3\x82\xa7\xe3\x83\xbc\xe7\x8e\x8b\xe5\x9b\xbd",
             "official_name:lb": "Kinneksr\xc3\xa4ich Norwegen",
             "official_name:lt": "Norvegijos Karalyst\xc4\x97",
             "official_name:sk": "N\xc3\xb3rske kr\xc3\xa1\xc4\xbeovstvo",
             "official_name:sv": "Konungariket Norge",
             "official_name:vi": "V\xc6\xb0\xc6\xa1ng qu\xe1\xbb\x91c Na Uy"
         })
Exemplo n.º 16
0
def objects_by_id(id_list, options):
    _id_list = [int(i[1:]) for i in id_list if i[0] == 'n']
    plan = plpy.prepare(
        'select *, (select name from users where id=user_id) as user from nodes where id=any($1)',
        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id': 'n' + str(r['id']),
            'members': [],
            'tags': pghstore.loads(r['tags']),
            'geo': r['geom'],
            'types': ['node', 'point']
        }
        t['tags']['osm:id'] = str(t['id'])
        t['tags']['osm:version'] = str(r['version'])
        t['tags']['osm:user_id'] = str(r['user_id'])
        t['tags']['osm:user'] = r['user']
        t['tags']['osm:timestamp'] = str(r['tstamp'])
        t['tags']['osm:changeset'] = str(r['changeset_id'])
        yield (t)

    _id_list = [int(i[1:]) for i in id_list if i[0] == 'w']
    plan = plpy.prepare(
        'select id, tags, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, linestring as linestring, array_agg(node_id) as member_ids from (select ways.*, node_id from ways left join way_nodes on ways.id=way_nodes.way_id where ways.id=any($1) order by way_nodes.sequence_id) t group by id, tags, version, user_id, tstamp, changeset_id, linestring',
        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id':
            'w' + str(r['id']),
            'members': [{
                'member_id': 'n' + str(m),
                'sequence_id': str(i)
            } for i, m in enumerate(r['member_ids'])],
            'tags':
            pghstore.loads(r['tags']),
            'geo':
            r['linestring'],
            'types': ['way', 'line', 'area']
        }
        t['tags']['osm:id'] = str(t['id'])
        t['tags']['osm:version'] = str(r['version'])
        t['tags']['osm:user_id'] = str(r['user_id'])
        t['tags']['osm:user'] = r['user']
        t['tags']['osm:timestamp'] = str(r['tstamp'])
        t['tags']['osm:changeset'] = str(r['changeset_id'])
        yield (t)

    _id_list = [int(i[1:]) for i in id_list if i[0] == 'r']
    plan = plpy.prepare(
        'select id, tags, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, array_agg(lower(member_type) || member_id) as member_ids, array_agg(member_role) as member_roles from (select relations.*, member_type, member_id, member_role from relations left join relation_members on relations.id=relation_members.relation_id where relations.id=any($1) order by relation_members.sequence_id) t group by id, tags, version, user_id, tstamp, changeset_id',
        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id':
            'r' + str(r['id']),
            'tags':
            pghstore.loads(r['tags']),
            'members': [{
                'member_id': m[0],
                'role': m[1],
                'sequence_id': i
            } for i, m in enumerate(zip(r['member_ids'], r['member_roles']))],
            'geo':
            None,
            'types': ['relation']
        }
        t['tags']['osm:id'] = str(t['id'])
        t['tags']['osm:version'] = str(r['version'])
        t['tags']['osm:user_id'] = str(r['user_id'])
        t['tags']['osm:user'] = r['user']
        t['tags']['osm:timestamp'] = str(r['tstamp'])
        t['tags']['osm:changeset'] = str(r['changeset_id'])
        yield (t)
Exemplo n.º 17
0
    def costAlgo(r):
        parsedJson = json.loads(r[1])
        # This needs to change depending on the stream information. Then we handle that and calculate cost.
        fromHash = str(parsedJson["from"])
        geohash = Geohash.encode(fromHash.get("lat"),fromHash.get("lon"), 9)
        dest = str(parsedJson["to"])
        toHash = Geohash.encode(dest.get("lat"),dest.get("lon"), 8)

        keyHash = str(parsedJson["key"])
        tStamp = str(parsedJson["timestamp"])
        value = str(parsedJson["value"])

        # Try to connect and insert into our database
        try:
            conn = psycopg2.connect("dbname='DRP' user='******' password='******'")
        except:
            print "I am unable to connect to the database."

        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        try:
            result = None

            while result == None:
                cur.execute("""SELECT * FROM geohashed_ways WHERE geohash LIKE  """ + "'" + geohash + "'%;")
                result = cur.fetchone()
                geohash = geohash[:-1]

        except:
            print "I can't SELECT."

        # Compute the cost using the updated data that was streamed and data already existing in our database.
        tagDict = pghstore.loads(result['tags'])
        isAccident = False
        if keyHash == 'TRAFFIC_INCIDENT':
            cost = value*10
            isAccident = True
        else:
            cost = value

        for k,v in tagDict:
            if k == 'lanes':

                if tagDict[k] == 2 and result['oneway'] == 'yes':
                    if isAccident:
                        cost=cost+(value*10)
                    else:
                        cost=cost+20
                elif tagDict[k] == 2 and result['oneway'] == 'no':
                    if isAccident:
                        cost=cost+(value*5)
                    else:
                        cost=cost+15
                elif tagDict[k] > 2 and result('oneway') == 'yes':
                    if isAccident:
                        cost=cost+(value*5)
                    else:
                        cost=cost+10
                elif tagDict[k] > 2 and result('oneway') == 'no':
                    if isAccident:
                        cost=cost+(value*2)
                    else:
                        cost=cost+5

            elif k == 'highway':
                if tagDict[k] == 'primary':
                    cost=cost-20
                elif tagDict[k] == 'secondary':
                    cost=cost-15
                elif tagDict[k] == 'trunk':
                    cost=cost-10
                elif tagDict[k] == 'trunk_link':
                    cost=cost-5

            elif k == 'maxspeed':
                if tagDict[k] >= 50:
                    cost = cost-tagDict[k]
                else:
                    cost = cost+tagDict[k]

        try:
            # Insert the updated cost for the way into our database.
            cur.execute("""INSERT INTO ways cost VALUES """ + cost + " where way.id=" + result['id'] + ';')
        except:
            print "I can't INSERT"
        cur.close()
        conn.close()
Exemplo n.º 18
0
 def test_escaped_double_quote(self):
     self.assertEqual(pghstore.loads('"k\\"ey" => "va\\"lue"'),
                      {"k\\\"ey": "va\\\"lue"})
Exemplo n.º 19
0
 def test_escaped_double_quote(self):
     self.assertEqual(
         pghstore.loads('"k\\"ey" => "va\\"lue"'), 
         {"k\\\"ey": "va\\\"lue"});
Exemplo n.º 20
0
def objects_by_id(id_list, options):
    _id_list = [int(i[1:]) for i in id_list if i[0] == 'n']
    plan = plpy.prepare('select * from planet_osm_point where osm_id=any($1)',
                        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id': 'n' + str(r['osm_id']),
            'members': [],
            'geo': r['way'],
            'types': ['node', 'point']
        }
        # START db.columns.node
        t['tags'] = {
            k: r[k]
            for k in r
            if k not in ['id', 'geo', 'types', 'tags', 'way', 'osm_id']
            if r[k] is not None
        }
        # START db.has-hstore
        t['tags'] = dict(pghstore.loads(r['tags']).items() | t['tags'].items())
        # END db.has-hstore
        # END db.columns.node
        # START db.hstore-only
        t['tags'] = pghstore.loads(r['tags'])
        # END db.hstore-only
        t['tags']['osm:id'] = t['id']
        yield t

    _id_list = [int(i[1:]) for i in id_list if i[0] == 'w']
    plan = plpy.prepare(
        "select t.*, planet_osm_ways.nodes from (select osm_id, tags, way, 'line' as _type from planet_osm_line where osm_id=any($1) union select osm_id, tags, way, 'way' as _type from planet_osm_polygon where osm_id=any($1)) t left join planet_osm_ways on t.osm_id=planet_osm_ways.id",
        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id':
            'w' + str(r['osm_id']),
            'members': [{
                'member_id': 'n' + str(m),
                'sequence_id': str(i)
            } for i, m in enumerate(r['nodes'])],
            'geo':
            r['way'],
            'types': ['way', r['_type']]
        }
        # START db.columns.way
        t['tags'] = {
            k: r[k]
            for k in r if k not in
            ['osm_id', 'geo', 'types', 'tags', 'nodes', '_type', 'way']
            if r[k] is not None
        }
        # START db.has-hstore
        t['tags'] = dict(pghstore.loads(r['tags']).items() | t['tags'].items())
        # END db.has-hstore
        # END db.columns.way
        # START db.hstore-only
        t['tags'] = pghstore.loads(r['tags'])
        # END db.hstore-only
        t['tags']['osm:id'] = t['id']
        yield t

    _id_list = [int(i[1:]) for i in id_list if i[0] == 'r']
    plan = plpy.prepare(
        "select id, planet_osm_rels.tags, members, planet_osm_polygon.way from planet_osm_rels left join planet_osm_polygon on -planet_osm_rels.id=planet_osm_polygon.osm_id where id=any($1)",
        ['bigint[]'])
    res = plpy.cursor(plan, [_id_list])
    for r in res:
        t = {
            'id': 'r' + str(r['id']),
            'tags': flatarray_to_tags(r['tags']) if r['tags'] else {},
            'members': flatarray_to_members(r['members']),
            'geo': r['way'],
            'types':
            ['relation'] if r['way'] is None else ['relation', 'area']
        }
        t['tags']['osm:id'] = t['id']
        yield t
Exemplo n.º 21
0
def objects_bbox(_bbox,
                 db_selects,
                 options,
                 add_columns={},
                 add_param_type=[],
                 add_param_value=[]):
    import pghstore

    qry = ''

    bbox = ''
    replacements = {
        'parent_bbox': '',
    }

    if _bbox is not None:
        bbox = 'way && $1 and ST_Intersects(way, $1) and'
        replacements[
            'parent_bbox'] = 'way && $1 and ST_Intersects(way, $1) and'

    if len(add_columns):
        add_columns_qry = ', ' + ', '.join(
            [q + ' as "' + k + '"' for k, q in add_columns.items()])
    else:
        add_columns_qry = ''

    if _bbox:
        param_type = ['geometry'] + add_param_type
        param_value = [_bbox] + add_param_value
    else:
        param_type = add_param_type
        param_value = add_param_value

    # planet_osm_point
    w = []
    for t in ('*', 'node', 'point'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select 'n' || cast(osm_id as text) as id,
       way as geo, Array['point', 'node'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.node}
       {add_columns}
from planet_osm_point
where {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns_qry)

        qry = qry.replace('__PARENT_BBOX__', replacements['parent_bbox'])
        qry = qry.replace('__TYPE_SHORT__', 'n')
        qry = qry.replace('__TYPE_MODIFY__', '')

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {
                'id': r['id'],
                'geo': r['geo'],
                'types': r['types'],
            }

            # START db.columns.node
            t['tags'] = {
                k: r[k]
                for k in r if k not in ['id', 'geo', 'types', 'tags']
                and k not in add_columns if r[k] is not None
            }
            # START db.has-hstore
            t['tags'] = dict(
                pghstore.loads(r['tags']).items() | t['tags'].items())
            # END db.has-hstore
            # END db.columns.node
            # START db.hstore-only
            t['tags'] = pghstore.loads(r['tags'])
            # END db.hstore-only
            t['tags']['osm:id'] = str(r['id'])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_line - ways
    w = []
    for t in ('*', 'line', 'way'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select 'w' || cast(osm_id as text) as id,
       way as geo, Array['line', 'way'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_line
where osm_id>0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns_qry)
        qry = qry.replace('__PARENT_BBOX__', replacements['parent_bbox'])
        qry = qry.replace('__TYPE_SHORT__', 'w')
        qry = qry.replace('__TYPE_MODIFY__', '')

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {
                'id': r['id'],
                'geo': r['geo'],
                'types': r['types'],
            }

            # START db.columns.way
            t['tags'] = {
                k: r[k]
                for k in r if k not in ['id', 'geo', 'types', 'tags']
                and k not in add_columns if r[k] is not None
            }
            # START db.has-hstore
            t['tags'] = dict(
                pghstore.loads(r['tags']).items() | t['tags'].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t['tags'] = pghstore.loads(r['tags'])
            # END db.hstore-only
            t['tags']['osm:id'] = str(r['id'])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_line - relations
    w = []
    for t in ('*', 'line', 'relation'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select 'r' || cast(-osm_id as text) as id,
       way as geo, Array['line', 'relation'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_line
where osm_id<0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns_qry)
        qry = qry.replace('__PARENT_BBOX__', replacements['parent_bbox'])
        qry = qry.replace('__TYPE_SHORT__', 'w')
        qry = qry.replace('__TYPE_MODIFY__', '')

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {
                'id': r['id'],
                'geo': r['geo'],
                'types': r['types'],
            }

            # START db.columns.way
            t['tags'] = {
                k: r[k]
                for k in r if k not in ['id', 'geo', 'types', 'tags']
                and k not in add_columns if r[k] is not None
            }
            # START db.has-hstore
            t['tags'] = dict(
                pghstore.loads(r['tags']).items() | t['tags'].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t['tags'] = pghstore.loads(r['tags'])
            # END db.hstore-only
            t['tags']['osm:id'] = str(r['id'])

            for k in add_columns:
                t[k] = r[k]

            yield (t)

    # planet_osm_polygon - ways
    w = []
    for t in ('*', 'area', 'way'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select 'w' || cast(osm_id as text) as id,
       way as geo, Array['area', 'way'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_polygon
where osm_id>0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns_qry)

        qry = qry.replace('__PARENT_BBOX__', replacements['parent_bbox'])
        qry = qry.replace('__TYPE_SHORT__', 'r')
        qry = qry.replace('__TYPE_MODIFY__', '-')

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {
                'id': r['id'],
                'geo': r['geo'],
                'types': r['types'],
            }

            # START db.columns.way
            t['tags'] = {
                k: r[k]
                for k in r if k not in ['id', 'geo', 'types', 'tags']
                and k not in add_columns if r[k] is not None
            }
            # START db.has-hstore
            t['tags'] = dict(
                pghstore.loads(r['tags']).items() | t['tags'].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t['tags'] = pghstore.loads(r['tags'])
            # END db.hstore-only
            t['tags']['osm:id'] = str(r['id'])

            for k in add_columns:
                t[k] = r[k]

            yield (t)


# planet_osm_polygon - relations
    w = []
    for t in ('*', 'area', 'relation'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select 'r' || cast(-osm_id as text) as id,
       way as geo, Array['area', 'relation'] as types
# START db.has-hstore
       , tags
# END db.has-hstore
       {sql.columns.way}
       {add_columns}
from planet_osm_polygon
where osm_id<0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns_qry)

        qry = qry.replace('__PARENT_BBOX__', replacements['parent_bbox'])
        qry = qry.replace('__TYPE_SHORT__', 'r')
        qry = qry.replace('__TYPE_MODIFY__', '-')

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            t = {
                'id': r['id'],
                'geo': r['geo'],
                'types': r['types'],
            }

            # START db.columns.way
            t['tags'] = {
                k: r[k]
                for k in r if k not in ['id', 'geo', 'types', 'tags']
                and k not in add_columns if r[k] is not None
            }
            # START db.has-hstore
            t['tags'] = dict(
                pghstore.loads(r['tags']).items() | t['tags'].items())
            # END db.has-hstore
            # END db.columns.way
            # START db.hstore-only
            t['tags'] = pghstore.loads(r['tags'])
            # END db.hstore-only
            t['tags']['osm:id'] = str(r['id'])

            for k in add_columns:
                t[k] = r[k]

            yield (t)
Exemplo n.º 22
0
def objects_member_of(objects, other_selects, self_selects, options):
    if 'relation' in other_selects:
        plan = plpy.prepare(
            'select *, (select name from users where id=user_id) as user from relation_members join relations on relation_members.relation_id=relations.id where member_id=$1 and member_type=$2',
            ['bigint', 'text'])
        for ob in objects:
            member_id = ob['id']

            res = plpy.cursor(plan,
                              [int(member_id[1:]), member_id[0:1].upper()])
            for r in res:
                t = {
                    'id': 'r' + str(r['id']),
                    'tags': pghstore.loads(r['tags']),
                    'types': ['relation'],
                    'geo': None,
                }
                link_tags = {
                    'sequence_id': str(r['sequence_id']),
                    'role': str(r['member_role']),
                    'member_id':
                    r['member_type'].lower() + str(r['member_id']),
                }
                t['tags']['osm:id'] = str(t['id'])
                t['tags']['osm:version'] = str(r['version'])
                t['tags']['osm:user_id'] = str(r['user_id'])
                t['tags']['osm:user'] = r['user']
                t['tags']['osm:timestamp'] = str(r['tstamp'])
                t['tags']['osm:changeset'] = str(r['changeset_id'])
                yield ((ob, t, link_tags))

    if 'way' in other_selects:
        plan = plpy.prepare(
            'select *, (select name from users where id=user_id) as user from way_nodes join ways on way_nodes.way_id=ways.id where node_id=$1',
            ['bigint'])
        for o in objects:
            member_id = o['id']

            if member_id[0] != 'n':
                continue

            num_id = int(member_id[1:])
            res = plpy.cursor(plan, [num_id])
            for r in res:
                t = {
                    'id': 'w' + str(r['id']),
                    'tags': pghstore.loads(r['tags']),
                    'types': ['way'],
                    'geo': r['linestring'],
                }
                link_tags = {
                    'member_id': member_id,
                    'sequence_id': str(r['sequence_id'])
                }
                t['tags']['osm:id'] = str(t['id'])
                t['tags']['osm:version'] = str(r['version'])
                t['tags']['osm:user_id'] = str(r['user_id'])
                t['tags']['osm:user'] = r['user']
                t['tags']['osm:timestamp'] = str(r['tstamp'])
                t['tags']['osm:changeset'] = str(r['changeset_id'])
                yield ((ob, t, link_tags))
Exemplo n.º 23
0
def objects(_bbox, where_clauses, add_columns=[], add_param_type=[], add_param_value=[]):
    import pghstore

    qry = ''

    bbox = ''
    if _bbox is not None:
        bbox = 'way && $1 and'

    if len(add_columns):
        add_columns = ', ' + ', '.join(add_columns)
    else:
        add_columns = ''

    param_type = [ 'geometry' ] + add_param_type
    param_value = [ _bbox ] + add_param_value

    # planet_osm_point
    w = []
    for t in ('*', 'node', 'point'):
        if t in where_clauses:
            w.append(where_clauses[t])

    if len(w):
        qry = '''
select 'n' || cast(osm_id as text) as id,
       tags, way as geo, Array['point', 'node'] as types
       {add_columns}
from planet_osm_point
where {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns)

        plan = plpy.prepare(qry, param_type )
        res = plpy.execute(plan, param_value )

        for r in res:
            r['tags'] = pghstore.loads(r['tags'])
            yield(r)

    # planet_osm_line - ways
    w = []
    for t in ('*', 'line', 'way'):
        if t in where_clauses:
            w.append(where_clauses[t])

    if len(w):
        qry = '''
select 'w' || cast(osm_id as text) as id,
       tags, way as geo, Array['line', 'way'] as types
       {add_columns}
from planet_osm_line
where osm_id>0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns)

        plan = plpy.prepare(qry, param_type )
        res = plpy.execute(plan, param_value )

        for r in res:
            r['tags'] = pghstore.loads(r['tags'])
            yield(r)

    # planet_osm_line - relations
    w = []
    for t in ('*', 'line', 'relation'):
        if t in where_clauses:
            w.append(where_clauses[t])

    if len(w):
        qry = '''
select 'r' || cast(-osm_id as text) as id,
       tags, way as geo, Array['line', 'relation'] as types
       {add_columns}
from planet_osm_line
where osm_id<0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns)

        plan = plpy.prepare(qry, param_type )
        res = plpy.execute(plan, param_value )

        for r in res:
            r['tags'] = pghstore.loads(r['tags'])
            yield(r)

    # planet_osm_polygon - ways
    w = []
    for t in ('*', 'area', 'way'):
        if t in where_clauses:
            w.append(where_clauses[t])

    if len(w):
        qry = '''
select 'w' || cast(osm_id as text) as id,
       tags, way as geo, Array['area', 'way'] as types
       {add_columns}
from planet_osm_polygon
where osm_id>0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns)

        plan = plpy.prepare(qry, param_type )
        res = plpy.execute(plan, param_value )

        for r in res:
            r['tags'] = pghstore.loads(r['tags'])
            yield(r)

    # planet_osm_polygon - relations
    w = []
    for t in ('*', 'area', 'relation'):
        if t in where_clauses:
            w.append(where_clauses[t])

    if len(w):
        qry = '''
select 'r' || cast(-osm_id as text) as id,
       tags, way as geo, Array['area', 'relation'] as types
       {add_columns}
from planet_osm_polygon
where osm_id<0 and {bbox} ( {w} )
'''.format(bbox=bbox, w=' or '.join(w), add_columns=add_columns)

        plan = plpy.prepare(qry, param_type )
        res = plpy.execute(plan, param_value )

        for r in res:
            r['tags'] = pghstore.loads(r['tags'])
            yield(r)
Exemplo n.º 24
0
 def test_empty(self):
     self.assertEqual(pghstore.loads(''), {})
Exemplo n.º 25
0
 def test_empty(self):
     self.assertEqual(pghstore.loads(''), {})
Exemplo n.º 26
0
    def test_simple(self):
        self.assertEqual(pghstore.loads('"key" => "value"'), {"key": "value"})

        self.assertEqual(
            pghstore.loads('"key" => "value", "key2" => "value2"'), 
            {"key": "value", "key2": "value2"});
Exemplo n.º 27
0
    def parse(self):
        """
        Start parsing the osm file
        """

        # Configuration for OGR
        gdal.SetConfigOption('OSM_CONFIG_FILE', self._osm_conf)
        gdal.SetConfigOption('OSM_USE_CUSTOM_INDEXING', 'NO')

        if not isfile(self.__osmFile):
            raise GeoAlgorithmExecutionException("File doesn't exist")

        uri = self.__osmFile + "|layername="
        layers = {}

        # If loadOnly, no parsing required:
        # It's used only when we ask to open an osm file
        if self.__loadOnly:
            file_name = basename(self.__osmFile)
            for layer in self.__layers:
                layers[layer] = QgsVectorLayer(
                    uri + layer, file_name + " " + layer, "ogr")

                if not layers[layer].isValid():
                    print "Error on the layer", layers[layer].lastError()

            return layers

        # Check if the order is node before way,relation
        # We don't check way before relation,
        # because we can have only nodes and relations
        with open(self.__osmFile) as f:
            for line in f:
                if re.search(r'node', line):
                    break
                if re.search(r'(way|relation)', line):
                    raise WrongOrderOSMException

        # Foreach layers
        for layer in self.__layers:
            self.signalText.emit(tr("QuickOSM", u"Parsing layer : " + layer))
            layers[layer] = {}

            # Reading it with a QgsVectorLayer
            layers[layer]['vectorLayer'] = QgsVectorLayer(
                uri + layer, "test_" + layer, "ogr")

            if not layers[layer]['vectorLayer'].isValid():
                msg = "Error on the layer : " + \
                      layers[layer]['vectorLayer'].lastError()
                raise GeoAlgorithmExecutionException(msg)

            # Set some default tags
            layers[layer]['tags'] = ['full_id', 'osm_id', 'osm_type']

            # Save the geometry type of the layer
            layers[layer]['geomType'] = layers[layer]['vectorLayer'].wkbType()

            # Set a featureCount
            layers[layer]['featureCount'] = 0

            # Get the other_tags
            fields = layers[layer]['vectorLayer'].pendingFields()
            field_names = [field.name() for field in fields]
            other_tags_index = field_names.index('other_tags')

            features = layers[layer]['vectorLayer'].getFeatures()
            for i, feature in enumerate(features):
                layers[layer]['featureCount'] += 1

                # Improve the parsing if comma in whitelist,
                # we skip the parsing of tags, but featureCount is needed
                if self.__whiteListColumn[layer] == ',':
                    continue

                # Get the "others_tags" field
                attributes = feature.attributes()[other_tags_index]

                if attributes:
                    h_store = pghstore.loads(attributes)
                    for key in h_store:
                        if key not in layers[layer]['tags']:
                            # If the key in OSM is not already in the table
                            if self.__whiteListColumn[layer]:
                                if key in self.__whiteListColumn[layer]:
                                    layers[layer]['tags'].append(key)
                            else:
                                layers[layer]['tags'].append(key)

                percent = int(100 / len(self.__layers) * (i + 1))
                self.signalPercentage.emit(percent)

        # Delete empty layers if this option is set to True
        if self.__deleteEmptyLayers:
            delete_layers = []
            for keys, values in layers.iteritems():
                if values['featureCount'] < 1:
                    delete_layers.append(keys)
            for layer in delete_layers:
                del layers[layer]

        # Creating GeoJSON files for each layers
        for layer in self.__layers:
            msg = tr("QuickOSM", u"Creating GeoJSON file : " + layer)
            self.signalText.emit(msg)
            self.signalPercentage.emit(0)

            # Creating the temp file
            tf = tempfile.NamedTemporaryFile(
                delete=False, suffix="_" + layer + ".geojson")
            layers[layer]['geojsonFile'] = tf.name
            tf.flush()
            tf.close()

            # Adding the attribute table
            fields = QgsFields()
            for key in layers[layer]['tags']:
                fields.append(QgsField(key, QVariant.String))

            encoding = get_default_encoding()
            file_writer = QgsVectorFileWriter(
                layers[layer]['geojsonFile'],
                encoding,
                fields,
                layers[layer]['geomType'],
                layers[layer]['vectorLayer'].crs(),
                'GeoJSON')

            # Foreach feature in the layer
            features = layers[layer]['vectorLayer'].getFeatures()
            for i, feature in enumerate(features):
                fet = QgsFeature()
                fet.setGeometry(feature.geometry())

                new_attributes = []
                attributes = feature.attributes()

                if layer in ['points', 'lines', 'multilinestrings']:
                    if layer == 'points':
                        osm_type = "node"
                    elif layer == 'lines':
                        osm_type = "way"
                    elif layer == 'multilinestrings':
                        osm_type = 'relation'

                    new_attributes.append(
                        self.DIC_OSM_TYPE[osm_type] + str(attributes[0]))
                    new_attributes.append(attributes[0])
                    new_attributes.append(osm_type)

                    if attributes[1]:
                        h_store = pghstore.loads(attributes[1])
                        for tag in layers[layer]['tags'][3:]:
                            if unicode(tag) in h_store:
                                new_attributes.append(h_store[tag])
                            else:
                                new_attributes.append("")
                        fet.setAttributes(new_attributes)
                        file_writer.addFeature(fet)

                elif layer == 'multipolygons':
                    if attributes[0]:
                        osm_type = "relation"
                        new_attributes.append(
                            self.DIC_OSM_TYPE[osm_type] + str(attributes[0]))
                        new_attributes.append(str(attributes[0]))
                    else:
                        osm_type = "way"
                        new_attributes.append(
                            self.DIC_OSM_TYPE[osm_type] + str(attributes[1]))
                        new_attributes.append(attributes[1])
                    new_attributes.append(osm_type)

                    h_store = pghstore.loads(attributes[2])
                    for tag in layers[layer]['tags'][3:]:
                        if unicode(tag) in h_store:
                            new_attributes.append(h_store[tag])
                        else:
                            new_attributes.append("")
                    fet.setAttributes(new_attributes)
                    file_writer.addFeature(fet)

                    percentage = int(
                        100 / layers[layer]['featureCount'] * (i + 1))
                    self.signalPercentage.emit(percentage)

            del file_writer

        return layers
Exemplo n.º 28
0
def objects_bbox(_bbox,
                 db_selects,
                 options,
                 add_columns={},
                 add_param_type=[],
                 add_param_value=[]):
    import pghstore

    qry = ''

    if len(add_columns):
        add_columns_qry = ', ' + ', '.join(
            [q + ' as "' + k + '"' for k, q in add_columns.items()])
    else:
        add_columns_qry = ''

    if _bbox:
        param_type = ['geometry'] + add_param_type
        param_value = [_bbox] + add_param_value
    else:
        param_type = add_param_type
        param_value = add_param_value

    # nodes
    w = []
    for t in ('*', 'node', 'point'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ''
        if _bbox is not None:
            bbox = 'geom && $1 and ST_Intersects(geom, $1) and'

        qry = '''
select 'n' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, geom as geo, Array['point', 'node'] as types
       {add_columns}
from nodes
where {bbox} ( {w} )
'''.format(bbox=bbox,
           w=' or '.join(w),
           add_columns=add_columns_qry.replace('__geo__', 'geom'))

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r['types'] = list(r['types'])
            r['tags'] = pghstore.loads(r['tags'])
            r['tags']['osm:id'] = str(r['id'])
            r['tags']['osm:version'] = str(r['version'])
            r['tags']['osm:user_id'] = str(r['user_id'])
            r['tags']['osm:user'] = r['user']
            r['tags']['osm:timestamp'] = str(r['tstamp'])
            r['tags']['osm:changeset'] = str(r['changeset_id'])
            yield (r)

    # ways
    w = []
    for t in ('*', 'line', 'area', 'way'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ''
        if _bbox is not None:
            bbox = 'linestring && $1 and (ST_NPoints(linestring) = 1 or ST_Intersects(linestring, $1)) and'

        qry = '''
select * {add_columns} from (
select 'w' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, (CASE WHEN ST_NPoints(linestring) >= 4 and ST_IsClosed(linestring) THEN ST_MakePolygon(linestring) ELSE linestring END) as geo, (ST_NPoints(linestring) >= 4) and ST_IsClosed(linestring) as is_closed, Array['line', 'way'] as types
       '''
        # START db.multipolygons
        # START db.multipolygons-v0.2
        # deprecated by osmosis-multipolygon v0.3
        qry += '''
, (select array_agg(has_outer_tags) from relation_members join multipolygons on relation_members.relation_id=multipolygons.id where relation_members.member_id=ways.id and relation_members.member_type='W' and relation_members.member_role in ('outer', 'exclave')) part_of_mp_outer
        '''
        # ELSE db.multipolygons-v0.2
        qry += '''
, (select array_agg(true) from multipolygons where hide_outer_ways @> Array[ways.id]) part_of_mp_outer
        '''
        # END db.multipolygons-v0.2
        # END db.multipolygons
        qry += '''
from ways
where {bbox} ( {w} ) offset 0) t
'''

        qry = qry.format(bbox=bbox,
                         w=' or '.join(w),
                         add_columns=add_columns_qry.replace('__geo__', 'geo'))

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r['types'] = list(r['types'])
            r['tags'] = pghstore.loads(r['tags'])
            if r['is_closed']:
                # START db.multipolygons
                if not r['part_of_mp_outer'] or True not in r[
                        'part_of_mp_outer']:
                    # END db.multipolygons
                    r['types'].append('area')
            r['tags']['osm:id'] = str(r['id'])
            r['tags']['osm:version'] = str(r['version'])
            r['tags']['osm:user_id'] = str(r['user_id'])
            r['tags']['osm:user'] = r['user']
            r['tags']['osm:timestamp'] = str(r['tstamp'])
            r['tags']['osm:changeset'] = str(r['changeset_id'])
            yield (r)

    done_multipolygons = set()
    # START db.multipolygons
    # multipolygons
    w = []
    for t in ('*', 'relation', 'area'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        bbox = ''
        if _bbox is not None:
            bbox = 'geom && $1 and ST_Intersects(geom, $1) and'

        qry = '''
select * {add_columns} from (
select (CASE WHEN has_outer_tags THEN 'm' ELSE 'r' END) || cast(id as text) as id, id as rid, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id, has_outer_tags,
       tags, geom as geo, Array['area'] as types
from (select multipolygons.*, relations.version, relations.user_id, relations.tstamp, relations.changeset_id from multipolygons left join relations on multipolygons.id = relations.id) t
where {bbox} ( {w} ) offset 0) t
'''.format(bbox=bbox,
           w=' or '.join(w),
           add_columns=add_columns_qry.replace('__geo__', 'geo'))

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r['types'] = list(r['types'])
            r['tags'] = pghstore.loads(r['tags'])
            r['tags']['osm:id'] = str(r['id'])
            r['tags']['osm:version'] = str(r['version'])
            r['tags']['osm:user_id'] = str(r['user_id'])
            r['tags']['osm:user'] = r['user']
            r['tags']['osm:timestamp'] = str(r['tstamp'])
            r['tags']['osm:changeset'] = str(r['changeset_id'])
            if r['has_outer_tags']:
                r['tags']['osm:has_outer_tags'] = 'yes'
            else:
                done_multipolygons.add(r['rid'])
                r['types'].append('relation')
            yield (r)


# END db.multipolygons

# relations - (no bbox match!)
    w = []
    for t in ('*', 'relation'):
        if t in db_selects:
            w.append(db_selects[t])

    if len(w):
        qry = '''
select * {add_columns} from (
select 'r' || cast(id as text) as id, version, user_id, (select name from users where id=user_id) as user, tstamp, changeset_id,
       tags, null as geo, Array['relation'] as types
from relations
where ({w}) and not id = ANY(Array[{done}]::bigint[])) t
'''.format(w=' or '.join(w),
           add_columns=add_columns_qry,
           done=','.join({str(d)
                          for d in done_multipolygons}))

        plan = plpy.prepare(qry, param_type)
        res = plpy.cursor(plan, param_value)

        for r in res:
            r['types'] = list(r['types'])
            r['tags'] = pghstore.loads(r['tags'])
            r['tags']['osm:id'] = str(r['id'])
            r['tags']['osm:version'] = str(r['version'])
            r['tags']['osm:user_id'] = str(r['user_id'])
            r['tags']['osm:user'] = r['user']
            r['tags']['osm:timestamp'] = str(r['tstamp'])
            r['tags']['osm:changeset'] = str(r['changeset_id'])
            yield (r)