def query_by_radius(self, lat, lon, radius):
"""
return tickets within a given radius
[radius in m,
see
http://postgis.refractions.net/documentation/manual-1.3/ch06.html#id2577138
]
"""
# Distance functions: see
# * http://postgis.refractions.net/pipermail/postgis-users/2006-January/010534.html
# * ST_DISTANCE : http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.spatial.doc/rsbp4047.html
# * http://postgis.refractions.net/documentation/manual-1.3/ch06.html#id2574404
# * http://postgis.refractions.net/documentation/manual-1.3/ch06.html#distance_spheroid
# * http://www.movable-type.co.uk/scripts/latlong.html#ellipsoid
# * http://oss.openplans.org/MobileGeoTrac/ticket/54#comment:12
query_str = "ST_DISTANCE_SPHERE(st_pointfromtext('POINT(' || longitude || ' ' || latitude || ')'), ST_PointFromText('POINT(%s %s)')) < %s" % (
lon, lat, radius)
tickets = get_column(self.env,
'ticket_location',
'ticket',
where=query_str)
assert tickets is not None
return tickets
def query_by_radius(self, lat, lon, radius):
"""
return tickets within a given radius
[radius in m,
see
http://postgis.refractions.net/documentation/manual-1.3/ch06.html#id2577138
]
"""
# Distance functions: see
# * http://postgis.refractions.net/pipermail/postgis-users/2006-January/010534.html
# * ST_DISTANCE : http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.spatial.doc/rsbp4047.html
# * http://postgis.refractions.net/documentation/manual-1.3/ch06.html#id2574404
# * http://postgis.refractions.net/documentation/manual-1.3/ch06.html#distance_spheroid
# * http://www.movable-type.co.uk/scripts/latlong.html#ellipsoid
# * http://oss.openplans.org/MobileGeoTrac/ticket/54#comment:12
query_str = (
"ST_DISTANCE_SPHERE(st_pointfromtext('POINT(' || longitude || ' ' || latitude || ')'), ST_PointFromText('POINT(%s %s)')) < %s"
% (lon, lat, radius)
)
tickets = get_column(self.env, "ticket_location", "ticket", where=query_str)
assert tickets is not None
return tickets
def upgrade_environment(self, db):
"""Actually perform an environment upgrade.
Implementations of this method should not commit any database
transactions. This is done implicitly after all participants have
performed the upgrades they need without an error being raised.
"""
# create a table for ticket location
ticket_location_table = Table("ticket_location", key="ticket")[
Column("ticket", type="int"),
Column("latitude", type="float"),
Column("longitude", type="float"),
Index(["ticket"]),
]
create_table(self.env, ticket_location_table)
# update ticket locations
tickets = get_column(self.env, "ticket", "id")
tickets = [Ticket(self.env, ticket) for ticket in tickets]
for ticket in tickets:
try:
location, (lat, lon) = self.locate_ticket(ticket)
self.set_location(ticket.id, lat, lon)
except GeolocationException:
pass
# note the DB version
execute_non_query(self.env, "insert into system (name, value) values ('geoticket.db_version', '1');")
# add a default dashboard panel
self.default_dashboard()
def upgrade_environment(self, db):
"""Actually perform an environment upgrade.
Implementations of this method should not commit any database
transactions. This is done implicitly after all participants have
performed the upgrades they need without an error being raised.
"""
# create a table for ticket location
ticket_location_table = Table(
'ticket_location', key='ticket')[Column('ticket', type='int'),
Column('latitude', type='float'),
Column('longitude', type='float'),
Index(['ticket'])]
create_table(self.env, ticket_location_table)
# update ticket locations
tickets = get_column(self.env, 'ticket', 'id')
tickets = [Ticket(self.env, ticket) for ticket in tickets]
for ticket in tickets:
try:
location, (lat, lon) = self.locate_ticket(ticket)
self.set_location(ticket.id, lat, lon)
except GeolocationException:
pass
# note the DB version
execute_non_query(
self.env,
"insert into system (name, value) values ('geoticket.db_version', '1');"
)
# add a default dashboard panel
self.default_dashboard()
def tickets_in_region(self, region):
assert self.column
srid = self.srid()
assert srid is not None
the_geom = "SELECT the_geom FROM georegions WHERE %s=%s" % (self.column, column_repr(self.env, 'georegions', self.column, region))
if srid != 4326:
the_geom = 'ST_TRANSFORM((%s), 4326)' % the_geom
query_str = "ST_CONTAINS((%s), st_pointfromtext('POINT(' || longitude || ' ' || latitude || ')', 4326))" % the_geom
tickets = get_column(self.env, 'ticket_location', 'ticket', where=query_str)
assert tickets is not None
return tickets
def process_request(self, req):
"""Process the request. For ClearSilver, return a (template_name,
content_type) tuple, where `template` is the ClearSilver template to use
(either a `neo_cs.CS` object, or the file name of the template), and
`content_type` is the MIME type of the content. For Genshi, return a
(template_name, data, content_type) tuple, where `data` is a dictionary
of substitutions for the template.
For both templating systems, "text/html" is assumed if `content_type` is
`None`.
Note that if template processing should not occur, this method can
simply send the response itself and not return anything.
"""
gids = get_column(self.env, 'georegions', 'gid')
regions = {}
georegions_columns = columns(self.env, 'georegions')
for gid in gids:
regions[gid] = {}
regions[gid]['data'] = {}
_columns = [ column for column in georegions_columns
if column not in set(['gid', 'the_geom']) ]
for column in _columns:
regions[gid]['data'][column] = get_scalar(self.env, "SELECT %s FROM georegions WHERE gid=%s" % (column, gid))
regions[gid]['region'] = Markup(get_scalar(self.env, "SELECT ST_AsKML(the_geom) FROM georegions WHERE gid=%s" % gid))
# filter out non-matching results
# XXX this is hacky, but I'm not sure how to do this in SQL
filter = {}
for key, value in req.args.items():
if key in georegions_columns:
filter[key] = value
for key in regions.keys():
for _key, _value in filter.items():
if str(regions[key]['data'][_key]) != _value:
del regions[key]
break
return 'region.kml', dict(regions=regions), 'application/vnd.google-earth.kml+xml'
def regions(self):
if self.column:
return (self.column_label or self.column, get_column(self.env, 'georegions', self.column))
def tickets_with_location(self):
"""return ids of all located tickets"""
return set(get_column(self.env, "ticket_location", "ticket"))
def tickets_with_location(self):
"""return ids of all located tickets"""
return set(get_column(self.env, 'ticket_location', 'ticket'))