At 11:51 PM +0100 9/11/08, Nathan Rixham wrote:
re: an earlier thread
as promised here are some note's on geo-coding using mysql and php
(and geoip and distance between points) also worth reading up on
wiki about the great circle and associated content! (+openGIS)
Won't make sense to all unless you're working with spatial data - if
anybody needs I also have the full ip to location databases; + the
geo-cords or every dwelling place in the world (basically a mashup
of all decent databases combined - about 4gb worth when rar'd)
if your a postgres coder you want be checkign out postgis and pgrouting
-
Geo Data Spatial
All geo columns are of type GEOMETRY sith SPATIAL indexes (see mysql
5 spatial documentation
http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html); in
short they are binary storage columns for geodata. (here's a handy
link about it aswell:
http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html
)
you extract data from them by using:
X(point) as lon
Y(point) as lat
or AsText(point)
in where statements you use MBRContains
some functions:
function spatialCountryFromIp($ipNumeric=false) {
if($ipNumeric) {
$getResultsSQL = select
l.isocode,
l.en,
X(l.geopoint) as lon,
Y(l.geopoint) as lat
from
geodata.spatialip_country as i
inner join
geodata.spatialloc_country as l
ON
MBRContains(l.geopoint,i.geopoint)
where
MBRContains(i.iprange,PointFromText('Point(0
.($ipNumeric/1000).)'));;
$result = mysql_query($getResultsSQL);
/* do what you want with data */
}
}
public function spatialCityFromIp($ipNumeric=false, $within=1, $units='km') {
if($ipNumeric) {
if(strtolower(trim($units)) == 'km') {
$single_unit = 0.0089992800575953923686105111591073;
}
$offset = 1*$single_unit;
if(is_numeric($within) $within) {
$offset = $within*$single_unit;
}
$getResultsSQL = select
l.cid,
l.name,
X(l.point) as lon,
Y(l.point) as lat,
l.cc,
l.pop,
ACOS(
SIN(Y(g.geopoint)*pi()/180)*SIN(Y(l.point)*pi()/180)
+COS(Y(g.geopoint)*pi()/180)*COS(Y(l.point)*pi()/180)
*COS((X(l.point)-X(g.geopoint))*pi()/180)
)*6372.795
as distance
from
geodata.spatialip_city as g
inner join
geodata.spatialloc_city as l
ON
MBRContains(
GeomFromText(
CONCAT(
'POLYGON(
(
',X(g.geopoint)-(.$offset.),'
',Y(g.geopoint)-(.$offset.),',
',X(g.geopoint)+(.$offset.),'
',Y(g.geopoint)-(.$offset.),',
',X(g.geopoint)+(.$offset.),'
',Y(g.geopoint)+(.$offset.),',
',X(g.geopoint)-(.$offset.),'
',Y(g.geopoint)+(.$offset.),',
',X(g.geopoint)-(.$offset.),'
',Y(g.geopoint)-(.$offset.),'
)
)
'))
,l.point)
where
MBRContains(g.iprange,PointFromText('Point(0
.($ipNumeric/1000).)'))
ORDER BY
distance;;
$result = mysql_query($getResultsSQL);
/* do something with returned data */
} else {
return false;
}
}
the key to using spatial indexes is MBRContains() together with
POLYGON OR POINT()
where the column has point data in it, you use a POLYGON to select
info around it, this is your radius as it where, but square! the
polygon goes:
dMinLong + + dMinLat
dMaxLong + + dMinLat
dMaxLong + + dMaxLat
dMinLong + + dMaxLat
