Beispiel #1
0
    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
Beispiel #2
0
    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
Beispiel #3
0
    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()
Beispiel #4
0
    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()
Beispiel #5
0
 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
Beispiel #6
0
    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'
Beispiel #7
0
 def regions(self):
     if self.column:
         return (self.column_label or self.column, get_column(self.env, 'georegions', self.column))
Beispiel #8
0
 def tickets_with_location(self):
     """return ids of all located tickets"""
     return set(get_column(self.env, "ticket_location", "ticket"))
Beispiel #9
0
 def tickets_with_location(self):
     """return ids of all located tickets"""
     return set(get_column(self.env, 'ticket_location', 'ticket'))