Skip to content

Instantly share code, notes, and snippets.

@migurski

migurski/queries.pgsql

Last active Dec 20, 2015
Embed
What would you like to do?
Queries to convert TIGER edges data to labeled address ranges. See: http://mike.teczno.com/img/tiger-ranges.png
DROP TABLE IF EXISTS tiger_2012_edges_102008;
DROP TABLE IF EXISTS tiger_2012_ends_102008;
CREATE TABLE tiger_2012_edges_102008
AS
(
SELECT statefp, countyfp, mtfcc, fullname,
fromadd, toadd, zip, "offset",
--
-- Snip off first and last 15m of each road to distance it from intersection.
--
ST_Line_Substring(s.geom, 15 / ST_Length(s.geom), (ST_Length(s.geom) - 15) / ST_Length(s.geom)) AS geom
FROM
(
SELECT statefp, countyfp, mtfcc, fullname,
lfromadd AS fromadd, ltoadd AS toadd, zipl AS zip, offsetl AS offset,
--
-- Offset 5m to the left.
--
ST_OffsetCurve(ST_Transform(ST_GeometryN(geom, 1), 102008), 5) AS geom
FROM tiger_2012_edges
WHERE roadflg = 'Y'
AND lfromadd IS NOT NULL
UNION
SELECT statefp, countyfp, mtfcc, fullname,
rfromadd AS fromadd, rtoadd AS toadd, zipr AS zip, offsetr AS offset,
--
-- Offset 5m to the right and reverse: "For a negative distance
-- it'll be at the right side and in the opposite direction."
-- http://postgis.refractions.net/docs/ST_OffsetCurve.html
--
ST_Reverse(ST_OffsetCurve(ST_Transform(ST_GeometryN(geom, 1), 102008), -5)) AS geom
FROM tiger_2012_edges
WHERE roadflg = 'Y'
AND lfromadd IS NOT NULL
) AS s
--
-- Limit to those road segments long enough to be meaningful.
--
WHERE ST_Length(s.geom) > 40
);
CREATE TABLE tiger_2012_ends_102008
AS
(
SELECT statefp, countyfp, mtfcc, fullname,
add, zip, "offset", p.geom
FROM
(
SELECT statefp, countyfp, mtfcc, fullname,
fromadd AS add, zip, "offset",
--
-- Get the first point on the line.
--
ST_Line_Interpolate_Point(geom, 0) AS geom
FROM tiger_2012_edges_102008
WHERE ST_GeometryType(geom) = 'ST_LineString'
UNION
SELECT statefp, countyfp, mtfcc, fullname,
toadd AS add, zip, "offset",
--
-- Get the last point on the line.
--
ST_Line_Interpolate_Point(geom, 1) AS geom
FROM tiger_2012_edges_102008
WHERE ST_GeometryType(geom) = 'ST_LineString'
) AS p
);
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.