I have a mysql table of data (non-spatial) that has columns for lat and lon geocodes along with our other relevant fields. there are 11 million records in this table.
I have several polygons listed as series of lat/lon boundaries.
I need to query the mysql table based on the polygons and get totals based on other fields in the mysql table. Each polygon will contain roughly 3% of the records in the main table.
I am looking for a solution either in sql for mysql (non-spatial) or a combination of sql and php that can use the existing mysql database.
The attached file is a 1,000 record sample of the table.
The queries we would want to run are:
1) Show count of records for each `group` inside or on the polygon:
psedue sql: SELECT `group`, count(*) FROM table WHERE lat,lon inside (polygon) GROUP BY `group`
2) Show all blocks inside or on the polygon
pseudo sql: SELECT DISTINCT(block) FROM table WHERE lat,lon inside (polygon)
* * *This broadcast message was sent to all bidders on Thursday Dec 15, 2011 3:06:01 PM:
I have updated the project with a sample table, polygon series, and details about the queries needed. Note that the polygon series is currently a space delimited series of lat,lon. I plan to extract the polygon series I'll be using from the source kml file, but if it is easier working directly with the kml file let me know.