Finding nearest neighbor with PostGIS and nodejs

Question

I'm using postgreSQL 9.1 , postGIS 2.0, nodejs 0.10.12 and the latest version of node's pg module.

I use websockets on the server side. I implemented a function that tries to find the 5 nearest neighbors of a given geometry (it can be a point or a line or a polygon). That does not work.

I admit I am a rookie on node. And also that I am using a complicated method of my own.

Here is the client side code. Working on a vector layer

    function feature_added(feature){

  //a feature just added
//grab its geometry
    var bat=map.layers[2].features[0].geometry;
//create sockets
          var wdm = new WebSocket("ws://localhost:8000");
//error reporting for sockets    
         wdm.onerror=function (evt) 
         {mapDou.textContent = evt;}
//open sockets        
         wdm.onopen = function(){
//convert geometry to a string
    var man=new String(bat);
//get the first five letters of the string to check if it is a point, line or polygon
    var spi=man.substring(0,5);

//according to the 5 first letters, send info to server
    if(spi=='POINT'){
//get just the geometry=numbers, spaces and commas to send to server
        var hul=man.substring(6,man.length-1);
             wdm.send(JSON.stringify({command: 'mapDoubleIn', geomT: 'point',geomDoubleIn: hul}));
             document.getElementById('mapDou2').innerHTML=hul;}

    if(spi=='LINES'){
        alert('in3');
        var hul=man.substring(11,man.length-1);
             wdm.send(JSON.stringify({command: 'mapDoubleIn', geomT: 'line', geomDoubleIn: hul}));
             document.getElementById('mapDou2').innerHTML=hul;}      

         //same IF snippet for polygons....
    //get the message from server
         wdm.onmessage = function (evt) { 
         var received_msg = evt.data;
         document.getElementById("mapDou").innerHTML+=received_msg;};
    //unregister so this function work for the first feature added, not the others the user may add
    pins.events.unregister('featureadded', this, feature_added);
        }

And here is the server side snippet that deals with the nearest neighbor

    function checkMapIn(je,jj){

//je is the numbers, commas, spaces came from user
//jj is the type

    if (jj=='point'){
    var conString = "pg://username:password@localhost:5432/myDB";
    var client = new pg.Client(conString);
    client.connect();
//replace space with comma , so syntax is like 1,2 not 1 2
    var je=je.replace(/ /gi, ',');
    var query = client.query('SELECT pins.p_name  FROM pins ORDER BY pins.p_geom <-> st_setsrid(ST_MakePoint('+je+'),900913)LIMIT 5;')
            query.on("row", function (row, result) {
        result.addRow(row);});
    query.on("end", function (result) {
    console.log(JSON.stringify(result.rows, null, "    ")); 
    for (var i=0; i<result.rows.length; i++){
       connection.send(result.rows[i].p_name)}
        client.end();});}


if (jj=='line'){
var conString = "pg://username:password@localhost:5432/myDB"; 
var client = new pg.Client(conString);
client.connect();
var query = client.query('SELECT pins.p_name  FROM pins ORDER BY pins.p_geom <-> st_setsrid(ST_MakeLine('+je+'),900913)LIMIT 5;')
        query.on("row", function (row, result) {
    result.addRow(row);});
query.on("end", function (result) {
console.log(JSON.stringify(result.rows, null, "    ")); 
for (var i=0; i<result.rows.length; i++){
   connection.send(result.rows[i].p_name+'  je  '+je)}
    client.end();});}
//for the polygons is the same thing.....    

}

Now, if I add a point on the map, I get ALL the points from the DB as a result. I have only 3 simple points in the db and I get all the names, no matter where I add the point.

If I add a line or a polygon on the map, I get this error from node

events.js:72
throw er; //Unhandled 'error' event
      ^
error: synatx error at or near "4740270.8015625"

This number is ALWAYS the second part of the first pair of the line or polygon.What I mean? Watch closely. This is the number 4740270.8015625 and this is what I get from openlayers when I add a feature POLYGON((2318122.615 *4740270.8015625*,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625)) I enclosed in ** where the number is placed.

Do you have any advice or can you suggest any alternatives?

Answer 1

Your problem is that ST_MakePoint nad ST_MakeLine doesn't take WKT/EWKT format as input.

Working example query : ( My test table is in SRID 4326 )

    SELECT ST_Distance(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326)) as distance,p.fips
    FROM world p 
     WHERE ST_DWithin(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326),10) 
     ORDER BY ST_Distance(p.geom, ST_SetSRID(ST_MakePoint(1.0, 2.0),4326)) ASC LIMIT 5

From PostGIS manual (http://postgis.net/docs/index.html)

SELECT ST_AsEWKT(ST_MakeLine(ARRAY[ST_MakePoint(1,2,3),
                ST_MakePoint(3,4,5), ST_MakePoint(6,6,6)]));
        st_asewkt

So ST_MakeLine wants geoms to work. Easiest way to write your code will be replace ST_MakePoint with ST_GeomFromEWKT() OR ST_GEomFromWKT() (note that you need ST_SetSRID with that)

Example : SELECT ST_GeomFromEWKT('SRID=4269;POINT(-71.064544 42.28787)');

Following example replaces ST_Makeline with ST_GromFromEWKT. Note 900973 is old SRID for google mercator, you should use 3857. Example uses my test table world which has administrative boundaries of the world in srid 4326. That is the reason why there is ST_Transform(geom, SRID) call

    SELECT ST_Distance(p.geom, 
    ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
    ) as distance,p.fips
    FROM world p 
     WHERE ST_DWithin(p.geom, 
    ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
    ,10) 
     ORDER BY ST_Distance(p.geom, 
    ST_Transform(ST_GeomFromEWKT('SRID=3857; POLYGON((2318122.615 4740270.8015625,2355043.3 4731221.6140625,2320294.42 4716380.9465625,2318122.615 4740270.8015625))'),4326)
    ) ASC LIMIT 5

So change your "je" Variable to contain WKT (http://en.wikipedia.org/wiki/Well-known_text) text, add SRID=xxx to make it EWKT and pass it to ST_GeomFromEWKT() instead.

Answer 2

You want something more like insert into prods (column_name, ...) values (?, ...)

Databases generally don't let you use placeholders for identifiers (table names, column names, ...). PostgreSQL is complaining because you're trying to use the first three placeholders as column names:

INSERT INTO users($1, $2, $3) ...

You can think of identifiers like variable names in JavaScript: you can't put a variable name into another variable without some sort of eval chicanery. Similarly, you can't put a column name in a placeholder.

You should specify the column names without placeholders:

var queryConfig = {
  text: 'Your query',
  values: ['A', 'B', 'C']
};