Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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

share|improve this answer
    
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??? –  Chris 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 –  Chris 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 –  Chris 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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