it turns out I don't need to use nearest neighbour after all. I assigned an id identical to the centroids to which i'm connecting the lines through
-- this sql script creates a line table that connects points
-- delete existing tables if they exist
DROP TABLE exploded_roads;
DROP TABLE projected_points;
DROP TABLE lines_from_centroids_to_roads;
-- convert multi lines into lines
CREATE TABLE exploded_roads (
the_geom geometry,
edge_id serial
);
-- insert the linestring that don't need to be converted
INSERT INTO exploded_roads
SELECT the_geom
FROM "StreetCenterLines"
WHERE st_geometrytype(the_geom) = 'ST_LineString';
INSERT INTO exploded_roads
SELECT the_geom
FROM (
SELECT ST_GeometryN(
the_geom,
generate_series(1, ST_NumGeometries(the_geom)))
AS the_geom
FROM "StreetCenterLines"
)
AS foo;
-- create projected points table with ids matching centroid table
CREATE TABLE projected_points (
the_geom geometry,
pid serial,
dauid int
);
-- Populate Table
INSERT INTO projected_points(the_geom, dauid)
SELECT DISTINCT ON ("DAUID")
(
ST_Line_Interpolate_Point(
exploded_roads.the_geom,
ST_Line_Locate_Point(
exploded_roads.the_geom,
centroids.the_geom
)
)
),
(centroids."DAUID"::int)
FROM exploded_roads, fred_city_o6_da_centroids centroids;
-- Create Line tables
CREATE TABLE lines_from_centroids_to_roads (
the_geom geometry,
edge_id SERIAL
);
-- Populate Line Table
INSERT INTO lines_from_centroids_to_roads(
SELECT
ST_MakeLine( centroids.the_geom, projected_points.the_geom )
FROM projected_points, fred_city_o6_da_centroids centroids
WHERE projected_points.dauid = centroids."DAUID"::int
);
-- Delete temp tables
--DROP TABLE exploded_roads;
--DROP TABLE projected_points;