Querying a PostGIS database with a KML "shapefile"

Question

I am working on a Google Maps application that doee the following (on a low zoom level):

1. Prints a bunch of polygon overlays onto the map. These polygons are created from KML files that I created with Google Earth. Basically these polygons cover continent sized chunks of the earth. 15 of them cover the entire land of the world.

2. On top of each polygon overlay, a number is displayed that represents the total number of points that exist within that polygon.

The points I want to display on my map have a field representing country, and a field for latitude/longitude. The way I have been getting the totals is to run a query for each polygon by the country code like so:

east_europe_total = Point.objects.filter( Q(country_code='TR') | Q(country_code='CZ') ... ).count()

Which is not very good if I want to have a boundary go across a country, or if I ever want to re-organize my polygons 'sectors'. A much better solution is to give the KML file to the database (which is PostGIS) and have it all sorted out for me. Is this possible in any way? If not, then what about converting the KML file to a format that can both be easily inserted to Google Maps and be query-able by postGIS?

Answer 1

Although it doesn't fully answer your request, I've found this article invaluable, both for its readable narrative (of an operation somewhat different from the one you need -- and different from what I was trying to do at the time, too -- but still requiring PostGIS to interact with a KML file). As the article's author says,

Generally there is a lot of GIS jargon that is quite academic, mathematical and disconcerting for the uninitiated (e.g. convex hulls) but if you keep looking long enough you'll eventually find what you need!

...and readable articles like this one (with pointers to many tools &c) are a start.

So, to summarize, first you need to find an acceptable way to turn your KML into a format PostGIS knows about [or specifically ogr2ogr knows about] such as GML (and validate that), then something like ogr2ogr lets you load that format into PostGIS -- and then of course you still need the point-in-polygon operations, but, hey, it's a start.