Skip to content

Instantly share code, notes, and snippets.

@pramsey pramsey/streets.sql
Last active May 31, 2019

Embed
What would you like to do?
Streets Data
-- shp2pgsql -s 26918 -D -I -i nyc_streets | psql streets
-- Generate gaussian offset random points from input roads
-- add more roads in the 'gid' list as desired
DROP TABLE IF EXISTS pts;
CREATE TABLE pts AS
WITH s AS (
SELECT
gid,
nextval('nyc_streets_gid_seq') AS a,
ST_LineInterpolatePoint(
ST_GeometryN(geom,1),
generate_series(0, floor(ST_Length(geom) / 50.0)::integer ) / (ST_Length(geom) / 50.0)
) AS geom,
random() AS u1,
random() AS u2
FROM nyc_streets
WHERE gid IN (20,18293,18382,16925,18391,16996,1426,11211)
),
t AS (
SELECT
gid, geom, a,
sqrt(-2 * ln(u1)) * cos(2*pi()*u2) AS z1,
sqrt(-2 * ln(u1)) * sin(2*pi()*u2) AS z2
FROM s
)
SELECT ST_Translate(geom, 6*z1, 6*z2) AS geom, gid, a
FROM t;
-- node the streets file so that all intersections
-- are noded, use this network for matching
DROP TABLE IF EXISTS nyc_streets_noded;
CREATE TABLE nyc_streets_noded AS
SELECT
(ST_Dump(ST_Node(ST_CollectionHomogenize(ST_Collect(geom))))).geom AS geom,
nextval('nyc_streets_gid_seq') AS pk
FROM nyc_streets;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.