使用 PHP、MySQL 和创建商店定位器谷歌地图有点不同

发布于 2024-12-19 14:05:36 字数 880 浏览 0 评论 0原文

我想使用 Google 地图创建商店定位器。

我有一个数据库，其中包含旅游对象表（及其坐标）和酒店表（也及其坐标）。

我希望用户在加载伦敦塔页面后能够查看 10 公里半径内该物体附近有哪些酒店，并在 Google 地图上显示带有标记的结果。

到目前为止，我只能使用 hasrsin 公式从数据库中获取 10 公里范围内的酒店，并将它们显示为文本：

$result = mysql_query("SELECT  nume,  poze, descriere, link, (
(
    ACOS( SIN( 45.515038 * PI( ) /180 ) * SIN( latitudine * PI( ) /180 ) +
          COS( 45.515038 * PI( ) /180 ) * COS( latitudine * PI( ) /180 ) *
          COS( ( 25.366935 - longitudine ) * PI( ) /180 )
    ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM `unitati`
HAVING distance <= '10'
ORDER BY distance ASC
LIMIT 0 , 30");

如何将它们显示为地图上的标记？

我发现了这个，我认为它可以帮助我：http: //code.google.com/intl/ro-RO/apis/maps/articles/phpsqlsearch.html，但它有不同的逻辑：用户输入地址。

原文

I want to create a store locator with Google Maps.

I have a database with a table of TOURISTIC OBJECTS (with their coordinates) and a table of HOTELS (also with their coordinates).

I want the user to have the possibility, after he loads the page of the Tower of London, let's say, to see what hotels are near the object within a 10-kilometer radius and display the results on Google Maps with markers.

So far, I've only managed to fetch the hotels in a 10-kilometer range from the database with the haversin formula and display them as text:

$result = mysql_query("SELECT  nume,  poze, descriere, link, (
(
    ACOS( SIN( 45.515038 * PI( ) /180 ) * SIN( latitudine * PI( ) /180 ) +
          COS( 45.515038 * PI( ) /180 ) * COS( latitudine * PI( ) /180 ) *
          COS( ( 25.366935 - longitudine ) * PI( ) /180 )
    ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM `unitati`
HAVING distance <= '10'
ORDER BY distance ASC
LIMIT 0 , 30");

How can I display them as markers on the map?

I found this and I thought it could help me: http://code.google.com/intl/ro-RO/apis/maps/articles/phpsqlsearch.html, but it has a different logic: the user types in the address.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

_蜘蛛 2024-12-26 14:05:36

此示例接受您的查询（您需要使用每个目标的纬度/经度编辑 45.515038 和 25.366935）并将其输出为 JS 数组数组（如果您愿意，您可以将其设置为更正式的 JSON），

然后循环通过该数组，为每个元素制作标记并将它们放置在地图上。最后，它为每个添加一个点击侦听器，以便它显示相关信息。

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
<style>
#map_canvas{width:500px;height:500px;}
</style>
</head>

<body>
<div id="map_canvas"></div>
<script>
<?php
//you'll first need to connect to your db
//you also have to edit the lat/lng in your SELECT statement to be that of your objective
$result = mysql_query("SELECT  latitudine, longitudine, nume,  poze, descriere, link, (
(
    ACOS( SIN( 45.515038 * PI( ) /180 ) * SIN( latitudine * PI( ) /180 ) +
          COS( 45.515038 * PI( ) /180 ) * COS( latitudine * PI( ) /180 ) *
          COS( ( 25.366935 - longitudine ) * PI( ) /180 )
    ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM `unitati`
HAVING distance <= '10'
ORDER BY distance ASC
LIMIT 0 , 30");
$c=0;
echo "var data=[";
while($markers=mysql_fetch_array($result)){
    if($c>0){echo ",";}
    echo "['".$markers[0]."','".$markers[1]."','".$markers[2]."','".$markers[3]."','".$markers[4]."','".$markers[5]."','".$markers[6]."'"."]";
    $c++;
}
echo "];";
?>
    //var data=[['45','-73','home','poz1','desc1','link1'],['43','-75','work','poz2','desc2','link2']];
    var places=new Array();
    var map;
    var MyInfoWindow = new google.maps.InfoWindow({content: 'Loading...'});
    var bounds = new google.maps.LatLngBounds();
    var myOptions = {
        zoom: 9,
        mapTypeControl: false,
        mapTypeId: google.maps.MapTypeId.ROADMAP
    };
    map = new google.maps.Map(document.getElementById('map_canvas'),myOptions);
    for(i=0;i<data.length;i++){
        var point=new google.maps.LatLng(data[i][0],data[i][1]);
        var a = new google.maps.Marker({position: point,map: map, icon:'someHotelIcon.png'});
        a.lat=data[i][0];
        a.lng=data[i][1];
        a.nume=data[i][2];
        a.poze=data[i][3];
        a.desc=data[i][4];
        a.url=data[i][5];
        places.push(a);
    }
    for(i=0;i<places.length;i++){
        var point2=new google.maps.LatLng(places[i].lat,places[i].lng);
        bounds.extend(point2);
    }
    map.fitBounds(bounds);
    var objLoc=new google.maps.LatLng(45.515038,26.366935);
    var objectiveMarker = new google.maps.Marker({position: objLoc,map: map, icon:'objectiveIcon.png'});    //---------------Marker for objective
    for (var i = 0; i < places.length; i++) {
        var marker = places[i];
        google.maps.event.addListener(marker, 'click', function () {
        MyInfoWindow.setContent(this.nume+'<br/>'+this.desc+'<br/><a href=\"'+this.url+'\">link</a>');
        MyInfoWindow.open(map, this);
        });
    }
</script>
</body>
</html>

This example takes your query (and you'll need to edit the 45.515038 and 25.366935 with the lat/lng for each objective) and outputs it as a JS array of arrays (you could make it more formal JSON if you like)

It then loops through that array, making markers for each and placing them on a map. Finally, it adds a click listener to each so that it'll display relevant information.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?sensor=false"></script>
<style>
#map_canvas{width:500px;height:500px;}
</style>
</head>

<body>
<div id="map_canvas"></div>
<script>
<?php
//you'll first need to connect to your db
//you also have to edit the lat/lng in your SELECT statement to be that of your objective
$result = mysql_query("SELECT  latitudine, longitudine, nume,  poze, descriere, link, (
(
    ACOS( SIN( 45.515038 * PI( ) /180 ) * SIN( latitudine * PI( ) /180 ) +
          COS( 45.515038 * PI( ) /180 ) * COS( latitudine * PI( ) /180 ) *
          COS( ( 25.366935 - longitudine ) * PI( ) /180 )
    ) *180 / PI( )
) *60 * 1.1515 * 1.609344
) AS distance
FROM `unitati`
HAVING distance <= '10'
ORDER BY distance ASC
LIMIT 0 , 30");
$c=0;
echo "var data=[";
while($markers=mysql_fetch_array($result)){
    if($c>0){echo ",";}
    echo "['".$markers[0]."','".$markers[1]."','".$markers[2]."','".$markers[3]."','".$markers[4]."','".$markers[5]."','".$markers[6]."'"."]";
    $c++;
}
echo "];";
?>
    //var data=[['45','-73','home','poz1','desc1','link1'],['43','-75','work','poz2','desc2','link2']];
    var places=new Array();
    var map;
    var MyInfoWindow = new google.maps.InfoWindow({content: 'Loading...'});
    var bounds = new google.maps.LatLngBounds();
    var myOptions = {
        zoom: 9,
        mapTypeControl: false,
        mapTypeId: google.maps.MapTypeId.ROADMAP
    };
    map = new google.maps.Map(document.getElementById('map_canvas'),myOptions);
    for(i=0;i<data.length;i++){
        var point=new google.maps.LatLng(data[i][0],data[i][1]);
        var a = new google.maps.Marker({position: point,map: map, icon:'someHotelIcon.png'});
        a.lat=data[i][0];
        a.lng=data[i][1];
        a.nume=data[i][2];
        a.poze=data[i][3];
        a.desc=data[i][4];
        a.url=data[i][5];
        places.push(a);
    }
    for(i=0;i<places.length;i++){
        var point2=new google.maps.LatLng(places[i].lat,places[i].lng);
        bounds.extend(point2);
    }
    map.fitBounds(bounds);
    var objLoc=new google.maps.LatLng(45.515038,26.366935);
    var objectiveMarker = new google.maps.Marker({position: objLoc,map: map, icon:'objectiveIcon.png'});    //---------------Marker for objective
    for (var i = 0; i < places.length; i++) {
        var marker = places[i];
        google.maps.event.addListener(marker, 'click', function () {
        MyInfoWindow.setContent(this.nume+'<br/>'+this.desc+'<br/><a href=\"'+this.url+'\">link</a>');
        MyInfoWindow.open(map, this);
        });
    }
</script>
</body>
</html>

回复收藏 0 原文

匿名。 2024-12-26 14:05:36

如果您打算进行基于距离/地理位置的搜索，请使用 MySQL 的地理空间功能。它比直接半正矢公式更有效。

这些只是一些片段，但它们应该可以帮助您走上精彩之路：

首先，您的表格需要一个地理空间列来保存您的纬度/经度，我的称为坐标，类型为点：

`coordinate` point NOT NULL,

接下来，您需要添加地理空间索引：（

SPATIAL KEY `coordinate` (`coordinate`)

这两个都是我的表创建 sql 语法的一部分）。

这一点将有助于将您的纬度/经度数据插入到坐标列中：

$data['coordinate'] = ("GeomFromText( 'POINT({$data['longitude']} {$data['latitude']})')");

这一点将取代您的距离计算（作为更大的 SQL 查询的一部分）：

$sql = str_replace(
            array('%LATITUDE%','%LONGITUDE%'), 
            array($latitude, $longitude),
            '(GLength( LineString( coordinate ,Point(%LONGITUDE%, %LATITUDE%)))) AS distance'
        );

上面的查询适用于欧几里得几何（即世界是平的），但世界并非如此，因此您应该使用以下内容来帮助缩小结果列表的范围。为了获得真正的准确性和正确的排序，请对从这 2 个 SQL 查询返回的结果使用半正矢或更好的公式。

该位将充当边界矩形/框（任何不在框内的内容都不会被返回 - 使用它来替换 HAVING distance <= '10'，您会发现查询速度更快 - 特别是有大量数据）。我对最大/最小纬度/经度的计算只是示例，您可以做得更好:-)：

        $minLat = $latitude - 0.5;
        $maxLat = $latitude + 0.5;
        $minLon = $longitude - 0.5;
        $maxLon = $longitude + 0.5;

        $sql = str_replace(
            array('%MINLAT%', '%MAXLAT%','%MINLON%', '%MAXLON%'), 
            array($minLat, $maxLat, $minLon, $maxLon),
            "MBRCONTAINS(GeomFromText('POLYGON((%MINLON% %MAXLAT%,%MAXLON% %MAXLAT%,%MAXLON% %MINLAT%,%MINLON% %MINLAT%, %MINLON% %MAXLAT%))'), `coordinate`)"
        );

If you are going to do distance/geolocation based search use MySQL's geo-spatial stuff. It is far more efficient then straight haversine formula.

These are just some snippets but they should help put you on the path to awesomeness:

First off you table will need a geospatial column to hold your lat/lon, mine is called coordinate and is on type point:

`coordinate` point NOT NULL,

next you will need to add a geospatial index:

SPATIAL KEY `coordinate` (`coordinate`)

(Both of these are part of my table create sql syntax).

This bit will help insert your lat/lon data into the coordinate column:

$data['coordinate'] = ("GeomFromText( 'POINT({$data['longitude']} {$data['latitude']})')");

This little bit will would replace your distance calc (as part of a larger SQL query):

$sql = str_replace(
            array('%LATITUDE%','%LONGITUDE%'), 
            array($latitude, $longitude),
            '(GLength( LineString( coordinate ,Point(%LONGITUDE%, %LATITUDE%)))) AS distance'
        );

The above query s good for euclidean geometry (i.e. the world is flat), but the world isn't so you should use the below to help narrow down your results list. For real accuracy and correct ordering use the haversine or better formula on the results that are brought back from these 2 SQL queries.

This bit will act as a bounding rectangle/box (anythng not inside the box will not be returned - use it to replace HAVING distance <= '10', you'll find the query faster - especially with lots of data). My calculations for max/min lat/lon are just examples, you can do better :-) :

        $minLat = $latitude - 0.5;
        $maxLat = $latitude + 0.5;
        $minLon = $longitude - 0.5;
        $maxLon = $longitude + 0.5;

        $sql = str_replace(
            array('%MINLAT%', '%MAXLAT%','%MINLON%', '%MAXLON%'), 
            array($minLat, $maxLat, $minLon, $maxLon),
            "MBRCONTAINS(GeomFromText('POLYGON((%MINLON% %MAXLAT%,%MAXLON% %MAXLAT%,%MAXLON% %MINLAT%,%MINLON% %MINLAT%, %MINLON% %MAXLAT%))'), `coordinate`)"
        );

回复收藏 0 原文

~没有更多了~