Query mysql table of geocoded points by polygons

This project was awarded to matfr for $255 USD.

Get free quotes for a project like this
Employer working
Awarded to:
Skills Required
Project Budget
$100 - $310 USD
Total Bids
Project Description

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)

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

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online