def coord_distance(self):
if self.coord_dist is None:
query=""" SELECT
ST_Distance(
ST_GeographyFromText('POINT({osm_lon} {osm_lat})'),
ST_GeographyFromText('POINT({wikipedia_lon} {wikipedia_lat})')
);
"""
query=query.format(osm_lon = self.item.osm_lon,
osm_lat = self.item.osm_lat,
wikipedia_lon = self.item.wikipedia_lon,
wikipedia_lat = self.item.wikipedia_lat
)
#logger.debug(query)
self.coord_dist = -1
geodb = PostgreSQLConnector(WOC['GEODB'])
geodb.connect()
res = geodb.query(query)
geodb.close()
if res:
if len(res) > 0:
self.coord_dist = res[0][0]
return self.coord_dist
def intersection(self):
if self.intersects is None:
if self.item.osm_type == 'point':
query=""" SELECT
ST_DWithin(
ST_GeographyFromText('POINT({osm_lon} {osm_lat})'),
ST_GeographyFromText('POINT({wikipedia_lon} {wikipedia_lat})'),
20
);
"""
query=query.format(osm_lon = self.item.osm_lon,
osm_lat = self.item.osm_lat,
wikipedia_lon = self.item.wikipedia_lon,
wikipedia_lat = self.item.wikipedia_lat
)
else:
query=""" SELECT
ST_Contains(
osmgeom.way,
ST_Transform(ST_GeometryFromText('POINT({wikipedia_lon} {wikipedia_lat})',4326),900913)
)
FROM (SELECT way
FROM planet_osm_{type}
WHERE osm_id = {osm_id}
) AS osmgeom;
"""
# query=""" SELECT
# ST_Within(
# ST_GeometryFromText('POINT({wikipedia_lon} {wikipedia_lat})',4326),
# ST_Transform(osmgeom.way,4326)
# )
# FROM (SELECT way
# FROM planet_osm_{type}
# WHERE osm_id = {osm_id}
# ) AS osmgeom;
# """
query=query.format(wikipedia_lon = self.item.wikipedia_lon,
wikipedia_lat = self.item.wikipedia_lat,
type = self.item.osm_type,
osm_id = self.item.osm_id
)
logger.debug(query)
self.intersects = False
geodb = PostgreSQLConnector(WOC['GEODB'])
geodb.connect()
res = geodb.query(query)
if len(res) > 0:
self.intersects = res[0][0]
geodb.close()
return self.intersects