6 replies to this topic

[Dean]

Hi Everyone, I need some PHP & mySQL help please!

I've successfully imported a file that includes access points in the area into my Etomite database (which I'm then going to use to generate a KML file).
The problem? I have a few duplicates within it.

The only unique thing that the software that I'm exporting from includes is the mac address (as no two are the same). It's possible to have two Access Point names the same, so I can't rely on that to find duplicates.

The problem is that not only do I want to filter the duplicates, I want it to delete the ones that've got the higher horizontalaccuracy number.

The software generates

<Placemark>
  <name>
	<![CDATA[zoom]]>
  </name>
  <description>
	<![CDATA[mac: 0:1:38:8d:e5:d2<br>channel: 10<br>MaxRssi: 4<br>Security: None<br>Type:Access Point<br>FirstSeen: 2009-03-17 11:37:42 AM +0000<br>LastSeen: 2009-03-17 11:37:55 AM +0000<br>HorizontalAccuracy: 80.651959]]>
  </description>
  <Point>
	<coordinates>-2.881937,54.062629,0</coordinates>
  </Point>
</Placemark>

which after I've searched and replaced I manage to get 539 lines of the following, which I ran in phpMyAdmin... so now I've got all the data in the database.. you can see the fields I've used within the table in the queries.

INSERT INTO `lawifii_db`.`etomite_wifi` (`id` , `name` , `mac` , `channel` , `maxrssi` , `security` , `type` , `firstseen` , `lastseen` , `horizontalaccuracy` , `coordinates` ) VALUES ( NULL , 'zoom', '0:1:38:8d:e5:d2', '10', '4', 'NONE', 'AP', '2009-03-17 11:37:42', '2009-03-17 11:37:55', '80.651959', '-2.881937,54.062629,0' );

So can anyone tell me the php code I need to use to remove the duplicates that have the higher horizontalaccuracy number?

[Dean]

Also, I've just noticed that some of them have duplicate horizontalaccuracy numbers, which i'll manually remove so it's safe to remove the ones that have the duplicate horizontalaccuracy

[Ralph]

I wasn't aware that there was a way to gather a MAC address... I've hunted for ways for years without much luck...

[Dean]

Yeah there are plenty of WarDriving applications out there.. the one I use gathers all of the data it outputs:
SSID
MAC Address
Channel
Security Type (None, WEP, WPA, WPA2)
Type of WiFi (Access Point, Infastructure, Ad-Hoc)
Firstseen & Lastseen Date
Horizontal Accuracy
Geo-Coordinates

Whic is obviously reliant on GPS coverage also ;-) Otherwise I'd just end up with a list of access points and no idea where they are.

Once I've figured out how to remove the duplicate data, I'll show you the output.

[Dean]

ok, scratch that... I've nearly manually removed the duplicates.

[Dean]

Hey Ralph, here we go: http://maps.google.co.uk/maps?f=q&sour...mp;t=h&z=14

[mikef]

weird shaped beach you have thee in Morecambe