Skip to content

Instantly share code, notes, and snippets.

@wboykinm
Last active August 29, 2015 13:56
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wboykinm/9234487 to your computer and use it in GitHub Desktop.
Save wboykinm/9234487 to your computer and use it in GitHub Desktop.
PostGIS Point in polygon
-- Aiming for count of point_table points in polygons of polygon_table
WITH polygon_table AS b
SELECT b.the_geom_webmercator AS the_geom_webmercator,
COUNT(i.cartodb_id) AS points_count,
COUNT(i.cartodb_id)/ST_Area(b.the_geom) AS points_density,
1 AS cartodb_id FROM b,
(SELECT * FROM point_table) i
WHERE ST_Intersects(i.the_geom_webmercator, b.the_geom_webmercator)
@andrewxhill
Copy link

SELECT 
  b.the_geom_webmercator AS the_geom_webmercator, 
  COUNT(i.cartodb_id) AS points_count, 
  COUNT(i.cartodb_id)/ST_Area(b.the_geom) AS points_density, 
  1 AS cartodb_id FROM b
FROM polygon_table b, point_table i 
  WHERE ST_Intersects(i.the_geom_webmercator, b.the_geom_webmercator)

@wboykinm
Copy link
Author

Woot! Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment