Setting the Geometry with sql query

Question

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?

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). — Ryan Garnett
– Ryan Garnett, Commented Jul 22, 2014 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? — Rebecca Harris
– Rebecca Harris, Commented Jul 22, 2014 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. — Ryan Garnett
– Ryan Garnett, Commented Jul 22, 2014 at 17:04

Mark Cupitt · Accepted Answer · 2014-07-23 02:42:54Z

1

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);

answered Jul 23, 2014 at 2:42

Mark Cupitt

3,9244 gold badges32 silver badges55 bronze badges

Add a comment |

Stack Exchange Network

Setting the Geometry with sql query

1 Answer 1

Your Answer

Hot Network Questions

Setting the Geometry with sql query

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions