I have this huge table with all the addresses of my country in it. Every house is an object, and there is a column for the streetname (or street ID) and a column for the housenumber. I want to make a new table with all the street-id's in it, and the sum of houses on that street, what is the fastest way to make this calculation ?


closed as off-topic by Jason Scheirer, BradHards, Fezter, Devdatta Tengshe, radouxju Apr 28 '14 at 8:14

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions about software development are off-topic here unless they relate directly to Geographical Information Systems, but they can be asked on Stack Overflow." – Jason Scheirer, BradHards, Fezter, Devdatta Tengshe, radouxju
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    This is a simple aggregation SQL query. Furthermore, at first glance, there isn't anything specific to GIS here. What have you tried so far? where are you stuck? – Devdatta Tengshe Apr 28 '14 at 5:02

Assuming your data table look like:

streetid    housenumber
100         12
100         15
101         12
101         18

You could do a basic SQL query:

SELECT streetid, count(*) FROM tablename GROUP BY streetid

Where this might get more GIS-y and interesting is the case where streets with the same ID are not contiguous. You didn't mention if this is a PostGIS database, but if it is and you have a geom column then you'll probably want to split multipart into singlepart geometries using ST_Dump.

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