Skip to content

Instantly share code, notes, and snippets.

Last active December 16, 2022 21:15
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?

Compton streetlights

Generating some points to approximate streetlight locations in Compton, L.A.

Loose homage to Kendrick Lamar, strong shouts to Snoop, Dre, Mary J., and the map underneath the 2022 Superbowl Halftime Show, and big respect to the L.A. county streetlight spacing guidelines.

Screen Shot 2022-02-14 at 9 15 10 AM

(Yes, I know this isn't Compton. Bear with me here.)

Get streets data

From overpass turbo, pull the local street grid as GeoJSON.

This has been generated by the overpass-turbo wizard.
The original search was:
“highway=* and type:way”
// gather results
  // query part for: “highway=*”
// print results
out body;
out skel qt;

Download as compton_highway.geojson.

Add to PostGIS

Create a working DB and import the data:

dropdb $DBURL --if-exists
createdb $DBURL
psql $DBURL -c "CREATE EXTENSION postgis;"
ogr2ogr \
  -f "PostgreSQL" \
  PG:"host=localhost dbname=$DBURL" \
  compton_highway.geojson \
  -t_srs "EPSG:3857" \
  -nln still_got_love_for_these_streets \
  -lco GEOMETRY_NAME=the_geom \
  -progress \

Generate streetlight points

. . . using the magic of PostGIS

psql $DBURL -c "
  DROP TABLE IF EXISTS kendrick_lights;
  CREATE TABLE kendrick_lights AS (
    WITH streets AS (
        -- Set linear light spacing based on CA guidelines
          WHEN highway LIKE '%motorway%' THEN 200
          ELSE 50
        END) AS spacing,
        -- Pull linestrings from multilinestrings
        (ST_Dump(the_geom)).geom AS the_geom
      FROM still_got_love_for_these_streets
      -- Ignore the polygons from the overpass export
      WHERE ST_geometrytype(the_geom) = 'ST_MultiLineString'
    lolos AS (
        -- Generative point function
          -- Use the outer boundary of a 10m buffer around all streets
          -- Only use one point if the street segment is shorter than the class spacing
            WHEN spacing >= ST_Length(the_geom) THEN 0.5::float
            ELSE spacing/(ST_Length(the_geom))::float
        ) AS the_geom
      FROM streets
      (ST_Dump(the_geom)).geom AS the_geom
    FROM lolos

Export to GeoJSON

ogr2ogr \
  -f "GeoJSON" \
  -t_srs "EPSG:4326" \
  streetlights.geojson \
  PG:"host=localhost dbname=$DBURL" \

And ta-daaaaa


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