Maps / Distance / GeoCoding [php/mysql]

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/10000000).")'));"; $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/10000000).")')) 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 dMinLong + " " + dMinLat so.. in the above spatialCityFromIp function we: query the spatialip_city first of all, using the final where clause: MBRContains(g.iprange,PointFromText('Point(0 ".($ipNumeric/10000000).")')) the iprange column is a gemotery column, that stores ip ranges as a POLYGON, think of the iprange as being stored as a box with four corners 0 ipsi/10000000 1 ipsi/10000000 1 ipei/10000000 0 ipei/10000000 0 ipsi/10000000 where the longitude is always 1 or 0 and the latitude acually stores the iprange (ipsi = start ip in range as numeric) (ipei = end ip in range as numeric) so we query the polygon to which POLYGON (iprange) the POINT (in this case a numeric ip) is in. this returns a single row which contains the iprange and long/lat point we then INNER JOIN the spatialloc_city table on the returned long/lat point, to find which places that point is near to do this, again we use MBRContains, but this time the stored data is a point, so we want to draw a virtual polygon and return all the points within that polygon we obviously had to calculate the dimensions of the box, we did this by finding the degree value of 1km: 0.0089992800575953923686105111591073 and multiplying it by the amount of km's we want to search within, say 15km (15*0.0089992800575953923686105111591073) 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) this will be the same for your postcode/business database, say geopoint was the long/lat of a postcode and each business had it's own long/lat assigned to it, set offset to 10 and it'll return all businesses within 10km of postcode's lat/lon the final thing we've done is ordered by distance, by calculating the distance from the ip's point to the places point this calculation is: ACOS( SIN(g.lat*pi()/180)*SIN(Y(point)*pi()/180) +COS(g.lat*pi()/180)*COS(Y(point)*pi()/180) *COS((X(point)-g.lon)*pi()/180) )*6372.795 as distance you may want to read up on the "Great Circle" wikipedia has a good article on it, but basically that little string up there will calc distance as the crow flies from any two points. those three sql statements there will cover everything you could need to do, it's just a case of putting them together how you want mate. more reference:: to insert geo POINT into a table.. INSERT INTO table (pointcol) VALUES ( PointFromText(CONCAT('POINT(',lon,' ',lat,')')) ) a really simply lookup: select AsText(geopoint) from spatialloc_country as i where MBRContains(i.geopoint,PointFromText('POINT(-2 54)')); AND select AsText(geopoint) from spatialloc_city as i where MBRContains(i.geopoint,PointFromText('POINT(LON LAT)')); (replace lon and lat above) regards all