3

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). 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.

Again, I admit it is comlecated, but I cannot fix it. Any advise? Even better, any alternatives?

Thank you very much

slevin

  • "5 nearest neighbors" means nearest neighbors on graph or do you mean nearest spatial features. If spatial then SELECT * FROM pins p ORDER BY ST_Distance(p.geom, ST_MakeLine(....)) ASC LIMIT 5. And this example will return all points in db if you have less than 5 there. If you want limit search to return max 5 point when when distance is within 1km uset 'WHERE ST_DWithin(geom, ST_Makeline(...)= < 1000' – simplexio Aug 8 '13 at 12:17
  • @simplexio Thanks for the anser. But: using ('SELECT * FROM pins p WHERE ST_DWithin(pins.p_geom, ST_MakePoint('+je+'), 1000),900913) ASC LIMIT 5') for points I get syntax error at or near ",". Using ('SELECT * FROM pins p WHERE ST_DWithin(pins.p_geom, ST_MakePoint('+je+'), 1000),900913) ASC LIMIT 5') for lines, I get the same error I described in my question... – slevin Aug 8 '13 at 13:28
4

Edit: 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.

  • Again, thanks for the great anser. I am trying to send to the server WKT. So in the client I use something like var aqu=new OpenLayers.Format.WKT(map.layers[2].features[0].geometry);. But I cannot send aqu to the server. I get this error Converting circular structure to JSON on the client side. I think it has something to do with JSON and stringify.This is why I was trying in the begging to send just the number to the server. Any advise? I'll keep trying... – slevin Aug 8 '13 at 18:52
  • Zero experince from Openlayers , but i would try 'var wkt = OpenLayers.Format.WKT().write(map.layers[2].features[0].geometry)' – simplexio Aug 9 '13 at 6:43
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']
};

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.