Nrixham2012-05-04 04:48:53

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

Nrixham2012-05-04 04:48:55

[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.sperling2012-05-04 11:17:28

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

## Leave a Reply

You must be logged in to post a comment.