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

Awarded to:
Skills Required

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