Places
The places module is intended for use with Googles Places API. The places table allows a limited amount of information to be cached locally by your service to improve performance. You should ensure that your specific usage is allowed under Googles Terms and Conditions.
Generally, the indended usage is that a user provides a free form 'input' that is stored in places table. Using Google's Place Autocomplete request, this input is used to retrive one or more place suggestions, each of which contains a 'description', 'main_text', and 'secondary_text', which are stored in the 'places_suggestions' table.
https://maps.googleapis.com/maps/api/place/autocomplete/json?input=100+Queen+Street,+Brisbane&key=<API KEY>
If only one suggestion is returned, the system will automatically perform a Google Places Details request, which will return further information which will be stored in the 'places' table; otherwise, a person will need to manually select which suggestion should be used for the address. Once a suggestion has been confirmed, its details will be retrieved and stored in the 'places' table.
Note, it is envisioned that use of this module will also involve use of the front-end Google Places Javascript API, which will reduce the need for such manual intervention.
https://maps.googleapis.com/maps/api/place/details/json?placeid=EjtCcmlzYmFuZSwgNzMgRGVubmlzIFJvYWQsIFNwcmluZ3dvb2QsIFF1ZWVuc2xhbmQsIEF1c3RyYWxpYQ&key=<API KEY>
Lifecycle 1
Front-end invokes call to 'Base Places Autocomplete' procedure, which creates a new tuple in 'base_places' table storing 'input'.
Base_Places_Autocomplete( <sid>, <input> )
Back-end process calls 'Base Places Unprocessed' procedure, which returns tuples in 'base_places' table that have no associated tuples in the 'base_places_suggestions' table; then for each such place calls Google Places Autocomplete request.
Base_Places_Unprocessed( 'LOCAL' )
For each suggestion returned, the back-end process will store the suggestion in the 'base_places_suggestions' table.
Base_Places_Suggestions_Replace( 'LOCAL', 0, <PLACE ID>, <place id>, <description>, <main text>, <secondary text> );
If only one suggestion is returned, the back-end process will also set the 'CONFIRMED_PLACE_SUGGESTION_ID' field of the 'base_places' table.
Base_Places_Save( 'LOCAL', <PLACE ID>, 'CONFIRMATION_PLACE_SUGGESTION_ID', <CONFIRMATION_PLACE_SUGGESTION_ID> );
If manual intervention is required, all unconfirmed place suggestions can be retrieved - optionally only suggestions related to a specific place may be retrieved:
Base_Places_Suggestions_Unconfirmed( <sid>, [PLACE ID] )
A suggestion can be associated with place using the 'Base Places Save' procedure.
Base_Places_Save( 'LOCAL', <PLACE ID>, 'CONFIRMATION_PLACE_SUGGESTION_ID', <CONFIRMATION_PLACE_SUGGESTION_ID> );
A back-end process will then retrieve all places that have had a suggestion confirmed, but are still incomplete:
Base_Places_Incomplete( 'LOCAL' )
For each of these tuples, the back-end process will call the Google Places Details API passing the google place id, and will use the return result to complete its tuple in the 'base_places' table.
Base_Places_Update( 'LOCAL', <PLACE ID>, <street number>, <street>, <suburb>, <city>, <state>, <country>, <postal_code>, <latitude>, <longitude> );
Places exported procedures
Base_Places
DROP PROCEDURE Base_Places;
DELIMITER //
CREATE PROCEDURE Base_Places
(
$Sid TEXT,
$apikey TEXT,
$PLACE_ID TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
CALL _Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT @USER THEN
CALL Notice( 'Base_Places: INVALID_AUTHORISATION' );
ELSEIF $PLACE_ID THEN
SELECT
base_places.*,
IFNULL( description, '' ) AS description,
IF(0=place_confirmed, 'warning', '' ) AS css_class,
IF(0=place_confirmed, '×', '✓' ) AS confirmed
FROM base_places
LEFT JOIN
(
SELECT
PLACE_SUGGESTION_ID AS CONFIRMED_PLACE_SUGGESTION_ID,
description
FROM base_places_suggestions
) AS S1 USING (CONFIRMED_PLACE_SUGGESTION_ID)
WHERE PLACE_ID=$PLACE_ID;
ELSE
SELECT
base_places.*,
IFNULL( nr_suggestions, 0 ) AS nr_suggestions,
IFNULL( description, '' ) AS description,
IF(0=place_confirmed, 'warning', '' ) AS css_class,
IF(0=place_confirmed, '×', '✓' ) AS confirmed
FROM base_places
LEFT JOIN
(
SELECT PLACE_ID, COUNT(*) AS nr_suggestions
FROM base_places_suggestions
GROUP BY PLACE_ID
) AS S0 USING (PLACE_ID)
LEFT JOIN
(
SELECT
PLACE_SUGGESTION_ID AS CONFIRMED_PLACE_SUGGESTION_ID,
description
FROM base_places_suggestions
) AS S1 USING (CONFIRMED_PLACE_SUGGESTION_ID)
ORDER BY suburb, PLACE_ID;
END IF;
END
//
DELIMITER ;
Base_Places_Autocomplete
DROP PROCEDURE Base_Places_Autocomplete;
DELIMITER //
CREATE PROCEDURE Base_Places_Autocomplete
(
$Sid TEXT,
$apikey TEXT,
$input TEXT,
$google_place_id TEXT,
$floor TEXT,
$street_number TEXT,
$street TEXT,
$suburb TEXT,
$city TEXT,
$state TEXT,
$country TEXT,
$postal_code TEXT,
$latitude TEXT,
$longitude TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $PLACE_ID INT DEFAULT 0;
CALL Base_Places_Autocomplete_Inout
(
$Sid,
$apikey,
$input,
$google_place_id,
$floor,
$street_number,
$street,
$suburb,
$city,
$state,
$country,
$postal_code,
$latitude,
$longitude,
$PLACE_ID
);
CALL Base_Places( $Sid, $apikey, $PLACE_ID );
END
//
DELIMITER ;
Other Procedures
Base_Places_Unprocessed
DROP PROCEDURE Base_Places_Unprocessed;
DELIMITER //
CREATE PROCEDURE Base_Places_Unprocessed
(
$Sid CHAR(64),
$apikey CHAR(64)
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
CALL _Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT "ADMIN" = @idtype AND NOT( "LOCAL" = $Sid AND IsLocalCaller() ) THEN
CALL Notice( 'Base_Places_Unprocessed: INVALID_AUTHORISATION' );
ELSE
SELECT *
FROM base_places
WHERE 0=place_processed;
END IF;
END
//
DELIMITER ;
Base_Places_Save
DROP PROCEDURE Base_Places_Save;
DELIMITER //
CREATE PROCEDURE Base_Places_Save
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_ID INT,
$name TEXT,
$value TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT( "ADMIN" = @idtype OR "CLIENT" = @idtype OR "LOCAL" = $Sid AND IsLocalCaller() ) THEN
CALL Notice( 'Base_Places_Save: INVALID_AUTHORISATION' );
ELSE
CASE $name
WHEN "CONFIRMED_PLACE_SUGGESTION_ID" THEN UPDATE base_places SET place_confirmed = NOW(), CONFIRMED_PLACE_SUGGESTION_ID = $value WHERE PLACE_ID=$PLACE_ID;
WHEN "place_processed" THEN UPDATE base_places SET place_processed = NOW() WHERE PLACE_ID=$PLACE_ID;
WHEN "place_error" THEN UPDATE base_places SET place_error = $value WHERE PLACE_ID=$PLACE_ID;
WHEN "route_shortest_distance_metres" THEN UPDATE base_places SET route_shortest_distance_metres = $value WHERE PLACE_ID=$PLACE_ID;
WHEN "route_shortest_duration_seconds" THEN UPDATE base_places SET route_shortest_duration_seconds = $value WHERE PLACE_ID=$PLACE_ID;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CASE IN Base_Places_Save';
END CASE;
END IF;
END
//
DELIMITER ;
Base_Places_Override
DROP PROCEDURE Base_Places_Override;
DELIMITER //
CREATE PROCEDURE Base_Places_Override
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_ID INT,
$OVERRIDE_PLACE_ID INT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $GROUP_ID INT DEFAULT 0;
DECLARE $input TEXT DEFAULT '';
DECLARE $suggested_google_place_id TEXT DEFAULT '';
DECLARE $CONFIRMED_PLACE_SUGGESTION_ID INT DEFAULT 0;
DECLARE $floor TEXT DEFAULT '';
DECLARE $street_number TEXT DEFAULT '';
DECLARE $street TEXT DEFAULT '';
DECLARE $suburb TEXT DEFAULT '';
DECLARE $city TEXT DEFAULT '';
DECLARE $state TEXT DEFAULT '';
DECLARE $country TEXT DEFAULT '';
DECLARE $postal_code TEXT DEFAULT '';
DECLARE $latitude FLOAT DEFAULT 0;
DECLARE $longitude FLOAT DEFAULT 0;
DECLARE $place_created DATETIME DEFAULT 0;
DECLARE $place_processed DATETIME DEFAULT 0;
DECLARE $place_confirmed DATETIME DEFAULT 0;
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF NOT( @USER OR ("LOCAL" = $Sid AND IsLocalCaller() ) ) THEN
CALL Notice( 'Base_Places_Override: INVALID_AUTHORISATION' );
ELSE
SELECT
GROUP_ID,
input,
suggested_google_place_id,
CONFIRMED_PLACE_SUGGESTION_ID,
floor,
street_number,
street,
suburb,
city,
state,
country,
postal_code,
latitude,
longitude,
place_created,
place_processed,
place_confirmed
INTO
$GROUP_ID,
$input,
$suggested_google_place_id,
$CONFIRMED_PLACE_SUGGESTION_ID,
$floor,
$street_number,
$street,
$suburb,
$city,
$state,
$country,
$postal_code,
$latitude,
$longitude,
$place_created,
$place_processed,
$place_confirmed
FROM base_places
WHERE PLACE_ID = $OVERRIDE_PLACE_ID;
UPDATE base_places
SET
GROUP_ID = $GROUP_ID,
input = $input,
suggested_google_place_id = $suggested_google_place_id,
CONFIRMED_PLACE_SUGGESTION_ID = $CONFIRMED_PLACE_SUGGESTION_ID,
floor = $floor,
street_number = $street_number,
street = $street,
suburb = $suburb,
city = $city,
state = $state,
country = $country,
postal_code = $postal_code,
latitude = $latitude,
longitude = $longitude,
place_created = $place_created,
place_processed = $place_processed,
place_confirmed = $place_confirmed
WHERE PLACE_ID = $PLACE_ID;
IF $PLACE_ID != $OVERRIDE_PLACE_ID THEN
DELETE FROM base_places_suggestions WHERE PLACE_ID=$PLACE_ID;
END IF;
END IF;
END
//
DELIMITER ;
Base_Places_Update
DROP PROCEDURE Base_Places_Update;
DELIMITER //
CREATE PROCEDURE Base_Places_Update
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_ID INT,
$google_place_id TEXT,
$floor CHAR(10),
$street_number CHAR(10),
$street CHAR(99),
$suburb CHAR(99),
$city CHAR(99),
$state CHAR(99),
$country CHAR(99),
$postal_code CHAR(10),
$latitude TEXT,
$longitude TEXT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $CONFIRMED_PLACE_SUGGESTION_ID INT DEFAULT 0;
DECLARE $place_geocoded DATETIME DEFAULT 0;
DECLARE $place_confirmed DATETIME DEFAULT 0;
SELECT PLACE_SUGGESTION_ID
INTO $CONFIRMED_PLACE_SUGGESTION_ID
FROM base_places_suggestions
WHERE PLACE_ID = $PLACE_ID
AND google_place_id = $google_place_id
ORDER BY PLACE_SUGGESTION_ID DESC
LIMIT 1;
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT "ADMIN" = @idtype THEN
CALL Notice( 'Base_Places_Update: INVALID_AUTHORISATION' );
ELSEIF NOT $PLACE_ID OR "" = $google_place_id THEN
CALL Warning( 'Base_Places_Update: INVALID_PARAMETERS' );
ELSE
IF "" != $street AND "" != $suburb AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_geocoded = NOW();
END IF;
IF "" != $street_number AND "" != $street AND "" != $suburb AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_confirmed = NOW();
END IF;
UPDATE base_places
SET
suggested_google_place_id = $google_place_id,
CONFIRMED_PLACE_SUGGESTION_ID = $CONFIRMED_PLACE_SUGGESTION_ID,
floor = $floor,
street_number = $street_number,
street = $street,
suburb = $suburb,
city = $city,
state = $state,
country = $country,
postal_code = $postal_code,
latitude = $latitude,
longitude = $longitude,
place_processed = NOW(),
place_geocoded = $place_geocoded,
place_confirmed = $place_confirmed
WHERE PLACE_ID=$PLACE_ID;
END IF;
END
//
DELIMITER ;
Base Places Suggestions
DROP PROCEDURE Base_Places_Suggestions;
DELIMITER //
CREATE PROCEDURE Base_Places_Suggestions
(
$Sid CHAR(64),
$PLACE_ID INT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
DECLARE $confirmed BOOL DEFAULT 0;
CALL _Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF NOT @USER THEN
CALL Notice( ': INVALID_AUTHORISATION' );
ELSE
SET $confirmed
=
EXISTS
(
SELECT *
FROM base_places
WHERE PLACE_ID = $PLACE_ID
AND NOT CONFIRMED_PLACE_SUGGESTION_ID = 0
);
SELECT
*,
IF( $confirmed, 'disabled', '' ) AS disabled
FROM base_places_suggestions
WHERE PLACE_ID = $PLACE_ID
ORDER BY description;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Suggestions_Replace;
DELIMITER //
CREATE PROCEDURE Base_Places_Suggestions_Replace
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_SUGGESTION_ID INT,
$PLACE_ID INT,
$google_place_id TEXT,
$description TEXT,
$main_text TEXT,
$secondary_text TEXT,
$types TEXT,
$suggestion_type TEXT,
$suggestion_floor TEXT,
$suggestion_street_number TEXT,
$suggestion_street TEXT,
$suggestion_suburb TEXT,
$suggestion_city TEXT,
$suggestion_state TEXT,
$suggestion_country TEXT,
$suggestion_postal_code TEXT,
$suggestion_latitude FLOAT,
$suggestion_longitude FLOAT
)
SQL SECURITY INVOKER
COMMENT 'EXPORTABLE'
BEGIN
CALL _Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
CALL Error( 'READ_ONLY' );
ELSEIF NOT @USER THEN
CALL Notice( 'Base_Places_Suggestions_Replace: INVALID_AUTHORISATION' );
ELSE
IF NOT EXISTS( SELECT * FROM base_places_suggestions WHERE PLACE_ID=$PLACE_ID AND google_place_id=$google_place_id ) THEN
REPLACE INTO base_places_suggestions
( PLACE_SUGGESTION_ID, PLACE_ID, google_place_id, description, main_text, secondary_text, types, suggestion_created )
VALUES
( 0, $PLACE_ID, $google_place_id, $description, $main_text, $secondary_text, $types, NOW() );
SET $PLACE_SUGGESTION_ID = LAST_INSERT_ID();
UPDATE base_places_suggestions
SET
suggestion_type = $suggestion_type,
suggestion_floor = $suggestion_floor,
suggestion_street_number = $suggestion_street_number,
suggestion_street = $suggestion_street,
suggestion_suburb = $suggestion_suburb,
suggestion_city = $suggestion_city,
suggestion_state = $suggestion_state,
suggestion_country = $suggestion_country,
suggestion_postal_code = $suggestion_postal_code
WHERE PLACE_SUGGESTION_ID = $PLACE_SUGGESTION_ID;
SELECT * FROM base_places_suggestions WHERE PLACE_SUGGESTION_ID=$PLACE_SUGGESTION_ID;
END IF;
END IF;
END
//
DELIMITER ;
Places internal procedures
DROP PROCEDURE Base_Places_Create_Inout;
DELIMITER //
CREATE PROCEDURE Base_Places_Create_Inout
(
$Sid CHAR(64),
$apikey CHAR(64),
$input TEXT,
$google_place_id TEXT,
OUT $PLACE_ID INT
)
SQL SECURITY INVOKER
BEGIN
SET $PLACE_ID = 0;
IF NOT "" = $google_place_id THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE NOT 0=place_confirmed AND input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE NOT 0=place_confirmed AND input = $input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE input=$input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
#
# Only create new place if could not retrieve place id!
# The above clauses must also match those in the Base_Places_Autocomplete_Inout
#
IF NOT $PLACE_ID THEN
CALL Base_Places_Autocomplete_Inout
(
$Sid,
$apikey,
$input,
$google_place_id, # google_place_id
'', # floor
'', # street_number
'', # street
'', # suburb
'', # city
'', # state
'', # country
'', # postal_code
'', # latitude
'', # longitude
$PLACE_ID );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Places_Create_Inout;
DELIMITER //
CREATE PROCEDURE _Base_Places_Create_Inout
(
$GROUP_ID INT,
$input TEXT,
$google_place_id TEXT,
OUT $PLACE_ID INT
)
SQL SECURITY INVOKER
BEGIN
SET $PLACE_ID = 0;
IF NOT "" = $google_place_id THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND NOT 0=place_confirmed AND input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND NOT 0=place_confirmed AND input = $input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND input=$input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
#
# Only create new place if could not retrieve place id!
# The above clauses must also match those in the Base_Places_Autocomplete_Inout
#
IF NOT $PLACE_ID THEN
CALL _Base_Places_Autocomplete_Inout
(
$GROUP_ID,
$input,
$google_place_id, # google_place_id
'', # floor
'', # street_number
'', # street
'', # suburb
'', # city
'', # state
'', # country
'', # postal_code
'', # latitude
'', # longitude
$PLACE_ID # PLACE_ID INOUT
);
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Autocomplete_Inout;
DELIMITER //
CREATE PROCEDURE Base_Places_Autocomplete_Inout
(
$Sid CHAR(64),
$apikey CHAR(64),
$input TEXT,
$google_place_id TEXT,
$floor CHAR(10),
$street_number CHAR(10),
$street CHAR(99),
$suburb CHAR(99),
$city CHAR(99),
$state CHAR(99),
$country CHAR(99),
$postal_code CHAR(10),
$latitude TEXT,
$longitude TEXT,
OUT $PLACE_ID INT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $GROUP_ID INT DEFAULT 0;
DECLARE $place_processed DATETIME DEFAULT 0;
DECLARE $place_geocoded DATETIME DEFAULT 0;
DECLARE $place_confirmed DATETIME DEFAULT 0;
DECLARE $lat DECIMAL(10,7) DEFAULT 0;
DECLARE $lng DECIMAL(10,7) DEFAULT 0;
SET $PLACE_ID = 0;
SET $lat = $latitude;
SET $lng = $longitude;
IF NOT "" = $google_place_id THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE NOT 0=place_confirmed AND input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE NOT 0=place_confirmed AND input = $input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT @USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Autocomplete';
ELSE
SELECT GROUP_ID INTO $GROUP_ID FROM base_groups_members WHERE USER=@USER;
IF NOT $PLACE_ID THEN
REPLACE INTO base_places ( PLACE_ID, place_created ) VALUES ( 0, NOW() );
SET $PLACE_ID = LAST_INSERT_ID();
IF "" != $street_number AND "" != $street AND "" != $suburb AND "" != $city AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_processed = NOW();
SET $place_confirmed = NOW();
END IF;
IF "" != $street AND "" != $suburb AND "" != $city AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_geocoded = NOW();
END IF;
UPDATE base_places
SET
GROUP_ID = $GROUP_ID,
input = $input,
suggested_google_place_id = $google_place_id,
floor = $floor,
street_number = $street_number,
street = $street,
suburb = $suburb,
city = $city,
state = $state,
country = $country,
postal_code = $postal_code,
latitude = $latitude,
longitude = $longitude,
place_processed = $place_processed,
place_geocoded = $place_geocoded,
place_confirmed = $place_confirmed
WHERE PLACE_ID=$PLACE_ID;
ELSE
UPDATE base_places
SET
street_number = $street_number,
latitude = $latitude,
longitude = $longitude
WHERE PLACE_ID=$PLACE_ID;
END IF;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE _Base_Places_Autocomplete_Inout;
DELIMITER //
CREATE PROCEDURE _Base_Places_Autocomplete_Inout
(
$GROUP_ID INT,
$input TEXT,
$google_place_id TEXT,
$floor CHAR(10),
$street_number CHAR(10),
$street CHAR(99),
$suburb CHAR(99),
$city CHAR(99),
$state CHAR(99),
$country CHAR(99),
$postal_code CHAR(10),
$latitude TEXT,
$longitude TEXT,
OUT $PLACE_ID INT
)
SQL SECURITY INVOKER
BEGIN
DECLARE $place_processed DATETIME DEFAULT 0;
DECLARE $place_geocoded DATETIME DEFAULT 0;
DECLARE $place_confirmed DATETIME DEFAULT 0;
DECLARE $lat DECIMAL(10,7) DEFAULT 0;
DECLARE $lng DECIMAL(10,7) DEFAULT 0;
SET $PLACE_ID = 0;
SET $lat = $latitude;
SET $lng = $longitude;
IF NOT "" = $google_place_id THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND NOT 0=place_confirmed AND input=$input AND suggested_google_place_id = $google_place_id ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF NOT $PLACE_ID THEN
SELECT PLACE_ID INTO $PLACE_ID FROM base_places WHERE GROUP_ID=$GROUP_ID AND NOT 0=place_confirmed AND input = $input ORDER BY PLACE_ID DESC LIMIT 1;
END IF;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT $GROUP_ID THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '_Base_Places_Autocomplete_Inout: INVALID_PARAMATERS(GROUP_ID)';
ELSEIF '' = TRIM($input) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '_Base_Places_Autocomplete_Inout: INVALID_PARAMATERS(input)';
ELSE
IF NOT $PLACE_ID THEN
REPLACE INTO base_places ( GROUP_ID, PLACE_ID, place_created ) VALUES ( $GROUP_ID, 0, NOW() );
SET $PLACE_ID = LAST_INSERT_ID();
IF "" != $street_number AND "" != $street AND "" != $suburb AND "" != $city AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_processed = NOW();
SET $place_confirmed = NOW();
END IF;
IF "" != $street AND "" != $suburb AND "" != $city AND "" != $state AND "" != $country AND "" != $postal_code AND "" != $latitude AND "" != $longitude THEN
SET $place_geocoded = NOW();
END IF;
UPDATE base_places
SET
input = $input,
suggested_google_place_id = $google_place_id,
floor = $floor,
street_number = $street_number,
street = $street,
suburb = $suburb,
city = $city,
state = $state,
country = $country,
postal_code = $postal_code,
latitude = $latitude,
longitude = $longitude,
place_processed = $place_processed,
place_geocoded = $place_geocoded,
place_confirmed = $place_confirmed
WHERE PLACE_ID=$PLACE_ID;
ELSE
UPDATE base_places
SET
street_number = $street_number,
latitude = $latitude,
longitude = $longitude
WHERE PLACE_ID=$PLACE_ID;
END IF;
END IF;
END
//
DELIMITER ;
Base_Places_Incomplete
DROP PROCEDURE Base_Places_Incomplete;
DELIMITER //
CREATE PROCEDURE Base_Places_Incomplete
(
$Sid CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( "LOCAL" = $Sid AND IsLocalCaller() ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Incomplete';
ELSE
SELECT *
FROM base_places
WHERE NOT 0=CONFIRMATION_PLACE_SUGGESTION_ID
AND "" = postal_code;
END IF;
END
//
DELIMITER ;
Base_Places_Manually_Matched
DROP PROCEDURE Base_Places_Manually_Matched;
DELIMITER //
CREATE PROCEDURE Base_Places_Manually_Matched
(
$Sid CHAR(64),
$apikey CHAR(64)
)
SQL SECURITY INVOKER
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF NOT "ADMIN" = @idtype AND NOT( "LOCAL" = $Sid AND IsLocalCaller() ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Manually_Matched';
ELSE
SELECT
PLACE_ID,
CONFIRMED_PLACE_SUGGESTION_ID,
street,
suggested_google_place_id,
google_place_id
FROM base_places
LEFT JOIN
(
SELECT *, PLACE_SUGGESTION_ID AS CONFIRMED_PLACE_SUGGESTION_ID
FROM base_places_suggestions
) AS S0 USING (PLACE_ID, CONFIRMED_PLACE_SUGGESTION_ID)
WHERE NOT CONFIRMED_PLACE_SUGGESTION_ID = 0
AND '' = street
AND NOT ISNULL( google_place_id );
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Suggestions_Unconfirmed;
DELIMITER //
CREATE PROCEDURE Base_Places_Suggestions_Unconfirmed
(
$Sid CHAR(64),
$PLACE_SUGGESTION_ID INT
)
SQL SECURITY INVOKER
BEGIN
CALL Base_Users_Authorise_Sessionid( $Sid, @email, @USER, @idtype );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( "ADMIN" = @idtype ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Suggestions_Unconfirmed';
ELSE
SELECT *
FROM base_places_suggestions
LEFT JOIN base_places USING (PLACE_ID)
WHERE 0 = CONFIRMATION_PLACE_SUGGESTION_ID
ORDER BY PLACE_ID, CONFIRMATION_PLACE_SUGGESTION_ID;
END IF;
END
//
DELIMITER ;
Places Routes
DROP PROCEDURE Base_Places_Routes;
DELIMITER //
CREATE PROCEDURE Base_Places_Routes
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_OGN_ID INT,
$PLACE_DST_ID INT,
$filter TEXT,
$limit INT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
SET @offset = 0;
CALL CheckLimitOffset( $limit, @offset );
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF NOT @USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Routes';
ELSEIF $PLACE_OGN_ID AND $PLACE_DST_ID THEN
IF EXISTS( SELECT * FROM base_places_routes WHERE PLACE_OGN_ID = $PLACE_OGN_ID AND PLACE_DST_ID = $PLACE_DST_ID AND NOT route_processed = 0 ) THEN
SELECT * FROM base_places_routes WHERE PLACE_OGN_ID = $PLACE_OGN_ID AND PLACE_DST_ID = $PLACE_DST_ID AND NOT route_processed = 0 ORDER BY ROUTE_ID LIMIT 1;
ELSEIF EXISTS( SELECT * FROM base_places_routes WHERE PLACE_OGN_ID = $PLACE_DST_ID AND PLACE_DST_ID = $PLACE_OGN_ID AND NOT route_processed = 0 ) THEN
BEGIN
DECLARE $route_distance_metres INT DEFAULT 0;
DECLARE $route_duration_seconds INT DEFAULT 0;
SELECT
route_distance_metres, route_distance_seconds
INTO
$route_distance_metres, $route_duration_seconds
FROM base_places_routes
WHERE PLACE_OGN_ID = $PLACE_DST_ID AND PLACE_DST_ID = $PLACE_OGN_ID AND NOT route_processed = 0
ORDER BY ROUTE_ID LIMIT 1;
CALL Base_Places_Routes_Create
(
$Sid,
$apikey,
$PLACE_OGN_ID,
$PLACE_DST_ID,
$route_distance_metres,
$route_duration_seconds
);
END;
SELECT * FROM base_places_routes WHERE PLACE_OGN_ID = $PLACE_OGN_ID AND PLACE_DST_ID = $PLACE_DST_ID AND NOT route_processed = 0 ORDER BY ROUTE_ID LIMIT 1;
END IF;
ELSEIF $filter = "sans" THEN
SELECT *
FROM base_places_routes
LEFT JOIN
(
SELECT
PLACE_ID AS PLACE_OGN_ID,
latitude AS place_ogn_lat,
longitude AS place_ogn_lng
FROM base_places
) AS S1 USING (PLACE_OGN_ID)
LEFT JOIN
(
SELECT
PLACE_ID AS PLACE_DST_ID,
latitude AS place_dst_lat,
longitude AS place_dst_lng
FROM base_places
) AS S2 USING (PLACE_DST_ID)
WHERE 0 = route_shortest_distance_metres
ORDER BY ROUTE_ID
LIMIT $limit;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Routes_Unprocessed;
DELIMITER //
CREATE PROCEDURE Base_Places_Routes_Unprocessed
(
$Sid CHAR(64),
$apikey CHAR(64)
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT "ADMIN" = @idtype AND NOT( "LOCAL" = $Sid AND IsLocalCaller() ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Routes_Unprocessed';
ELSE
SELECT ROUTE_ID, PLACE_OGN_ID, PLACE_DST_ID, origin_place_id, destination_place_id
FROM
(
SELECT *
FROM base_places_routes
WHERE (0 = route_distance_metres OR 0 = route_duration_seconds)
AND PLACE_OGN_ID != PLACE_DST_ID
) AS S0
LEFT JOIN
(
SELECT PLACE_ID AS PLACE_OGN_ID, suggested_google_place_id AS origin_place_id
FROM base_places
WHERE NOT 0 = place_geocoded
) AS S1 USING (PLACE_OGN_ID)
LEFT JOIN
(
SELECT PLACE_ID AS PLACE_DST_ID, suggested_google_place_id AS destination_place_id
FROM base_places
WHERE NOT 0 = place_geocoded
) AS S2 USING (PLACE_DST_ID)
WHERE NOT ISNULL(origin_place_id) AND NOT ISNULL(destination_place_id) AND route_processed = 0;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Routes_Create;
DELIMITER //
CREATE PROCEDURE Base_Places_Routes_Create
(
$Sid CHAR(64),
$apikey CHAR(64),
$PLACE_OGN_ID INT,
$PLACE_DST_ID INT,
$route_distance_metres INT,
$route_duration_seconds INT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
DECLARE $ROUTE1_ID INT DEFAULT 0;
DECLARE $ROUTE2_ID INT DEFAULT 0;
DECLARE $GROUP1_ID INT DEFAULT 0;
DECLARE $GROUP2_ID INT DEFAULT 0;
DECLARE $route_processed DATETIME DEFAULT 0;
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
SELECT GROUP_ID INTO $GROUP1_ID FROM base_places WHERE PLACE_ID=$PLACE_OGN_ID;
SELECT GROUP_ID INTO $GROUP2_ID FROM base_places WHERE PLACE_ID=$PLACE_DST_ID;
SET $route_processed = NOW();
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT @USER THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Routes_Create';
#ELSEIF NOT( $GROUP1_ID AND $GROUP2_ID AND $GROUP1_ID = $GROUP2_ID ) THEN
#
# SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_PARAMETERS IN Base_Places_Routes_Create';
#
#ELSEIF NOT Base_Groups_Members_Contains( $GROUP1_ID, @USER ) THEN
#
# SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Routes_Create';
#
ELSE
SELECT ROUTE_ID
INTO $ROUTE1_ID
FROM base_places_routes
WHERE PLACE_OGN_ID = $PLACE_OGN_ID
AND PLACE_DST_ID = $PLACE_DST_ID
ORDER BY route_processed DESC
LIMIT 1;
SELECT ROUTE_ID
INTO $ROUTE2_ID
FROM base_places_routes
WHERE PLACE_OGN_ID = $PLACE_DST_ID
AND PLACE_DST_ID = $PLACE_OGN_ID
ORDER BY route_processed DESC
LIMIT 1;
IF NOT $ROUTE1_ID THEN
REPLACE INTO base_places_routes
( ROUTE_ID, GROUP_ID, PLACE_OGN_ID, PLACE_DST_ID, route_created, route_processed, route_distance_metres, route_duration_seconds )
VALUES
( 0, $GROUP1_ID, $PLACE_OGN_ID, $PLACE_DST_ID, NOW(), $route_processed, $route_distance_metres, $route_duration_seconds );
SET $ROUTE1_ID = LAST_INSERT_ID();
END IF;
IF NOT $ROUTE2_ID THEN
REPLACE INTO base_places_routes
( ROUTE_ID, GROUP_ID, PLACE_OGN_ID, PLACE_DST_ID, route_created, route_processed, route_distance_metres, route_duration_seconds )
VALUES
( 0, $GROUP1_ID, $PLACE_DST_ID, $PLACE_OGN_ID, NOW(), $route_processed, $route_distance_metres, $route_duration_seconds );
SET $ROUTE2_ID = LAST_INSERT_ID();
END IF;
IF NOT 0 = $route_distance_metres THEN
UPDATE base_places_routes
SET
route_distance_metres = $route_distance_metres,
route_duration_seconds = $route_duration_seconds
WHERE ROUTE_ID = $ROUTE1_ID
AND route_distance_metres = 0;
UPDATE base_places_routes
SET
route_distance_metres = $route_distance_metres,
route_duration_seconds = $route_duration_seconds
WHERE ROUTE_ID = $ROUTE2_ID
AND route_distance_metres = 0;
END IF;
UPDATE base_places_routes
SET
route_processed = $route_processed
WHERE ROUTE_ID = $ROUTE1_ID;
UPDATE base_places_routes
SET
route_processed = $route_processed
WHERE ROUTE_ID = $ROUTE2_ID;
END IF;
END
//
DELIMITER ;
DROP PROCEDURE Base_Places_Routes_Save;
DELIMITER //
CREATE PROCEDURE Base_Places_Routes_Save
(
$Sid CHAR(64),
$apikey CHAR(64),
$ROUTE_ID INT,
$field TEXT,
$value TEXT
)
SQL SECURITY DEFINER
COMMENT 'EXPORT'
BEGIN
DECLARE $GROUP_ID INT DEFAULT 0;
CALL Base_Users_Authorise_Sessionid_Or_APIKey( $Sid, $apikey, @email, @USER, @idtype );
SELECT GROUP_ID INTO $GROUP_ID FROM base_places_routes WHERE ROUTE_ID=$ROUTE_ID;
IF @@read_only THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'READ_ONLY';
ELSEIF NOT( "ADMIN" = @idtype OR Base_Groups_Members_Contains( $GROUP_ID, @USER ) ) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_AUTHORISATION IN Base_Places_Routes_Save';
ELSE
CASE $field
WHEN 'route_distance_metres' THEN UPDATE base_places_routes SET route_distance_metres = $value, route_processed=NOW() WHERE ROUTE_ID=$ROUTE_ID;
WHEN 'route_duration_seconds' THEN UPDATE base_places_routes SET route_duration_seconds = $value, route_processed=NOW() WHERE ROUTE_ID=$ROUTE_ID;
WHEN 'route_shortest_distance_metres' THEN UPDATE base_places_routes SET route_shortest_distance_metres = $value WHERE ROUTE_ID=$ROUTE_ID;
WHEN 'route_shortest_duration_seconds' THEN UPDATE base_places_routes SET route_shortest_duration_seconds = $value WHERE ROUTE_ID=$ROUTE_ID;
ELSE
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'INVALID_CASE_OPTION IN Base_Places_Routes_Save';
END CASE;
END IF;
END
//
DELIMITER ;