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?


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


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

  • Nicklas: Thanks for the reply... Just so I understand how to setup my data.... I create a table with my CSV data and use a variable that is common to both tables (like the ISO 2 character code) to join the 2 tables with a select statement similar to your example. The results can be saved either as a new table (static) or a view (dynamic). Correct??? – ChrisJ Nov 18 '10 at 13:55
  • sounds right. to create a table you just put "Create table mynewtable as", in front of the select query, and corresponding for views. Is ISO 2 character code a countrycode? In that case it sounds like the fastest join attribute. – Nicklas Avén Nov 18 '10 at 14:02
  • Yes it is a 2 character code... and thanks for the extra tip creating the table with the select query... Will let you know how it goes... Chris – ChrisJ Nov 18 '10 at 14:40
  • Nicklas: I tried your suggestion and for some reason things did turn out right with the_geom,,, So I opened and looked at the dbf file associated with the country boundaries shape file in excel. I don't see a geometry (the_geom) column... If this column is does exist, how come I see the map in UDIG and my browser via postgis, mapserver and openlayer? Thanks Chris – ChrisJ Nov 22 '10 at 0:42
  • well,I thing you are mixing things here. first, a dbf doesn,t hold any geometry data. it is in the .shp file and is not readable in excel. second, you did your operation oin PostGIS. have you exported the resulting table to shape or why are you searching there? in your esulting table from the query above you should have a field the_geom. read about how to connect to a postgis database from the softwares you are mentioning and just open the table directly without exporting it. – Nicklas Avén Nov 22 '10 at 7:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.