def query_candidates(conn, road_table_name, sequence, search_radius):
"""
サーチ円内に存在するシーケンスデータの各々の計測データの候補をクエリーする
Query candidates of each measurement in a sequence within
search_radius.
"""
stmt = '''
WITH
--- WITH sequence AS (subquery here),
seq AS (SELECT *,
ST_SetSRID(ST_MakePoint(ST_X({sequence_name}.way), ST_Y({sequence_name}.way)), 4326) AS geom,
ST_SetSRID(ST_MakePoint(ST_X({sequence_name}.way), ST_Y({sequence_name}.way)), 4326)::geography AS geog
FROM {sequence_name})
SELECT seq.csv_id, ST_X(seq.way) as lon, ST_Y(seq.way) as lat, seq.ptime,
--- Edge information
edge.gid, edge.source, edge.target,
edge.length, edge.length,
--- Location, a float between 0 and 1 representing the location of the closest point on the edge to the measurement.
ST_LineLocatePoint(edge.the_geom, seq.geom) AS location,
--- Distance in meters from the measurement to its candidate's location
ST_Distance(seq.geog, edge.the_geom::geography) AS distance,
--- Candidate's location (a position along the edge)
ST_X(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clon,
ST_Y(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clat
FROM seq CROSS JOIN {road_table_name} AS edge
WHERE edge.the_geom && ST_Envelope(ST_Buffer(seq.geog, {search_radius})::geometry)
AND ST_DWithin(seq.geog, edge.the_geom::geography, {search_radius})
'''.format(road_table_name=road_table_name,
sequence_name=sequence,
search_radius=search_radius)
cur = conn.cursor()
cur.execute(stmt)
for mid, mlon, mlat, mdt, \
eid, source, target, cost, reverse_cost, \
location, distance, \
clon, clat in cur:
measurement = Measurement(id=mid, lon=mlon, lat=mlat)
edge = Edge(id=eid,
start_node=source,
end_node=target,
cost=cost,
reverse_cost=reverse_cost)
assert 0 <= location <= 1
candidate = Candidate(measurement=measurement,
edge=edge,
location=location,
distance=distance)
# Coordinate along the edge (not needed by MM but might be
# useful info to users)
candidate.lon = clon # マッチングポイント X
candidate.lat = clat # マッチングポイント Y
candidate.mlon = mlon # プローブポイント X
candidate.mlat = mlat # プローブポイント Y
candidate.ptime = mdt # プローブ日付(TIMESTAMP)
candidate.edgeflg = 0
yield candidate
cur.close()
def query_candidates(conn, road_table_name, sequence, search_radius):
"""
Query candidates of each measurement in a sequence within
search_radius.
"""
subquery = create_sequence_subquery(len(sequence), ('id', 'lon', 'lat'))
subquery = subquery + ',' + '''
--- WITH sequence AS (subquery here),
seq AS (SELECT *,
ST_SetSRID(ST_MakePoint(sequence.lon, sequence.lat), 4326) AS geom,
ST_SetSRID(ST_MakePoint(sequence.lon, sequence.lat), 4326)::geography AS geog
FROM sequence)
'''
stmt = subquery + '''
SELECT seq.id, seq.lon, seq.lat,
--- Edge information
edge.gid, edge.source, edge.target,
edge.length, edge.length,
--- Location, a float between 0 and 1 representing the location of the closest point on the edge to the measurement.
ST_LineLocatePoint(edge.the_geom, seq.geom) AS location,
--- Distance in meters from the measurement to its candidate's location
ST_Distance(seq.geog, edge.the_geom::geography) AS distance,
--- Candidate's location (a position along the edge)
ST_X(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clon,
ST_Y(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clat
FROM seq CROSS JOIN {road_table_name} AS edge
WHERE edge.the_geom && ST_Envelope(ST_Buffer(seq.geog, %s)::geometry)
AND ST_DWithin(seq.geog, edge.the_geom::geography, %s)
'''.format(road_table_name=road_table_name)
# Aggregate and flatten params
params = sum([[idx, lon, lat] for idx, (lon, lat) in enumerate(sequence)], [])
params.append(search_radius)
params.append(search_radius)
cur = conn.cursor()
cur.execute(stmt, params)
for mid, mlon, mlat, \
eid, source, target, cost, reverse_cost, \
location, distance, \
clon, clat in cur:
measurement = Measurement(id=mid, lon=mlon, lat=mlat)
edge = Edge(id=eid, start_node=source, end_node=target, cost=cost, reverse_cost=reverse_cost)
assert 0 <= location <= 1
candidate = Candidate(measurement=measurement, edge=edge, location=location, distance=distance)
# Coordinate along the edge (not needed by MM but might be
# useful info to users)
candidate.lon = clon
candidate.lat = clat
yield candidate
cur.close()
def query_candidates(conn, road_table_name, sequence, search_radius):
"""
Query candidates of each measurement in a sequence within
search_radius.
"""
subquery = create_sequence_subquery(len(sequence), ('id', 'lon', 'lat'))
subquery = subquery + ',' + '''
--- WITH sequence AS (subquery here),
seq AS (SELECT *,
ST_SetSRID(ST_MakePoint(sequence.lon, sequence.lat), 4326) AS geom,
ST_SetSRID(ST_MakePoint(sequence.lon, sequence.lat), 4326)::geography AS geog
FROM sequence)
'''
stmt = subquery + '''
SELECT seq.id, seq.lon, seq.lat,
--- Edge information
edge.gid, edge.source, edge.target,
edge.length, edge.length,
--- Location, a float between 0 and 1 representing the location of the closest point on the edge to the measurement.
ST_LineLocatePoint(edge.the_geom, seq.geom) AS location,
--- Distance in meters from the measurement to its candidate's location
ST_Distance(seq.geog, edge.the_geom::geography) AS distance,
--- Candidate's location (a position along the edge)
ST_X(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clon,
ST_Y(ST_ClosestPoint(edge.the_geom, seq.geom)) AS clat,
---modifications on 3/11/16 : addition of class_id and road_type
--- Class_id(class id of all the roads)
edge.class_id
--- Type_of_road(classification of roads)
FROM seq CROSS JOIN {road_table_name} AS edge
WHERE edge.the_geom && ST_Envelope(ST_Buffer(seq.geog, %s)::geometry)
AND ST_DWithin(seq.geog, edge.the_geom::geography, %s)
'''.format(road_table_name=road_table_name)
# Aggregate and flatten params
params = sum([[idx, lon, lat] for idx, (lon, lat) in enumerate(sequence)],
[])
params.append(search_radius)
params.append(search_radius)
cur = conn.cursor()
cur.execute(stmt, params)
'''modifications on 3/11/16 : addition of class_id and road_type'''
for mid, mlon, mlat, eid, source, target, cost, reverse_cost, location, distance, clon, clat, class_id in cur:
measurement = Measurement(id=mid, lon=mlon, lat=mlat)
edge = Edge(id=eid,
start_node=source,
end_node=target,
cost=cost,
reverse_cost=reverse_cost)
'''modifications begin'''
if class_id in (101, 102, 103, 104, 105, 106, 107, 108, 109, 124, 125):
road_type = 'highway'
elif class_id in (110, 111, 112, 113, 114, 115, 116, 117, 118, 119,
120, 121, 122, 301, 303, 304, 305):
road_type = 'local'
else:
road_type = 'others'
assert 0 <= location <= 1
candidate = Candidate(measurement=measurement,
edge=edge,
location=location,
distance=distance)
''' modifications end'''
# Coordinate along the edge (not needed by MM but might be
# useful info to users)
candidate.lon = clon
candidate.lat = clat
candidate.class_id = class_id
candidate.road_type = road_type
yield candidate
cur.close()