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'))