1
4th May 05:48
External User

Maps / Distance / GeoCoding
re: an earlier thread
as promised here are some note's on geocoding 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
geocords 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 do***entation
http://dev.mysql.com/doc/refman/5.0/en/spatialextensions.html); in
short they are binary storage columns for geodata. (here's a handy link
about it aswell:
http://dev.mysql.com/techresources/articles/4.1/giswithmysql.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
