caleb87

temporary table Dumping 580k rows to tempTable ("INSERT INTO tempTable (col1, col2, col3, col4) VALUES (1, 2, 3, 4), (5, 6, 7, , (9, 10, 11, 12)"): InnoDB: 200 seconds MyISAM: 130 seconds --251 bytes per row "SELECT * FROM tempTable LEFT JOIN fewMillionRowsON tempTable.col1=fewMillionRows.col1 AND tempTable.col2=fewMillionRows.col2 AND tempTable.col3=fewMillionRows.col3 AND tempTable.col4=fewMillionRows.col4 LIMIT 1000" The select took 30 seconds on 1000 limit. ----------------------------------------- asynchronous queries It appears I could use mysqli asynchronous queries (pdo doesn't appear to have this; but I haven't looked much yet). At first glance this seems to be the best solution or the php array dump. ----------------------------------------- original question Even if I find a superb alternative, I would really like to understand why it takes PHP so long to communicate to MySQL for each select in my original question. It's about .0068 seconds per query. It would be nice if it were .00068 instead. I did duplicate fewMillionRows to a memory engine, which reduced the select on 10k rows to 43.42 seconds. I don't really like doing this though, since any time the server is reboot it has to be rebuilt.

In this instance, $bigList is not from a database. It's a csv file. Yes the column values are from $bigList ($preparedValues); I just didn't show binding the parameters to keep it short. It's merely example code.

Hello, Any ideas why server resources not exhausted on the code below, when my.cnf and php.ini are both set to use half the resources each? PHP & MySQL will both utilize all allotted resources on other scripts, so it's not a tuning problem. There is no script-side tuning. The bottleneck: pdo select as shown below foreach($bigList as $listObject) { $sql = $dbl->prepare("SELECT * FROM fewMillionRows WHERE indexedCol1=:indexedCol1 AND indexedCol2=:indexedCol2 AND indexedCol3=:indexedCol3 AND indexedCol4=:indexedCol4 LIMIT 1"); $sql->execute($preparedValues); $return = $sql->fetchAll(PDO::FETCH_ASSOC); } On a dedicated server with 8GB RAM, the server uses >5% cpu/ram but takes a long time to finish the script. Second Question What are some alternative designs? Because the column values all happen to be alphanumeric, I could select the entire table and store it in an array. Accessing the keys like so: indexedCol1indexedCol2indexedCol3indexedCol4. Results: MyISAM - Select Whole Table: 30 seconds MyISAM – Select Individual Rows (10k times) – 68 seconds InnoDB – Select Whole Table: 30 seconds InnoDB – Select Individual Rows (10k times) – 131 seconds I am surprised it takes so long to select a whole table. The server resources use 1% for about 20 seconds, then cpu/ram jump to 30%+ for about 10 seconds. This is still drastically faster than individual selects. In this instance, $bigList is over 500k items. At 68 seconds per 10k rows it’s absurdly long. Building an array with key/values is the only realistic way I currently know of, but I suspect there is a much better way of doing this. As far as I know, I cannot do a select like so: SELECT * FROM t1 WHERE (column1,column2,column3) IN ((val1, val2,val3), (val4, val5,val6)) There is no way to determine whether a row was found for each entry as far as I know.

Well I realized a solution. I guess I'm too quick to post a topic lol I didn't figure out how to determine the middle coordinate of the L shape polygons, which I would still find interesting if anyone knows. I realized I can just use all the verticies that are within 1/10 mile of the min/max XY values (rather than the ones that would fall in the middle).

I want to determine the point that is .0018 lat/lon (roughly a 1/10 mile) within the interior of different polygons at each veritces. It doesn't have to be precise, but I really just need to ensure it is within the polygon. The blue dots represent approximate coordinates I want. The google maps points I used for the L shaped polygon: var polygon1 = [ new google.maps.LatLng(48.110073077,-102.568801362), new google.maps.LatLng(48.124993054,-102.568801526), new google.maps.LatLng(48.124573268,-102.547401948), new google.maps.LatLng(48.124603482,-102.525802378), new google.maps.LatLng(48.110173554,-102.52560225), new google.maps.LatLng(48.095623624,-102.525702115), new google.maps.LatLng(48.09563336,-102.547381656), new google.maps.LatLng(48.110123313,-102.547401802), new google.maps.LatLng(48.110073077,-102.568801362), ]; The square wouldn't be too hard, but the L shape has my little brain going. Any suggestions? ** PS. I realize the post title doesn't make sense. Originally I wanted centroids, but decided vertices would be better.

Wow. More than I expected Thanks mac!

Just for S&G, ya'll can checkout this site: http://www.darrinward.com/lat-long/ And put in this value: 48.010918880032534,-102.27871447619208 48.007298546092386,-102.27871873313035 48.007300404087175,-102.28410804755288 48.007302263082124,-102.28949736197535 48.007304121076913,-102.29488670439923 48.007305979071589,-102.30027601882199 48.010925506717513,-102.30027931864152 48.014545007363211,-102.30028259146059 48.018163867996407,-102.3002842242235 48.023143739025727,-102.30028731751861 48.023143709229146,-102.29967535036019 48.023140964347476,-102.29489150234963 48.023137856666608,-102.28949565918271 48.023137825974516,-102.28890660889158 48.023135053987971,-102.28409987201547 48.023131946306933,-102.2787040848512 48.018158490915084,-102.27870810340568 48.014539184972818,-102.27871024825487 48.010918880032534,-102.27871447619208 And see all the coordinates actually pulled from the BLM. There are sections along the border of townships called "Fractional Sections", which reduce/add acreage to a section to account for the curvature of the earth. These sections are generally what cause me issues since they are less square shaped. If I get 4 fractional sections stacked up, the variance is horrible. My plan was to use the min/max values to find potential matches, then use the exact coordinates and math to determine if the object is actually inside the tract of land. I'm going to try to determine the coordinate with the min/max values, though I still think there's a better way lol. Thanks for all the replies! Oh and no... the website is not mine. It's just the only one I can find that I can easily add a list of XY values. Can't believe how few there are that actually work.

