+1  A: 

A problem would be that I think the function expects the second argument (distguess) to be a double precision instead of an integer. Try 1000000.0 or try casting to float explicitely...

ChristopheD
I tried both and that didn't fix it, but kudos on the notice.
dassouki
A guess, but i'd try writing the 'exploded_roads' ,'true', 'gid' - part as ''exploded_roads'' ,''true'', ''gid'' or "exploded_roads" ,"true", "gid"... Are you using a tool like pgadmin to run this query?
ChristopheD
I'm using PgAdmin to run the queries. the '' true '' returns an error now : ERROR: syntax error at or near "true"LINE 28: ...the_geom, 1000000.0, 1,1000, "exploded_roads" , ''true'', "g...
dassouki
with that error, i tried changing ''true '' to 'true' "true" with no vain. I also tried to change "true" to "DAUID" >0 " (one of the fields in centroids)
dassouki
+1  A: 

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;
dassouki