Query mysql table of geocoded points by polygons

In Progress

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.

## Deliverables

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)

Thanks,

Doug

* * *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.

Skills: Engineering, Geolocation, MySQL, PHP

See more: details about space, query table, php kml, table queries, table extract, table based, mysql geocodes, kml mysql spatial, php kml mysql, bidders extract, kml mysql php, query project data, kml polygon mysql, polygons kml mysql, kml mysql polygon, php mysql show table columns, polygon inside polygon, kml polygon, engineering block project, extract database data sql, using kml mysql, mysql queries using, extract data mysql, database query project, project sql query

About the Employer:
( 93 reviews ) Brandon, United States

Project ID: #3754925

Awarded to:

matfr

See private message.

$255 USD in 7 days
(6 Reviews)
5.1

12 freelancers are bidding on average $192 for this job

jacobdd1

See private message.

$150.45 USD in 7 days
(259 Reviews)
7.0
phpguru2012

See private message.

$260 USD in 7 days
(194 Reviews)
6.4
shihan033010

See private message.

$200 USD in 7 days
(235 Reviews)
6.3
matfizvw

See private message.

$263.5 USD in 7 days
(48 Reviews)
5.9
customizedata

See private message.

$263.5 USD in 7 days
(92 Reviews)
5.5
bdsitech

See private message.

$180.2 USD in 7 days
(54 Reviews)
5.1
sytneivw

See private message.

$100.3 USD in 7 days
(26 Reviews)
3.9
ishu999

See private message.

$150.45 USD in 7 days
(9 Reviews)
3.9
junaidatique

See private message.

$200.6 USD in 7 days
(15 Reviews)
3.7
bickos

See private message.

$100 USD in 7 days
(13 Reviews)
3.3
siqbalvw

See private message.

$180 USD in 7 days
(2 Reviews)
1.4