That's a good idea. I'm going to try something like this.

Barand would be right, all but 2,3. I think you both get what I want, but see attached image. Red square is what's produced using min/max. I need the blue square. There will always be 4 coordinates that make up a square/rectangle more or less. I want the exact coordinates, because when I combine two sections vertically, I should end up with an approx. 2x1 mile tract of land. The method I'm using now is min/max, but it produces a 2.1x1.25 mile tract more or less. If I go out four sections, it really gets bad.

I stated in my first post "I am trying to determine the four corner coordinates of a list. Any ideas?". The prior suggestions don't determine the actual coordinates. They get ballpark coordinates, which end up producing an exact square. I want the exact corner coordinates which typically produces a rhombus. I am sending a request to the Bureau of Land Management for a tract or section coordinates. The response includes all the coordinates within the section as well (quarters, and quarter quarters). I don't want those, but the BLM doesn't always return the exact same number of coordinates, so I want to determine the four corners regardless of the response. Sometimes I'll combine multiple sections (a section is 1 mile, so it could be 4 square miles, or 2 miles by 1 mile). I use the coordinates to work with other database data that is in coordinates such as finding objects that are within the section.

$String = '48.010918880032534,-102.27871447619208,48.007298546092386,-102.27871873313035,48.007300404087175,-102.28410804755288,48.007302263082124,-102.28949736197535,48.007304121076913,-102.29488670439923,48.007305979071589,-102.30027601882199,48.010925506717513,-102.30027931864152,48.014545007363211,-102.30028259146059,48.018163867996407,-102.3002842242235,48.023143739025727,-102.30028731751861,48.023143709229146,-102.29967535036019,48.023140964347476,-102.29489150234963,48.023137856666608,-102.28949565918271,48.023137825974516,-102.28890660889158,48.023135053987971,-102.28409987201547,48.023131946306933,-102.2787040848512,48.018158490915084,-102.27870810340568,48.014539184972818,-102.27871024825487,48.010918880032534,-102.27871447619208';$Exp = explode(',',$String); if(is_array($Exp)) {$C = 1;foreach($Exp as $Exp2) {if(!empty($Exp2)) {if($Exp2 > 0) {$Latitude[] = $Exp2; } else {$Longitude[] = $Exp2; }}}}$MaxLong = max($Longitude);$MinLong = min($Longitude);$MaxLat = max($Latitude);$MinLat = min($Latitude); // SECOND IDEA! array_multisort($Latitude, SORT_ASC, $Longitude, SORT_DESC); // NO, SAME ISSUE This doesn't work as I explained, since it gathers the highest values when in reality, one corner might have a Y value 10, the other may have a Y value of 8 (because it's a mile a part, and township/range/sections are not exactly square).

I'm saying I don't want the *top right*. I'm saying I need the exact corners. The XY coordinates give a list of all the coordinates a section from the BLM. If I simply take the highest latitude and highest longitude, I won't get the exact corner because the top left corner might be at a higher y value, so if I use the highest Y value for the top right corner, I end up with a wrong number.

Yeah, the Y is vertical. I just see a lot of people refer to coordinates as XY. The picture is a ghetto POS I slapped into fireworks to wrap my mind around this. The 1,-7 dot isn't even in the right spot lol. The problem is that my box isn't a square. It's a rhombus. If you use the example coordinates, the arrays would be: Latitude ( 5, 4, 1, 0) Longitude ( -6, -7, -1, 0) If I take the max X and max Y i'll 5 and 0, but the actual top right corner is 4,-1.

It will always be rectangular. It's just section corners. The min/max XY is what I'm using now, and it's close but not 100% accurate. If you look at the picture attached, how could the top right corner be determined from a list? If I did min/max to the XY, I would end up with 5,0 for the coordinate when it should actually be 4, -1.

I am trying to determine the four corner coordinates of a list. Any ideas? 48.010918880032534,-102.27871447619208, 48.007298546092386,-102.27871873313035, 48.007300404087175,-102.28410804755288, 48.007302263082124,-102.28949736197535, 48.007304121076913,-102.29488670439923, 48.007305979071589,-102.30027601882199, 48.010925506717513,-102.30027931864152, 48.014545007363211,-102.30028259146059, 48.018163867996407,-102.3002842242235, 48.023143739025727,-102.30028731751861, 48.023143709229146,-102.29967535036019, 48.023140964347476,-102.29489150234963, 48.023137856666608,-102.28949565918271, 48.023137825974516,-102.28890660889158, 48.023135053987971,-102.28409987201547, 48.023131946306933,-102.2787040848512, 48.018158490915084,-102.27870810340568, 48.014539184972818,-102.27871024825487, 48.010918880032534,-102.27871447619208