nrixham

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/...ensions.html); in
short they are binary storage columns for geodata. (here's a handy link
about it aswell:
http://dev.mysql.com/tech-resources/...ith-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

nrixham

[snip]

or you may just need this bit:

distance =
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

used to calc the distance between two points on the surface of the earth
accurately

tedd.sperling

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