[GIS] Building SQL statement in javascript and casting variables as strings

geojsonjavascriptpostgresqlsql

I'm working with Postgres 8.3/PostGIS which is storing some polygons. I'm writing a javascript function which will take in a parameter of a watershed id from another function. The javascript will build up a sql statement and then send that along to a jsp script which will send that to the database and return some geojson which will then be output into Openlayers. The problem is that the geojson is not being returned because I think I have a problem with my select statement and some type (integer/text) problem.
The table holding the polygon geometry contains a column set as varchar(50) but it contains some id's which have only numbers, and some id's which contain numbers and string characters. The function attempts to return a geojson from the database of a polygon of a given id. I've left off the last end of the function because I think my problem is somewhere in the building of the sql statement.

function displaySelectedShed(shedId) {
    var json_url = "04-sql-to-json.jsp?sql=";
    var sql_geom = "st_transform(ST_Force_2D(the_geom),900913)";
    var sql = "select st_asgeojson(" + sql_geom + "), id ";
        sql += "from medford.subshed2 ";
        sql += "where subshed2.id = " + shedId +"";
        json_url += encodeURIComponent(sql);
 }

The select statement above looks like this:

select st_asgeojson(st_transform(ST_Force_2D(the_geom),900913)), id from medford.subshed2 where subshed2.id = 01616500

and I can test this by putting this:

http://localhost:8080/spatialdbtips/04-sql-to-json.jsp?sql=select%20st_asgeojson%28st_transform%28ST_Force_2D%28the_geom%29%2C900913%29%29%2C%20id%20from%20medford.subshed2%20where%20subshed2.id%20%3D%2001616500

into a browser window. But the error I get is this:

Error: operator does not exist: character varying = integer

I know this has probably something to do with the fact that my shed id is a number when inserted as a variable into the sql statement, but I've tried all sorts of cast combinations to get it to be a string, since the values in the database are stored as such, but I can't get them to work. I've tried both the CAST as well as the :: such as

sql += "where subshed2.id = " + shedId +"::text";

but I can't get it to work. Does anyone have any suggestions to get my watershed id numbers (the shedId variable) to act like string in the sql where clause, and how to build it up in a way I've shown in javascript?

Best Answer

An easy way to treat shedId as text will be

sql += "where subshed2.id = '" + shedId +"'";

simply putting single quotes around it. Without it PostGRESql would treat numbers as numbers and expect character data to be an identifier.

Related Question