1

I have the tiger census data of population density in each block group. I also have a different kind of data: traffic accident data, where an accident has a lat/lon pair. My goal is to find the population density of each accident location.

How can I match the accident to the block groups?

population density map

crash location map

Improve this question
3
  • so you're just trying to get the population value from each block group that each accident point intersects?
    – DPSSpatial_BoycottingGISSE
    Jul 6 '15 at 16:43
  • Yes. <accident1,population1> <accident2,population2>.
    – daydayup
    Jul 6 '15 at 16:45
  • 1
    Don't cross-post
    – Mike T
    Jul 6 '15 at 21:32
1

You could use the ST_Intersects function, and use a query like this:

    select 
acc.[column]
    , bg.[population]
      from accidents as acc
    join blockgroups as bg on ST_Intersects(acc.geom, bg.geom)
Improve this answer
4
  • Thank you, I am sure this is the right way to do. But I didn't see a geometry column in the population data. Is there any way to add it?
    – daydayup
    Jul 6 '15 at 17:08
  • is it called SHAPE or something? If you're running PGAdmin you should be able to inspect the table and the columns to see which one is the geometry...
    – DPSSpatial_BoycottingGISSE
    Jul 6 '15 at 17:33
  • Thank you Baker! I found it in PGAdmin. Interesting Qgis didn't show the geom.
    – daydayup
    Jul 6 '15 at 18:48
  • @daydayup if you use the DB Manager tool, it will show you the properties of your PostGIS tables, and list the fields and data types of the columns of your tables. That might be the best place to interact with your PostGIS database in QGIS...
    – DPSSpatial_BoycottingGISSE
    Jul 6 '15 at 19:23

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.