I am trying to merge data from a U.S. states shapefile with data on each state. I used this SQL query which is supposed to work:

 SELECT * FROM child_poverty_by_state
 UPDATE child_poverty_by_state
 SET the_geom = us_states.the_geom
 FROM us_states
 WHERE us_states.state = child_poverty_by_state.state

The table "us_states" which is the shapefile, doesn't auto-populate in the query. Not sure why, is there an error with my query?

  • 2
    What software are you using to do this. A shapefile is not a database, and the query you are using is specific to a spatial database (ie: Spatialite, PostGIS, etc.). If you want to do those types of queries you will want to put your data into a spatial database (like the types mentioned above). Jul 22 '14 at 16:43
  • I'm not using any software I'm just using Cartodb. I have a separate shapefile that's uploaded as a table that I was trying to merge with the child_poverty_by_state table. All I'm trying to do is a have a U.S. map that shows child poverty rates by state. Any advice on what I should do since this isn't working? Jul 22 '14 at 16:56
  • Without looking at the data it is a little difficult for me, but you could look at table joins or spatial joins. I am assuming you have some data with birth rates (points or polygons). If the birth rates data has any attributes related to the state, then you could do a table join with the state polygon to put that data into the state table. Otherwise, you may want to do a spatial join. Going about this will depend on how your data is put together. Jul 22 '14 at 17:04

When you create child_poverty_by_state, you need to create the geometry table with the following, assuming you are working in WGS84 ..

ALTER TABLE child_poverty_by_state ADD COLUMN the_geom geometry(POINT,4326);

You also need to ensure that the geometry column has the correct srid, it is not always added from the shape-file, for example for WGS84 ...

UPDATE us_states SET the_geom = ST_SetSRID(the_geom,4326);

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.