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/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
Very impressive.
But please realize your solution is an approximation that should work
well for postal codes and other such inaccuracy tolerant location
questions. However, there are accuracy demands of mapping that will
far exceed your solution.
Cheers,
tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
