1

I'm trying to build my first choropleth map from data that we have stored in our PostGIS enabled Postgres database.

So far I've been able to build a map on Mapbox using a topojson set of UK postal area data, but I'm stumped on how to show concentration of our data points per postal area from our database.

Basically I'm trying to show we have a concentration of user type X in postal areas.

I know enough that querying the db point-in-polygon for each polygon in the postal data set would be slow and cumbersome, but is there a way to search the data by postal area and return a merged data set e.g. x users in area 1, y users in area 2?

Should I import the postal areas into a spatial table to do the query against in PostGIS?

Currently all the data points are output as JSON but I'm not sure how to associated the relevant points with each postal area.

  • If you mean 'front end' in that you have something like GeoServer set up, I would think you could use Leaflet heatmapping tools such as github.com/Leaflet/Leaflet.heat or patrick-wied.at/static/heatmapjs/example-heatmap-leaflet.html as long as PostGIS was outputting the proper GeoJSON format... – DPSSpatial Aug 6 '14 at 16:01
  • Sorry, I should have been clearer. I've got the front end presentation under control, in that I want to use a choropleth map either with D3 or Mapbox - it's the finding the number of users in each postal area. I'll try to rephrase my question :) – Les Aug 6 '14 at 16:11
  • Maybe this will work: d3noob.org/2014/02/generate-heatmap-with-leafletheat-and.html – DPSSpatial Aug 6 '14 at 16:19
  • Thanks, but I don't want a heatmap. I want to use a Choropleth map for reasons to do with our data. – Les Aug 6 '14 at 16:22
  • 1
    If you're using PostGIS doing a point in polygon count shouldn't be that taxing, especially if you generate a table of 'count of user of Type X' by zip code, then simply join that table back to the zip codes by their ID's, then use that as your new dataset for the choropleth map, that would work. – DPSSpatial Aug 6 '14 at 16:44
2

If you load your zip codes into PostGIS - with their geometry - you can create a new spatial view of your zip codes with a built-in column that is a count by Zip code of the number of points within each one.

Below is an example I created using some Zip codes around Denver, and address points found on the GIS Denver Data Portal.

To create the spatial view, examine the following SQL:

create view zipWithAddressCount as (
select 
gid
, zcta5ce10
, classfp10
, mtfcc10
, funcstat10
, aland10
, awater10
, intptlat10
, intptlon10
, geom
, ac.addresscount
from zip_co as z
join
(
select 
 z.geoid10
, count(a.gid) as AddressCount

from zip_co as z
join addresses as a 
on ST_Intersects(z.geom, a.geom)

group by z.geoid10
) as ac
on (z.geoid10=ac.geoid10)

)

...and here's the SQL with some comments added to explain what's going on:

      create view zipWithAddressCount as (
        select 
    --START FIELDS FROM ZIP CODES
        gid --NEED unique id for QGIS mapping
        , zcta5ce10
        , classfp10
        , mtfcc10
        , funcstat10
        , aland10
        , awater10
        , intptlat10
        , intptlon10
        , geom --need geometry for mapping
    --END FIELDS FROM ZIP CODE
    --field from temp table 'ac' which contains the count
        , ac.addresscount
        from zip_co as z
        join
        (
        select 
--temp table to do the spatial join and count of address points within zip code polygons
         z.geoid10 -- ID field from zip codes will be used for joining back
        , count(a.gid) as AddressCount --counting the records in address table

        from zip_co as z
        join addresses as a 
        on ST_Intersects(z.geom, a.geom)

        group by z.geoid10
        ) as ac
--back out of the temp table and specifying how to join to master zip codes
        on (z.geoid10=ac.geoid10)

        )

So now back in QGIS you've got a nice new spatial view you can add to your map:

enter image description here

...and when you get into the layer properties, you can symbolize on the AddressCount field you created in the temp table (spatial join and count), and you end up with something like this:

Using Graduated Renderer in QGIS layer properties:

enter image description here

And lastly and interestingly: this view is dynamic so when you add more points to the point layer the zipWithAddressCount count will automatically show this new count!

Ah PostGIS...such a relief!

  • This is an awesome response, and I've learned tonnes from it already! Thanks for taking the time to put such a thorough breakdown. One more question: do the initial points need a geom column for this to work? Should I add that column and convert the lat long positions to geom? – Les Aug 8 '14 at 20:58
  • @Les great!!! The ZIP codes are the primary geometry, so those are the ones that need the unique ID - they should come with one in the original dataset... let me know if you need any more help, and please mark this as your answer if you can! – DPSSpatial Aug 8 '14 at 21:02

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.