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?
Best Answer
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 )
From PostGIS manual (http://postgis.net/docs/index.html)
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
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.