Re: [PHP] Maps / Distance / GeoCoding [php/mysql]
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/1000).")'));";
$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/1000).")'))
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
Re: [PHP] Maps
On 12/6/07, Richard Lynch <[EMAIL PROTECTED]> wrote: > > Yes, you can do that, and look at Yahoo! maps as an alternative. > > Same smell. :-) > > On Wed, December 5, 2007 12:27 pm, Robert Fitzpatrick wrote: > > Well, I have two clients that both want mapping for their sales > > people. > > I have been looking at the Google API and Phoogle > > (www.systemsevendesigns.com/phoogle). I assume with these examples I > > will be able to produce what they need? Which is to select several > > addresses and produce a fastest route map with directions to those > > addresses from a start address. Not clear from my reading yet, but can > > someone with some experience tell me if this is possible with the > > Google > > API or some hints that might speed up my process of the best way to go > > for this... > > > > Thanks in advance! > > > > -- > > Robert > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > > > > > -- > Some people have a "gift" link here. > Know what I want? > I want you to buy a CD from some indie artist. > http://cdbaby.com/from/lynch > Yeah, I get a buck. So? > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > Welcome back, Richard. David
Re: [PHP] Maps
Yes, you can do that, and look at Yahoo! maps as an alternative. Same smell. :-) On Wed, December 5, 2007 12:27 pm, Robert Fitzpatrick wrote: > Well, I have two clients that both want mapping for their sales > people. > I have been looking at the Google API and Phoogle > (www.systemsevendesigns.com/phoogle). I assume with these examples I > will be able to produce what they need? Which is to select several > addresses and produce a fastest route map with directions to those > addresses from a start address. Not clear from my reading yet, but can > someone with some experience tell me if this is possible with the > Google > API or some hints that might speed up my process of the best way to go > for this... > > Thanks in advance! > > -- > Robert > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Some people have a "gift" link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[Fwd: Re: [PHP] Maps]
I forgot to CC the list, sorry. Original Message Subject: Re: [PHP] Maps Date: Wed, 05 Dec 2007 20:25:06 +0100 From: Jochem Maas <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] References: <[EMAIL PROTECTED]> Robert Fitzpatrick wrote: > Well, I have two clients that both want mapping for their sales people. > I have been looking at the Google API and Phoogle > (www.systemsevendesigns.com/phoogle). I assume with these examples I > will be able to produce what they need? Which is to select several > addresses and produce a fastest route map with directions to those > addresses from a start address. Not clear from my reading yet, but can > someone with some experience tell me if this is possible with the Google > API or some hints that might speed up my process of the best way to go > for this... google maps is no real help here, your able to plot points but there is no API interface with their routing functionality (so you have no idea where roads actually are) ... you've been asked to solve the travelling salesman problem, this is hard. time to start reading: http://www.google.com/search?q=travelling+salesman+problem and more specifically (with an eye to finding something that solves the problem for you): http://www.google.com/search?q=travelling+salesman+problem+google+maps chances are you'll end up building something expensive, likely that offering something that allows these salepeople to quickly/easily link to google/yahoo/whatever maps and then manually view/tweak their route will be a lot less painful to implement and alot more cost effective. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Maps
On Dec 5, 2007 1:27 PM, Robert Fitzpatrick <[EMAIL PROTECTED]> wrote: > Well, I have two clients that both want mapping for their sales people. > I have been looking at the Google API and Phoogle > (www.systemsevendesigns.com/phoogle). I assume with these examples I > will be able to produce what they need? Which is to select several > addresses and produce a fastest route map with directions to those > addresses from a start address. Not clear from my reading yet, but can > someone with some experience tell me if this is possible with the Google > API or some hints that might speed up my process of the best way to go > for this... To be honest, your best bet would be to ask in one of their groups. Google has groups specifically-dedicated to its API, so you'd probably be better off asking there than on the PHP list. -- Daniel P. Brown [Phone Numbers Go Here!] [They're Hidden From View!] If at first you don't succeed, stick to what you know best so that you can make enough money to pay someone else to do it for you. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
