CSV File query in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

CSV File query in PHP

Post by bwv2 »

I have a large .csv file (1 GB) containing information pertaining to different coordinate positions. The user inputs a pair of coordinates on a page and then presses "submit". This sends the coordinates to a page that gets the info from the .csv pertaining to the coordinates they entered.

However, there is no guarantee that the exact coordinates they entered will be in the .csv file, so I need to choose the closest coordinates. To do this, I would like to pull all coordinate pairs that are like the input coordinates and place them into an array (i.e. if the coordinates are entered to 6 significant figures, I would truncate it to 4 sig figs and find all coords that share those first sig figs.)

Up until now I have been doing this with mySQL. I imported the .csv file into mySQL and had a huge table in my database. Well, now I have about 30 other tables just like it (oh, sweet growth!) that I need to use as well. I don't want to have a huge mySQL database, so I'd prefer to put the .csv files into a folder on the server and just pull from them instead. Here is a basic summary of how I pull the data from mySQL:

Code: Select all

$lat=$_GET['lat'];
$lon=$_GET['lon'];
$latRound=round($lat,1);
$lonRound=round($lon,0);
$sql="SELECT * FROM coordData WHERE latitude LIKE '$latRound%' AND longitude LIKE '$lonRound%'";
$result=mysql_query($sql,$conn);
Basically what I need is some help with a script that can pull entire rows for a range of field values from a .csv file, then store them in an array in PHP. Once I get the array of potential rows, I can write a script to choose the closest value. My only issue is getting teh array built efficiently. Any help would be appreciated.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

reading a 1GB file for a specific piece of information will take longer than performing a query against that same information. :?
bwv2
Forum Commoner
Posts: 83
Joined: Fri Jun 10, 2005 11:50 am
Location: AZ

Post by bwv2 »

What if I had a 100MB to 500MB file? I don't mind if it takes up to 30 seconds or so, because the query doesn't happen too often and I can send them to a "wait" page.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It could easily take longer than 30 seconds to process the full file. Stick with a properly built database table.

I'd suggest having the database perform the distance calculation as well. I don't want to rain on your parade too much, but your query has several issues that may be a problem: Your search can only find similar coordinates within the grid, but if the user enters a coordinate near the board of a grid you may miss an even closer stored position. That is why a real distance calculation should be performed. It will find the closest of all coordinates.
Post Reply