Another add data to multiple records in PostGIS table

Question

Nicklas, thanks for the answer to my previous question....

Forgive me for my ignorance and for asking what may turn out to be rather simple questions, but databases are not my area of expertise.

The select statement that gives me the number of crashes in each percinct: SELECT P.precinct, count(C) FROM nycpp P, nyccrash C WHERE _st_contains(P.the_geom, C.crashpoint) GROUP BY P.precinct ORDER BY P.precinct;

I want to add just the count to my nycpp table the variable that will hold the count is number_of crashes....

Thanks again the assistance

Chris

Answer 1

Hallo

I assume that precinct is the unique id in nycpp, then you can try:

update nycpp set number_of crashes=a.n_crashes from 
(SELECT P.precinct, count(C) as n_crashes FROM nycpp P, nyccrash C 
WHERE _st_contains(P.the_geom, C.crashpoint) 
GROUP BY P.precinct 
ORDER BY P.precinct) a
where nycpp.precinct=a.precinct;

But why do you use _st_contains instead of st_contains

the underscore version will not use your spatial indexes, but st_contains will do a first indexscan finding intersecting bouning boxes before running theunderscore version.

So, you probably absolutely want to use st_contains instead of contains. If your tables are big enough for needing index: In this query the spatial indexes is important on both tables and an index on precinct. Don't forget to analyse the table after creating the indexes to make them work.

BTW, I think you are supposed to mark the questions as answered if you are satisfied with the answer so others don't have to try to answer them.

HTH Nicklas