GIS project advice

Question

Hello: I have the following:

1. Shapefile of the world with the boundaries of each country
   • Successfully imported shapefile into PostGIS database
   • Can display map of the world in a browser via Openlayers.
2. A data (CSV) file where each record has
   • birth country,
   • gender,
   • lat and long of the country of birth (which matches the lat & long in the shapefile)

I am trying to figure out the best approach to display a map where each country (based on the CSV data) that has a 100 or more people with a color say red or green....

So my question is: If I create a separate table with the CSV data, is there a way link this table with the shapefile table to do the above?

OR

Do I need to merge the CSV data into the shapefile table in order to accomplish the above

I am using PostGIS, mapserver and Openlayers

Thanks Chris

Answer 1

First, import the CSV file to a new table in the database. How to do it you can find here

Then you have several possible approaches to get what you want. You seems to have more than one way to join the two tables. You probably have the countryname in both tables, that can be used. You mention you have some identifying point that correspond in the two tables, and you could go the spatial way and polygonize the countryborders and find what country the point in the SCV table is inside.

The last one is unnessecary complicated and slow when you have the other two options.

so, let's assume that your lat-lon that you mention is represented in two separate fields like latfld and lonfld, then you could do something like:

Select a.the_geom, count(b.rowid) as numberof 
from country_borders a 
inner join attribute_table b 
on a.latfld=b.latfld and a.lonfld=b.lonfld 
group by a.countryid, a.the_geom;

Then you will get a result with the country borders and the number of people in the country. Then you put the right color on diffeent classes in mapserver.

If the result is static you put it in a new table, if it will change and should be dynamic you put it in a view.

To make this query work fast you should put indexes on latfld, lonfld in both tables and on the_geom for mapserver to faster get the right geometries when not showing all countries.

Regards Nicklas