public function os_grid_to_lat_lng($easting, $northing)
{
include_once GEO_DIR . "lib/coords.php";
$os = new OSRef($easting, $northing);
$ll = $os->toLatLng();
$ll->OSGB36ToWGS84();
return array('lat' => $ll->lat, 'lng' => $ll->lng);
}
function format_stop($row)
{
$easting = (int) $row['Location_Easting'];
$northing = (int) $row['Location_Northing'];
$os = new OSRef($easting, $northing);
$latlng = $os->toLatLng();
$latlng->OSGB36ToWGS84();
$stop = array('bus_stop_code' => $row['Bus_Stop_Code'], 'stop_name' => ucwords(strtolower($row['Stop_Name'])), 'location_os' => array('easting' => $easting, 'northing' => $northing), 'location' => array('lat' => $latlng->lat, 'lng' => $latlng->lng), 'heading' => (int) $row['Heading'], 'virtual_bus_stop' => (int) $row['Virtual_Bus_Stop']);
return $stop;
}
public function up()
{
$sql0 = Yii::app()->db->createCommand("SELECT * FROM transportStations")->queryAll();
if (count($sql0) != 0) {
Yii::app()->db->createCommand("truncate table transportStations")->execute();
$res2 = Yii::app()->db->createCommand("truncate table link_transportStations_to_transportTypes")->execute();
}
$transportStationsTitle = Yii::app()->db->createCommand("Show columns from transportStations WHERE Field = 'title'")->queryAll();
if ($transportStationsTitle[0]['Key'] == 'UNI') {
@Yii::app()->db->createCommand("ALTER TABLE transportStations DROP INDEX title")->execute();
}
@Yii::app()->db->createCommand("ALTER TABLE transportStations ADD UNIQUE (title)")->execute();
$command = Yii::app()->db->createCommand("SELECT * FROM places");
$places = $command->queryAll();
$i = 0;
foreach ($places as $place) {
$type = $place['place_type'];
$title = $place['place_title'];
$desc = $place['place_desc'];
$objCoOrdinates = new OSRef($place['place_osx'], $place['place_osy']);
$coOrdinates = $objCoOrdinates->toLatLng();
// convert in lat ang lng and insert in to table transportStations
$lat = $coOrdinates->lat;
$lng = $coOrdinates->lng;
$user = 0;
$dtTm = date('Y-m-d H:i:s');
$sql1 = Yii::app()->db->createCommand("SELECT * FROM transportStations WHERE title = '{$title}' ")->queryAll();
if (count($sql1) == 0) {
$sql = "INSERT INTO transportStations(title,description,latitude,longitude,createdBy,createdDt,modifiedBy,modifiedDt,statusId) VALUES ('{$title}','{$desc}','{$lat}','{$lng}','{$user}','{$dtTm}','{$user}','{$dtTm}','1')";
$command = Yii::app()->db->createCommand($sql);
$res = $command->execute();
// convert in lat ang lng and insert in to table transportStations
// insert all types in table link_transportStations_to_transportTypes
if (!empty($res)) {
$i++;
$thisType = $type == 1 ? 2 : 1;
$sql = "INSERT INTO link_transportStations_to_transportTypes(transportStation,transportType,status) VALUES ('{$i}','{$thisType}','1')";
Yii::app()->db->createCommand($sql)->execute();
}
}
// insert all types in table link_transportStations_to_transportTypes
}
// Import file
$csvFile = Yii::getPathOfAlias('application.data') . '/transport_station_positions.csv';
$importFileSql = "LOAD DATA LOCAL INFILE '" . $csvFile . "'\n\t\t\t\t\t\t\tREPLACE\n\t\t\t\t\t\t\tINTO TABLE transportStations\n\t\t\t\t\t\t\tCOLUMNS TERMINATED BY ','\n\t\t\t\t\t\t\tOPTIONALLY ENCLOSED BY '\"'\n\t\t\t\t\t\t\tLINES TERMINATED BY '|'\n\t\t\t\t\t\t\tIGNORE 0 LINES";
Yii::app()->db->createCommand($importFileSql)->execute();
$newStations = Yii::app()->db->createCommand("SELECT * FROM transportStations WHERE id > " . $i . " ORDER BY id ASC ")->queryAll();
foreach ($newStations as $newStation) {
$stationId = $newStation['id'];
$sql2 = "INSERT INTO link_transportStations_to_transportTypes(transportStation,transportType,status) VALUES ('{$stationId}','1','1')";
Yii::app()->db->createCommand($sql2)->execute();
}
return true;
}
function load()
{
if ($this->isValid()) {
$sql = "SELECT b.*, c.`td` AS class, w.`title` AS ward, p.`title` AS parish \n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_blpu` b\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_blpu_class` c ON b.`blpu_class` = c.`blpu_id`\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_ons_ward` w ON b.`ward_code` = w.`w_id`\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_ons_parish` p ON b.`parish_code` = p.`p_id`\n\t\t\t\tWHERE `uprn`=?";
if ($rs = $this->mDb->query($sql, array($this->mUprn))) {
if ($this->mInfo = $rs->fields) {
if ($this->mInfo['local_custodian_code'] == 0) {
global $gBitSystem;
$gBitSystem->fatalError(tra('You do not have permission to access this UPRN record'), 'error.tpl', tra('Permission denied.'));
}
$os1 = new OSRef($this->mInfo['x_coordinate'], $this->mInfo['y_coordinate']);
$ll1 = $os1->toLatLng();
$this->mInfo['prop_lat'] = $ll1->lat;
$this->mInfo['prop_lng'] = $ll1->lng;
$this->mInfo['display_uprn'] = $this->getUprnEntryUrl($this->mInfo['uprn']);
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_blpu_provenance` p WHERE p.`uprn`=?";
$result = $this->mDb->query($sql, array($this->mUprn));
while ($res = $result->fetchRow()) {
$this->mInfo['provenance'][] = $res;
}
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_blpu_xref` x WHERE x.`uprn`=?";
$result = $this->mDb->query($sql, array($this->mUprn));
while ($res = $result->fetchRow()) {
$this->mInfo['xref'][] = $res;
}
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_lpi` lpi WHERE lpi.`uprn`=?";
$result = $this->mDb->query($sql, array($this->mUprn));
while ($res = $result->fetchRow()) {
$this->mInfo['lpi'][$res['language']] = $res;
}
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "citizen` ci WHERE ci.`nlpg`=? ORDER BY `surname`, `forename`";
$result = $this->mDb->query($sql, array($this->mUprn));
while ($res = $result->fetchRow()) {
$this->mInfo['ci'][] = $res;
}
$this->mInfo['display_usrn'] = $this->getUsrnEntryUrl($this->mInfo['lpi']['ENG']['usrn']);
} else {
global $gBitSystem;
$gBitSystem->fatalError(tra('USRN does not exist'), 'error.tpl', tra('Not found.'));
}
}
}
return count($this->mInfo);
}
function load()
{
if ($this->isValid()) {
$sql = "SELECT * \n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_street` s WHERE `usrn`=?";
if ($rs = $this->mDb->query($sql, array($this->mUsrn))) {
if ($this->mInfo = $rs->fields) {
if ($this->mInfo['swa_org_ref_naming'] == 0) {
global $gBitSystem;
$gBitSystem->fatalError(tra('You do not have permission to access this USRN record'), 'error.tpl', tra('Permission denied.'));
}
$os1 = new OSRef($this->mInfo['street_start_x'], $this->mInfo['street_start_y']);
$ll1 = $os1->toLatLng();
$this->mInfo['street_start_lat'] = $ll1->lat;
$this->mInfo['street_start_lng'] = $ll1->lng;
$os1->easting = $this->mInfo['street_end_x'];
$os1->northing = $this->mInfo['street_end_y'];
$ll1 = $os1->toLatLng();
$this->mInfo['street_end_lat'] = $ll1->lat;
$this->mInfo['street_end_lng'] = $ll1->lng;
$this->mInfo['display_usrn'] = $this->getUsrnEntryUrl($this->mInfo['usrn']);
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_street_xref` x WHERE `usrn`=?";
$result = $this->mDb->query($sql, array($this->mUsrn));
while ($res = $result->fetchRow()) {
$this->mInfo['xref'][] = $res;
}
$sql = "SELECT * \n\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_street_descriptor` s WHERE `usrn`=?";
$result = $this->mDb->query($sql, array($this->mUsrn));
while ($res = $result->fetchRow()) {
$this->mInfo['descriptor'][$res['language']] = $res;
}
} else {
global $gBitSystem;
$gBitSystem->fatalError(tra('USRN does not exist'), 'error.tpl', tra('Not found.'));
}
}
}
return count($this->mInfo);
}
$ll1 = $os1->toLatLng();
echo "Converted to Lat/Long: " . $ll1->toString();
?>
</p>
<p>
Using WGS84 (convert an OSGB grid reference to a latitude and longitude using the WGS84 datum):
<pre>$os1w = new OSRef(651409.903, 313177.270);
echo "OS Grid Reference: " . $os1w->toString() . " - " . $os1w->toSixFigureString() . "<br />";
$l1w = $os1w->toLatLng();
$l1w->OSGB36ToWGS84();
echo "Converted to Lat/Long: " . $ll1w->toString();</pre>
<?php
$os1w = new OSRef(651409.903, 313177.27);
echo "OS Grid Reference: " . $os1w->toString() . " - " . $os1w->toSixFigureString() . "<br />";
$ll1w = $os1w->toLatLng();
$ll1w->OSGB36ToWGS84();
echo "Converted to Lat/Long: " . $ll1w->toString();
?>
</p>
<h2>Convert Latitude/Longitude to OS Grid Reference</h2>
<p>
Note that the OSGB-Latitude/Longitude conversions use the OSGB36 datum by default. The
majority of applications use the WGS84 datum, for which the appropriate conversions
need to be added. See the examples below to see the difference between the two data.
</p>
/**
* getOsnList( &$pParamHash );
* Get county list
*/
function getOsnList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
if (empty($pParamHash['sort_mode'])) {
if (empty($_REQUEST["sort_mode"])) {
$pParamHash['sort_mode'] = 'title_asc';
} else {
$pParamHash['sort_mode'] = $_REQUEST['sort_mode'];
}
}
LibertyContent::prepGetList($pParamHash);
$findSql = '';
$selectSql = '';
$joinSql = '';
$whereSql = '';
$bindVars = array();
// array_push( $bindVars, $this->mContentTypeGuid );
// $this->getServicesSql( 'content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars );
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if ($pParamHash['list'] == 'street') {
if (is_array($find)) {
// you can use an array of pages
$findSql .= " AND `d.street_descriptor` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} else {
if (is_string($find)) {
// or a string
$findSql .= " AND UPPER( `d.street_descriptor` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
}
} else {
if ($pParamHash['list'] == 'postcode') {
if (is_array($find)) {
// you can use an array of pages
$whereSql .= " WHERE p.`add2` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} else {
if (is_string($find)) {
// or a string
$whereSql .= " WHERE UPPER( p.`add2` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
}
} else {
if ($pParamHash['list'] == 'local') {
if (is_string($find) and $find != '') {
// or a string
$whereSql .= " WHERE UPPER( l.`title` ) like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
} else {
if (isset($c_id) and is_string($c_id) and $c_id != '') {
$whereSql .= " WHERE UPPER( l.`c_id` ) = ? ";
$bindVars[] = strtoupper($c_id);
}
}
} else {
if ($pParamHash['list'] == 'ward') {
if (is_string($find) and $find != '') {
// or a string
$whereSql .= " WHERE UPPER( w.`title` ) like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
} else {
if (isset($l_id) and is_string($l_id) and $l_id != '') {
$whereSql .= " WHERE UPPER( w.`l_id` ) = ? ";
$bindVars[] = strtoupper($l_id);
}
}
} else {
if ($pParamHash['list'] == 'parish') {
if (is_string($find) and $find != '') {
// or a string
$whereSql .= " WHERE UPPER( p.`title` ) like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
} else {
if ($pParamHash['list'] == 'blpu_class') {
if (is_string($find) and $find != '') {
// or a string
$whereSql .= " WHERE ( UPPER( c.`pd` ) like ? OR UPPER( c.`sd` ) like ? OR UPPER( c.`td` ) like ? )";
$bindVars[] = '%' . strtoupper($find) . '%';
$bindVars[] = '%' . strtoupper($find) . '%';
$bindVars[] = '%' . strtoupper($find) . '%';
}
} else {
if (is_string($find) and $find != '') {
// or a string
$whereSql .= " WHERE UPPER( `title` )like ? ";
$bindVars[] = '%' . strtoupper($find) . '%';
}
}
}
}
}
}
}
/* } else if( @$this->verifyId( $pUserId ) ) {
// or a string
$whereSql .= " AND lc.`creator_user_id` = ? ";
$bindVars[] = array( $pUserId );
}
*/
if ($pParamHash['list'] == 'county') {
$query = "SELECT c.* {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_county` c\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_county` c {$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'local') {
$query = "SELECT l.*, c.title AS county {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_local_authority` l\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_ons_county` c ON l.c_id = c.c_id AND l.c_id > 0\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_local_authority` l {$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'ward') {
$query = "SELECT w.*, l.title AS local_authority, c.title AS county {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_ward` w\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "nlpg_ons_local_authority` l ON w.l_id = l.l_id\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_ons_county` c ON l.c_id = c.c_id AND l.c_id > 0\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_ward` w {$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'parish') {
$query = "SELECT p.*, l.title AS local_authority, c.title AS county {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_parish` p\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "nlpg_ons_local_authority` l ON p.l_id = l.l_id\n\t\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_ons_county` c ON l.c_id = c.c_id AND l.c_id > 0\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_ons_parish` p {$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'blpu_class') {
$query = "SELECT c.blpu_id, c.pd, c.sd, c.td AS title {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_blpu_class` c\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_blpu_class` c {$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'street') {
$query = "SELECT s.*, d.street_descriptor AS title, d.locality_name, d.town_name {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_street` s\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "nlpg_street_descriptor` d ON s.usrn = d.usrn AND d.language = 'ENG' {$findSql}\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_street` s\n\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "nlpg_street_descriptor` d ON s.usrn = d.usrn AND d.language = 'ENG' {$findSql}\n\t\t\t\t{$joinSql} {$whereSql}";
} else {
if ($pParamHash['list'] == 'postcode') {
$query = "SELECT p.postcode, p.add1, p.add2 AS title, p.add3, p.add4, p.town, p.county, p.grideast, p.gridnorth {$selectSql}\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_postcode` p\n\t\t\t\t{$joinSql} {$whereSql} ORDER BY " . $this->mDb->convertSortmode($sort_mode);
$query_cant = "SELECT COUNT( * )\n\t\t\t\tFROM `" . BIT_DB_PREFIX . "nlpg_postcode` p {$joinSql} {$whereSql}";
}
}
}
}
}
}
}
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
if (!empty($parse_split)) {
$res = array_merge($this->parseSplit($res), $res);
}
if ($pParamHash['list'] == 'street') {
$os1 = new OSRef($res['street_start_x'], $res['street_start_y']);
$ll1 = $os1->toLatLng();
$res['street_start_lat'] = $ll1->lat;
$res['street_start_lng'] = $ll1->lng;
$os1->easting = $res['street_end_x'];
$os1->northing = $res['street_end_y'];
$ll1 = $os1->toLatLng();
$res['street_end_lat'] = $ll1->lat;
$res['street_end_lng'] = $ll1->lng;
$res['display_usrn'] = $this->getUsrnEntryUrl($res['usrn']);
} else {
if ($pParamHash['list'] == 'postcode') {
$os1 = new OSRef($res['grideast'], $res['gridnorth']);
$ll1 = $os1->toLatLng();
$res['pc_lat'] = $ll1->lat;
$res['pc_lng'] = $ll1->lng;
}
}
$ret[] = $res;
}
$pParamHash["data"] = $ret;
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
LibertyContent::postGetList($pParamHash);
return $ret;
}
//if you want busgobbler's source, visit http://dev.aendrew.com/busgobbler.phps
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
if ($row > 0 && !empty($data[0])) {
//ignore header row
$csv[$row - 1]['row'] = $row;
$csv[$row - 1]['route'] = $data[0];
$csv[$row - 1]['run'] = $data[1];
$csv[$row - 1]['sequence'] = $data[2];
$csv[$row - 1]['stop_code_lbsl'] = $data[3];
$csv[$row - 1]['bus_stop_code'] = $data[4];
$csv[$row - 1]['naptan_atco'] = $data[5];
$csv[$row - 1]['stop_name'] = $data[6];
$csv[$row - 1]['location_easting'] = $data[7];
$csv[$row - 1]['location_northing'] = $data[8];
$csv[$row - 1]['heading'] = $data[9];
$csv[$row - 1]['virtual_bus_stop'] = $data[10];
$os1 = new OSRef($csv[$row - 1]['location_easting'], $csv[$row - 1]['location_northing']);
//here's where we use phpCoord from above. I hate eastings/northings...
$latlng = $os1->toLatLng();
$latlng->OSGB36ToWGS84();
$csv[$row - 1]['latitude'] = @$latlng->lat;
$csv[$row - 1]['longitude'] = @$latlng->lng;
}
$row++;
}
fclose($handle);
}
//print_r($routes);
scraperwiki::save_sqlite(array('row'), $csv, $table_name = "tfl_buses");
//scraperwiki::save_sqlite(array('route'), $routes, "tfl_routes");
//not bad for <44 lines, eh?
$pipedelimited = preg_replace('|<outlet id="(.+)" outlettypeid="(.+)" easting="(.+)" northing="(.+)"><outletname>(.+)</outletname><distance>.+</distance></outlet>|', '\\1|\\2|\\3|\\4|\\5', $xml);
// Split string into array of outlets, then each outlet into array of data
$areaarray = explode("\n", $pipedelimited);
// Loop through every outlet, convert lat/long and insert into database
$arearowcount = 0;
foreach ($areaarray as $outlet) {
$outlet = explode('|', $outlet);
$id = $outlet[0];
$outletType = $outlet[1];
$easting = $outlet[2];
$northing = $outlet[3];
$name = mysql_real_escape_string($outlet[4]);
$phone = null;
$openhours = null;
$address = '';
$os1 = new OSRef($easting, $northing);
$ll1 = $os1->toLatLng();
$ll1->OSGB36ToWGS84();
$latitude = $ll1->lat;
$longitude = $ll1->lng;
// Build an HTTP POST query to request XML data for a specific area+type combination
$query = http_build_query(array('theID' => "{$id}|Recycle"));
// Add request headers to the query
$contextData = array('method' => 'POST', 'header' => "Connection: close\r\nContent-Length: " . strlen($query) . "\r\n", 'content' => $query);
// Encapsulate HTTP query in context format for PHP
$context = stream_context_create(array('http' => $contextData));
// Actually send the HTTP request and get the data from the recyclescotland server, finally!
$html = file_get_contents('http://www.recycleforscotland.com/tools/singleOutletScript.asp', false, $context);
/* DEBUG HTML REGEX
$sql = "REPLACE INTO outlets_info_html VALUES ('$id', '".mysql_real_escape_string($html)."')";
mysql_query($sql);*/
/**
* loadCitizen( &$pParamHash );
* Get citizen record
*/
function loadCitizen(&$pParamHash = NULL)
{
if ($this->isValid()) {
$sql = "SELECT ci.*, a.*, n.*, p.*\n\t\t\tFROM `" . BIT_DB_PREFIX . "citizen` ci \n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "citizen_address` a ON a.usn = ci.usn\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_blpu` n ON n.`uprn` = ci.`nlpg`\n\t\t\tLEFT JOIN `" . BIT_DB_PREFIX . "nlpg_lpi` p ON p.`uprn` = ci.`nlpg` AND p.`language` = 'ENG' AND p.`logical_status` = 1\n\t\t\tWHERE ci.`content_id` = ?";
if ($rs = $this->mDb->query($sql, array($this->mContentId))) {
if ($this->mInfo = $rs->fields) {
/* if( $this->mInfo['local_custodian_code'] == 0 ) {
global $gBitSystem;
$gBitSystem->fatalError( tra( 'You do not have permission to access this client record' ), 'error.tpl', tra( 'Permission denied.' ) );
}
*/
$sql = "SELECT x.`last_update_date`, x.`source`, x.`cross_reference` \n\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "citizen_xref` x\n\t\t\t\t\t\t\tWHERE x.content_id = ?";
/* Link to legacy system
CASE
WHEN x.`source` = 'POSTFIELD' THEN (SELECT `USN` FROM `".BIT_DB_PREFIX."caller` c WHERE ci.`caller_id` = x.`cross_reference`)
ELSE '' END AS USN
*/
$result = $this->mDb->query($sql, array($this->mContentId));
while ($res = $result->fetchRow()) {
$this->mInfo['xref'][] = $res;
if ($res['source'] == 'POSTFIELD') {
$ticket[] = $res['cross_reference'];
}
}
if (isset($ticket)) {
$sql = "SELECT t.* FROM `" . BIT_DB_PREFIX . "task_ticket` t \n\t\t\t\t\t\t\tWHERE t.caller_id IN(" . implode(',', array_fill(0, count($ticket), '?')) . " )";
$result = $this->mDb->query($sql, $ticket);
while ($res = $result->fetchRow()) {
$this->mInfo['tickets'][] = $res;
}
}
$os1 = new OSRef($this->mInfo['x_coordinate'], $this->mInfo['y_coordinate']);
$ll1 = $os1->toLatLng();
$this->mInfo['prop_lat'] = $ll1->lat;
$this->mInfo['prop_lng'] = $ll1->lng;
// $this->mInfo['display_usrn'] = $this->getUsrnEntryUrl( $this->mInfo['usrn'] );
// $this->mInfo['display_uprn'] = $this->getUprnEntryUrl( $this->mInfo['uprn'] );
//vd($this->mInfo);
} else {
global $gBitSystem;
$gBitSystem->fatalError(tra('Client record does not exist'), 'error.tpl', tra('Not found.'));
}
}
}
return count($this->mInfo);
}
public function testFromSixFigureString2()
{
$OSRef = OSRef::fromSixFigureReference('HU396753');
$expected = "(439600, 1175300)";
self::assertEquals($expected, $OSRef->__toString());
}
/**
* Convert this LatLng object into an OSGB grid reference. Note that this
* function does not take into account the bounds of the OSGB grid -
* beyond the bounds of the OSGB grid, the resulting OSRef object has no
* meaning
*
* Reference values for transformation are taken from OS document
* "A Guide to Coordinate Systems in Great Britain"
*
* @return OSRef
*/
public function toOSRef()
{
$this->toOSGB36();
$OSGB = new OSRef(0, 0);
//dummy to get reference data
$scale = $OSGB->getScaleFactor();
$N0 = $OSGB->getOriginNorthing();
$E0 = $OSGB->getOriginEasting();
$phi0 = $OSGB->getOriginLatitude();
$lambda0 = $OSGB->getOriginLongitude();
$coords = $this->toTransverseMercatorEastingNorthing($scale, $E0, $N0, $phi0, $lambda0);
return new OSRef(round($coords['E']), round($coords['N']), $this->h);
}
/**
* Convert this LatLng object into an OSGB grid reference. Note that this
* function does not take into account the bounds of the OSGB grid -
* beyond the bounds of the OSGB grid, the resulting OSRef object has no
* meaning
*
* Reference values for transformation are taken from OS document
* "A Guide to Coordinate Systems in Great Britain"
*
* @return OSRef
*/
public function toOSRef()
{
if ($this->refEll != RefEll::Airy1830()) {
trigger_error('Current co-ordinates are in a non-OSGB datum', E_USER_WARNING);
}
$OSGB = new OSRef(0, 0);
//dummy to get reference data
$scale = $OSGB->getScaleFactor();
$N0 = $OSGB->getOriginNorthing();
$E0 = $OSGB->getOriginEasting();
$phi0 = $OSGB->getOriginLatitude();
$lambda0 = $OSGB->getOriginLongitude();
$coords = $this->toTransverseMercatorEastingNorthing($scale, $E0, $N0, $phi0, $lambda0);
return new OSRef($coords['E'], $coords['N']);
}
* Location name: specify as a string. String is passed directly to Nominatim, e.g. ?search=Ladybower+inn
* Scope: scope can optionally be limited to Sheffield (1), or the general area for walks (2). e.g. ?search=Ladybower+inn&scope=2 TODO: Use constants on some appropriate class
* This is only a preference, nominatim will return results outside this area if necessary.
* Searches are always limited to the UK.
* Location name search is done here for now - may be moved elsewhere when appropriate
*/
header("Content-type: application/json");
$scopeSheffield = 1;
$scopeGeneralArea = 2;
if (!empty($_GET['lat']) && !empty($_GET['lon']) || !empty($_GET['east']) && !empty($_GET['north']) || !empty($_GET['gridref'])) {
$wp = new Waypoint();
if (!empty($_GET['lat']) && !empty($_GET['lon'])) {
$wp->latLng = new LatLng((double) $_GET['lat'], (double) $_GET['lon']);
} else {
if (!empty($_GET['east']) && !empty($_GET['north'])) {
$osRef = new OSRef((int) $_GET['east'], (int) $_GET['north']);
$latLng = $osRef->toLatLng();
$latLng->OSGB36ToWGS84();
$wp->latLng = $latLng;
} else {
if (!empty($_GET['gridref'])) {
$osRef = getOSRefFromSixFigureReference($_GET['gridref']);
$latLng = $osRef->toLatLng();
$latLng->OSGB36ToWGS84();
$wp->latLng = $latLng;
}
}
}
// Now do a reverse geocode & return the result
$placeName = $wp->reverseGeocode();
echo json_encode($placeName);
public function testFromSixFigureString()
{
$OSRef = OSRef::getOSRefFromSixFigureReference('TQ301842');
$expected = "(530100, 184200)";
self::assertEquals($expected, $OSRef->__toString());
}
function load_applications($application_results)
{
$applications = array();
if (sizeof($application_results) > 0) {
for ($i = 0; $i < sizeof($application_results); $i++) {
$application = new application();
$application->council_reference = $application_results[$i][0];
$application->address = $application_results[$i][1];
$application->postcode = $application_results[$i][2];
$application->description = $application_results[$i][3];
$application->info_url = $application_results[$i][4];
$application->comment_url = $application_results[$i][5];
$application->map_url = $application_results[$i][6];
$application->x = $application_results[$i][7];
$application->y = $application_results[$i][8];
$application->date_received = $application_results[$i][9];
$application->date_scraped = $application_results[$i][10];
$application->authority_name = $application_results[$i][11];
$os = new OSRef($application->x, $application->y);
$latlng = $os->toLatLng();
$application->lat = $latlng->lat;
$application->lon = $latlng->lng;
array_push($applications, $application);
}
}
return $applications;
}
