Instantly share code, notes, and snippets.

Embed
What would you like to do?
getAddress SQL for the OSM database created using http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/
-- Source: http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/
-- getAddress SQL for the OSM database
DELIMITER ;;
CREATE PROCEDURE `getAddress`(IN lat1 double, IN log1 double)
BEGIN
SELECT
nodetags.id,
GROUP_CONCAT(CONCAT(nodetags.k,"=",nodetags.v) SEPARATOR ',') AS address,
fp.distance_in_meters
FROM
nodetags,
(
SELECT
nodes.id,
slc( lat1, log1, y(nodes.geom), x(nodes.geom))*1000 as distance_in_meters,
astext(nodes.geom)
FROM
nodes
WHERE
MBRContains(envelope(linestring(point((log1+(0.1/111)), (lat1+(0.1/111))), point((log1-(0.1/111)), (lat1-(0.1/111))))), nodes.geom)
LIMIT 10000
) AS fp
WHERE
fp.id = nodetags.id AND
nodetags.k LIKE 'addr%' AND
nodetags.k IS NOT NULL
GROUP BY fp.id
ORDER BY fp.distance_in_meters ASC;
END;;
DELIMITER ;
-- slc function excerpted from http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/
CREATE FUNCTION `slc`(lat1 double, lon1 double, lat2 double, lon2 double) RETURNS double
RETURN 6371 * acos(cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) + sin(radians(lat1)) * sin(radians(lat2)));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment